OpenForm with data from a lookup table

Topic

New Reply

Arrgh! I’m trying to use a command button to open a form on a record based on a lookup table. The form opens but with a blank record.

TL;DR

For testing purposes I created two tables and two forms. Person Data table contains names & addresses, Books table contains titles and borrower name. Borrower name is a lookup table from Person Data. Added a command button (using the wizard) called “Info” to the Books form to open the Person Data Input form with the idea that it would show me the record. All I get is the form on a blank record (the record navigator at the bottom shows record 1 of 1(filtered)). When I set up a watch for the variable that is being sent to the OpenForm command I see that it is picking up the NameID from the lookup table so the expression ends up looking like “[Name]=’7′”. Clearly I need to do something else.

This first image shows the tables, forms, and VBA code:

45598-Image1

The second image shows the output and the VBA watchlist

45599-Image2

And another question: I see Me![fieldname] used regularly. What does the Me! signify? :newb:

Thanks for any help!

Reply | Quote

Replies

A solution?

This may not be the best solution but it seems to be working. Look it over and tell me what you think:

Code:

Private Sub PersonInfo_Click()
On Error GoTo Err_PersonInfo_Click

    Dim stDocName As String             ‘  Form Name
    Dim stLinkCriteria As String        ‘  Link Criteria
    Dim varName As Variant              ‘  Name returned from DLookup
    Dim stDLField As String             ‘  Field Name for DLookup
    Dim stDLTable As String             ‘  Table Name for DLookup
    
    stDLField = “[Name]”
    stDLTable = “Person Data”
    stDocName = “Person Data Input”
    
    ‘  Error test for null pointer
    If IsNull(Me![BorrowerName]) Then
        x = MsgBox(“Cannot find data on empty records. Please try again.”, vbOKOnly, “No Data”)
        GoTo Exit_PersonInfo_Click
    End If
    
    varName = DLookup(stDLField, stDLTable, “[NameID]=” & Me![BorrowerName])
    
    ‘  Error test for null name
    If IsNull(varName) Then
        x = MsgBox(“Name field is blank. Cannot find data. Please try again.”, vbOKOnly, “No Data”)
        GoTo Exit_PersonInfo_Click
    End If
    
    stLinkCriteria = “[Name]=” & “‘” & varName & “‘”

    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly

Exit_PersonInfo_Click:
    Exit Sub

Err_PersonInfo_Click:
    MsgBox Err.Description
    Resume Exit_PersonInfo_Click
    
End Sub

The DLookup() function seems to get the right data and allows me to properly error-check. I had trouble at first if the Name field was somehow Null. I had initially defined varName as a string but was getting “Invalid use of Null” errors if the name was null. Changed varName to Variant and it works great now.

There is probably an easier and/or better way to do this but this is what I came up with. Please show me how YOU would do it!

Reply | Quote

The Where clause should be “NameID=7”.

Reply | Quote

The Where clause should be “NameID=7”.

Doesn’t work. Going back to the original (wizard based) code in the first post, I changed the Where clause to: stLinkCriteria = “[NameID]=” & “‘” & Me![BorrowerName] & “‘” and now get “The OpenForm action was canceled” instead of the empty Person Data Input form. One step backwards…

I really don’t get what is going on. I understand that since BorrowerName comes from a lookup table, its data is a pointer to the relevant record in the Person Data table, even though when I look at the Books table in datasheet view it shows the name rather than the pointer. I suppose that is a function of the table relationship? At any rate, if the Where clause passes the NameID field to the Person Data Input form with a valid ID pointer, should it not open to that record? To test I changed the string to simply stLinkCriteria = “[NameID]=’7′” and got the action canceled error. But if I change it to stLinkCriteria = “[Name]=’Hollis W'” (the name associated with NameID=7) the Person Data Input form opens to that record. That is why I went through the convoluted steps using DLookup to get the name. Clearly I have no idea what the heck I am doing!

Is it the relationship that is causing the problem?

Ahhh crap… more testing while I’m typing this and I think I discovered the problem. I removed the single quotes from around the 7 so it now reads stLinkCriteria = “[NameID]=7” and it took to the correct record in the form. Changed the original string to: stLinkCriteria = “[NameID]=” & Me![BorrowerName] and it works just fine now. I guess I need to learn a little more about variables in VBA. Added acFormReadOnly to the OpenForm action and it works as a “view only” screen, just as I wanted.

Thanks Mark! That’s #2. Hope you don’t get bored with me when it’s at #10, #20, #1000000000…

Reply | Quote

I don’t see how “[NameID]=” & Me![BorrowerName] would work, unless perhaps BorrowerName is the name of the combobox and that the ID is the bound column?

Any time you are formulating WHERE clauses, you need to be aware of the data types for the fields you are using. Obviously [NameID] is probably a numeric field, so quotes aren’t needed to use 7 and not ‘7’. Quotes are needed around text fields, and you must use the # symbol around date fields.

As a word of caution, you should get out of the habit of naming fields in your tables with reserved words (like Name) that Access uses for Property, methods, or function names. So instead of your Name field, you should use BorrowerName, etc.

Reply | Quote

I don’t see how “[NameID]=” & Me![BorrowerName] would work, unless perhaps BorrowerName is the name of the combobox and that the ID is the bound column?

Yes, I believe that is the case. I’m still learning the terminology. BorrowerName is the combobox used for looking up Name from the Person Data table. Being that the Data Type for BorrowerName is Numeric I suppose the bound column would be NameID. That seems to be the way the Lookup Wizard set it up.

Any time you are formulating WHERE clauses, you need to be aware of the data types for the fields you are using. Obviously [NameID] is probably a numeric field, so quotes aren’t needed to use 7 and not ‘7’. Quotes are needed around text fields, and you must use the # symbol around date fields.

The problem initially (aside from my complete lack of knowledge) was with the code that the Button Wizard created based on my uninformed decisions. Since the Books table was showing a name in the BorrowerName field I assumed that the relationship to the Person Data table would be with the Name field. So I selected BorrowerName Name in the wizard. Wrong choice! That’s why it created the code: stLinkCriteria = “[NameID]=” & “‘” & Me![BorrowerName] & “‘” which translated to [NameID]=’7′. Now that I know a little more about Lookups, I’ll be more careful of field naming and choosing in the future. BorrowerName really should have been BorrowerID. Which leads me to…

As a word of caution, you should get out of the habit of naming fields in your tables with reserved words (like Name) that Access uses for Property, methods, or function names. So instead of your Name field, you should use BorrowerName, etc.

Yes I am learning that! And that it is better not to include spaces in Table, Form, and Report names. Much to learn, very much to learn.

Thanks again for your input Mark! I really appreciate it.

Reply | Quote