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

[martin DH]

I'm sorry; I don't understand why the BANNER_IDs would not be the same thing. As a user searches for an employee via the combo boxes, the matching records appear in the list box, the user should then be able to click on a record, which opens the record in the form below so that the user can review/edit the record. This happens because when a record is selected, the db pulls the full record and displays it based on the BANNER_ID that matches one in the EMPLOYEE table. BANNER_ID is the unique id for the employee records. Could you explain where my logic is falling short and if there is a way to accomplish this task? Thanks so much!

Martin DH

[Rabbit]

I'm sorry; I don't understand why the BANNER_IDs would not be the same thing. As a user searches for an employee via the combo boxes, the matching records appear in the list box, the user should then be able to click on a record, which opens the record in the form below so that the user can review/edit the record. This happens because when a record is selected, the db pulls the full record and displays it based on the BANNER_ID that matches one in the EMPLOYEE table. BANNER_ID is the unique id for the employee records. Could you explain where my logic is falling short and if there is a way to accomplish this task? Thanks so much!

Martin DH

If the form is bound to a row source, i.e. a table, query, etc., then it has access to those fields for whichever record it is on. So Me.FieldName refers to the field of that record. A bound control is linked to a field while an unbound control is not. So if the control named ControlA is bound to a field named FieldA, there are two ways to access and change that value. One is through the field name. Me.FieldA, the other is through the control, Me.ControlA. A listbox, can only return one value and can only be bound to one field. A listbox only displays values.

So let's say you have a form, FormA, that is bound to table TableA with 5 records, and it has a field called ID.

Let's also say you have a listbox called ListA which displays all the IDs from TableA.

So, when you open the form and it starts on the first record, the form's ID value is the first ID value. When you click a different record on that list, it does NOT change the form's ID value, it changes the value that the List returns.