Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: Stumped: Cascading Combo Boxes in Name Look Up

    First post so I will try and include everything that I should.

    I am trying to build a name look-up form (frmNameLookupTest) that has two combo boxes for entering the first and last names (name FirstName and LastName). I want to query two tables (tblpeople and tblaliasnamechanges, both of which have corresponding fields for each category) with a union based on what is on the form. The tblpeople holds a lot of different fields but the tblsliasnamechange only holds fields for a key field, a peopleID field that corresponds to the peopleID in tblpeople, and a first/single name alias and a last name alias. For example John Doe has an alias of Jack Doe and an ID of 12 so tblpeople would contain ID/First Name/Last Name fields of 12,John,Doe and tblaliasnamechange would contain Key/Person ID/First Name/Last Name fields of 6,12,Jack,Doe. I set it up this way because any given person might have more than one alias. It is also important that some aliases are single name stored in the First Name field. I created a union (qryFirstAndLastID) with the following:

    Code:
    SELECT peopleLastName,peopleFirstName,peopleID FROM tblpeople 
    UNION SELECT aliasLast,aliasSingleFirst,aliasPeopleID FROM tblaliasnamechanges
    ORDER BY peopleLastName;
    And got a query that included all the IDs and their names (First or First and Last) from both tables.

    I wanted the first name field on the form to auto-fill as people typed with values from qryFirstAndLastID.peopleFirstName so I set that as the row source on the form; so far so good. I read the tutorial located at Access Tips: Cascading Lists for Access Forms and entered the following code on the form:

    Code:
    Private Sub Form_Current()
       On Error Resume Next
       cboLastName = DLookup("[peopleLastName]", "qryFirstAndLastID", "[peopleFirstName]='" & cboFirstName.Value & "'")
       cboFirstName.RowSource = "Select qryFirstAndLastID.peopleFirstName " & _
                "FROM qryFirstAndLastID " & _
                "WHERE qryFirstAndLastID.peopleLastName = '" & cboLastName.Value & "' " & _
                "ORDER BY qryFirstAndLastID.peopleFirstName;"
    End Sub
    
    Private Sub LastName_AfterUpdate()
       On Error Resume Next
       cboFirstName.RowSource = "Select qryFirstAndLastID.peopleFirstName " & _
                "FROM qryFirstAndLastID " & _
                "WHERE qryFirstAndLastID.peopleLastName = '" & cboLastName.Value & "' " & _
                "ORDER BY qryFirstAndLastID.peopleFirstName;"
    End Sub
    I did this in the hopes that when either field was filled in it would limit the other field to results from qryFirstAndLastID and change those limitations if I re-entered a field and altered it. It did not work, I still got auto-fill on the FirstName combo box but none on the second. I then added

    Code:
    SELECT DISTINCT [qryCombinedFirstAndLastID].[peopleLastName] FROM qryCombinedFirstAndLastID WHERE ((([qryCombinedFirstAndLastID].[peopleLastName]) Is Not Null)) ORDER BY [qryCombinedFirstAndLastID].[peopleLastName];
    as a row source for the LastName combo box and then got auto-fill there but it is not limited to names that only match the FirstName box.

    I then wrote a query (qryNameLookupTestResult) like so:

    Code:
    SELECT qryCombinedFirstAndLastID.peopleID, tblpeople.peopleFirstName, tblpeople.peopleMiddleName, tblpeople.peopleLastName, tblpeople.peopleSuffix, tblpeople.peopleDOB, tblaliasnamechanges.aliasSingleFirst, tblaliasnamechanges.aliasMiddle, tblaliasnamechanges.aliasLast, tblaliasnamechanges.aliasSuffix
    FROM tblaliasnamechanges INNER JOIN (qryCombinedFirstAndLastID INNER JOIN tblpeople ON qryCombinedFirstAndLastID.peopleID = tblpeople.peopleID) ON tblaliasnamechanges.aliasPeopleID = tblpeople.peopleID
    WHERE (((qryCombinedFirstAndLastID.peopleFirstName) Like [Forms]![frmNameLookupTest]![FirstName] & "*") AND ((qryCombinedFirstAndLastID.peopleLastName) Like [Forms]![frmNameLookupTest]![LastName] & "*")) OR (((qryCombinedFirstAndLastID.peopleFirstName) Like [Forms]![frmNameLookupTest]![FirstName] & "*") AND ((qryCombinedFirstAndLastID.peopleLastName) Is Null))
    ORDER BY qryCombinedFirstAndLastID.peopleID;
    to return a list of all people by ID whose name or alias matched the info in the combo boxes. This worked great for people with aliases but did not return anything for people without aliases. When I looked it over the results made sense and I figured I needed to change one of the joins but no matter how much I monkeyed with it I could not get the results I needed.

    So, to recap, I can not get the cascading combo boxes to work and I want to return all values even when no alias exists.

    Is there anyone out there who has built a cascading name look up that could offer up some advice? If so I would be very appreciative.

    BTW: using Access 2000 and I am very new to it, connecting to mySQL using ODBC (so I am limited to a single union in each query), and am only slightly smarter than I look (I would have to be).

    Thanks In Advance
    Last edited by cperkins; 01-11-11 at 04:05.

  2. #2
    Join Date
    Jan 2009
    Posts
    3

    Second Problem Solved

    I looked at my join problem again and solved it with this:

    Code:
    SELECT qryCombinedFirstAndLastID.peopleID, tblpeople.peopleFirstName, tblpeople.peopleMiddleName, tblpeople.peopleLastName, tblpeople.peopleSuffix, tblpeople.peopleDOB, tblpeople.peopleSex, tblpeople.peopleRace, tblpeople.peopleWeight, tblpeople.peopleHeight, tblpeople.peopleEyes, tblpeople.peopleHair, tblpeople.peopleOLN, tblaliasnamechanges.aliasSingleFirst, tblaliasnamechanges.aliasMiddle, tblaliasnamechanges.aliasLast, tblaliasnamechanges.aliasSuffix
    FROM tblaliasnamechanges RIGHT JOIN (qryCombinedFirstAndLastID INNER JOIN tblpeople ON qryCombinedFirstAndLastID.peopleID = tblpeople.peopleID) ON tblaliasnamechanges.aliasPeopleID = tblpeople.peopleID
    WHERE (((qryCombinedFirstAndLastID.peopleFirstName) Like [Forms]![frmNameLookupTest]![FirstName] & "*") AND ((qryCombinedFirstAndLastID.peopleLastName) Like [Forms]![frmNameLookupTest]![LastName] & "*")) OR (((qryCombinedFirstAndLastID.peopleFirstName) Like [Forms]![frmNameLookupTest]![FirstName] & "*") AND ((qryCombinedFirstAndLastID.peopleLastName) Is Null))
    ORDER BY qryCombinedFirstAndLastID.peopleID;
    Thought I had already tried that but I guess I didn't. Guess I just still need help with the cascading combo box.

Posting Permissions

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