Results 1 to 6 of 6
  1. #1
    New Lounger RedWagnum's Avatar
    Join Date
    Sep 2016
    Location
    San Antonio, TX
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question OpenForm with data from a lookup table

    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:

    Image1.jpg

    The second image shows the output and the VBA watchlist

    Image2.jpg

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

    Thanks for any help!

  2. #2
    New Lounger RedWagnum's Avatar
    Join Date
    Sep 2016
    Location
    San Antonio, TX
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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!

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    The Where clause should be "NameID=7".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    RedWagnum (2016-09-20)

  5. #4
    New Lounger RedWagnum's Avatar
    Join Date
    Sep 2016
    Location
    San Antonio, TX
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    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...

  6. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #6
    New Lounger RedWagnum's Avatar
    Join Date
    Sep 2016
    Location
    San Antonio, TX
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •