Searching by unique ID text boxes or name combo boxes

[martin DH]

I have an Access database of employees.
Each employee has a unique identification number called "BANNER_ID" .
Each employee's last name and first name are in the database but are not unique (obviously).
The data is stored in a table named EMPLOYEE.

I want to create a search option whereby managers can search for an employee by "BANNER_ID"
by entering the number and pressing a command button named "Search".

I want to create an accompanying search option whereby managers can search for an employee
by selecting his or her last name from a combo box (LAST_NAME) and then first name from
another combo box (FIRST_NAME).
When a last name is selected, I would like the combo box of first names to narrow to only
first names that are associated with the selected last name.

I want both search options to be available.

I have been helped with the following code that searches for employees based on their
last name. However, I am having trouble adapting it to work with my new needs listed above.

Private Sub cmdSearch01_Click()
    Dim strStudentRef As String
    Dim strSearch As String
 
'Check txtSearch01 for Null value or Nill Entry first.
 
    If IsNull(Me![txtSearch01]) Or (Me![txtSearch01]) = "" Then
        MsgBox "Please enter a last name!", vbOKOnly, "Martin DH Message"
        Me![txtSearch01].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch01
'and evaluates this against values in LAST_NAME
 
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("LAST_NAME")
    DoCmd.FindRecord Me!txtSearch01
 
    LAST_NAME.SetFocus
    strStudentRef = LAST_NAME.Text
    txtSearch01.SetFocus
    strSearch = txtSearch01.Text
 
'If matching record found sets focus in LAST_NAME and shows msgbox
'and clears search control
 
    If strStudentRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Martin DH Message"
        LAST_NAME.SetFocus
        txtSearch01 = ""
 
    'If value not found sets focus back to txtSearch01 and shows msgbox
        Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Martin DH Message"
            txtSearch01.SetFocus
    End If
End Sub
 

I would appreciate any help available in developing these search options. Thank you!

Martin DH

[Rabbit]

The Cascading Combo/List Boxes tutorial will help you set up those comboboxes.

[martin DH]

I have looked over the combo boxes tutorial as well as some other information. I now have a combo box that limits the options in the second combo box, both of which display the selected results in a list (exactly as I wanted it - thank you!). Now, I want to be able to select a record in the list and thereby open another form that contains the detailed information of the record. Here is the code included in my "search" subform:

Private Sub Form_Load()
On Error Resume Next
   ResetCombos Me
End Sub
Private Sub cmdReset_Click()
On Error Resume Next
   ResetCombos Me
End Sub
Private Sub cboLAST_NAME_AfterUpdate()
On Error Resume Next
   SetListBox Me.lstList, Me.[cboLAST_NAME], Me.[cboFIRST_NAME]
End Sub
Private Sub cboFIRST_NAME_AfterUpdate()
On Error Resume Next
   SetListBox Me.lstList, Me.[cboFIRST_NAME]
End Sub
Private Sub lstList_Click()
On Error GoTo Err_cmdOpen_Click
 
DoCmd.OpenForm "Search/Edit Employee Files", , , "BANNER_ID=" & Me.BANNER_ID
 
Exit_cmdOpen_Click:
    Exit Sub
 
Err_cmdOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpen_Click
 
End Sub

I do believe my problem is in the DoCmd.OpenForm code - still new at this and just not sure how to make it work.

Thank you for your help!

Martin DH

[Rabbit]

If Banner_ID is text you have to surround it with quotes, so:

"'" & Me.Banner_ID & "'"

[martin DH]

Ha, of course, quotes - thank you.
Everything is working until I select a record from the list box: at this point a dialog box opens asking me to enter a parameter value for Me!BANNER_ID (the text unique ID for these records). If I do not, the record's detail is not retrieved; if I do enter the correct BANNER_ID, the record's detail is pulled up on the opened form.

So, how do I get past this dialog box (ie selecting a record from the list box opens the record's detail immediately).

Option Compare Database
Option Explicit
Private Sub Form_Load()
On Error Resume Next
   ResetCombos Me
End Sub
Private Sub cmdReset_Click()
On Error Resume Next
   ResetCombos Me
End Sub
Private Sub cboLAST_NAME_AfterUpdate()
On Error Resume Next
   SetListBox Me.lstList, Me.[cboLAST_NAME]
End Sub
Private Sub lstList_Click()
On Error GoTo Err_cmdOpen_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Search/Edit Employee Files"
 
    stLinkCriteria = "[BANNER_ID]=" & "Me![BANNER_ID]"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmdOpen_Click:
    Exit Sub
 
Err_cmdOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpen_Click
 
End Sub

Thank you!

[Rabbit]

You misunderstood what I meant about enclosing Banner_ID in quotes. Take a closer look at my previous post.

[martin DH]

So, now my code is as below; I thought I saw what you were getting at with the quotes. When I select the record from the list box (created by the combo box selection) it simply leaves the first record on screen.

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Search/Edit Employee Files"
 
    stLinkCriteria = "[BANNER_ID]=" & "'" & Me![BANNER_ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks for your help Rabbit.

[Rabbit]

Not a problem.

[martin DH]

Do you have any solution to the new issue, though? When I select the record from the list box (created by the combo box selection) it simply leaves the first record in the database on screen instead of opening the selected record.

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Search/Edit Employee Files"
 
    stLinkCriteria = "[BANNER_ID]=" & "'" & Me![BANNER_ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

martin dh

[Rabbit]

Do you have any solution to the new issue, though? When I select the record from the list box (created by the combo box selection) it simply leaves the first record in the database on screen instead of opening the selected record.

    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Search/Edit Employee Files"
 
    stLinkCriteria = "[BANNER_ID]=" & "'" & Me![BANNER_ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

martin dh

It seems then you don't want to use the Banner_ID from the form but the Banner_ID from the listbox. These are not the same thing.