I would use cascading combo boxes.
Create a new form - name it frmSearch
Set the default view to continuous forms
Set the form record source to
Code:
SELECT AllDeathRecords.PersonID, AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.MiddleName, AllDeathRecords.DeathDate
FROM AllDeathRecords
ORDER BY AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.DeathDate;
In the detail section, add text box controls for the last name, first name, DeathDate and PersonID. Set the visible property for the PersonID to FALSE.
In the form header add two unbound combo boxes:
The first combo box - name it cboLastName
The row source would be
Code:
SELECT DISTINCT AllDeathRecords.LastName FROM AllDeathRecords ORDER BY AllDeathRecords.LastName;
In the after update event of cboLastName, add this code:
Code:
Private Sub cboLastName_AfterUpdate()
Me.cboFirstName.Requery
End Sub
The second combo box - name it cboFirstName
The row source would be
Code:
SELECT DISTINCT AllDeathRecords.FirstName FROM AllDeathRecords WHERE AllDeathRecords.LastName = Forms!frmSearch.cboLastName ORDER BY AllDeathRecords.FirstName;
Add a button to the form header. Name it cmdSearch. Set the caption to "Search".
The code for the click event:
Code:
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim sFilter As String
If Len(Me.cboLastName & "") > 0 Then
sFilter = "LastName = '" & Me.cboLastName & "' AND "
End If
If Len(Me.cboFirstName & "") > 0 Then
sFilter = sFilter & "FirstName = '" & Me.cboFirstName & "' AND "
End If
If Len(sFilter) > 0 Then
sFilter = Left(sFilter, Len(sFilter) - 5)
End If
'Debug.Print sFilter
Me.Filter = sFilter
Me.FilterOn = True
Exit_cmdSearch_Click:
Exit Sub
Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
End Sub
Add another button to the form header. Name it cmdClearFilter. Set the caption to "Clear Filter"
The code in the click event:
Code:
Private Sub cmdClearFilter_Click()
On Error GoTo Err_cmdClearFilter_Click
Me.cboLastName = ""
Me.cboFirstName = ""
Me.Filter = ""
Me.FilterOn = False
Exit_cmdClearFilter_Click:
Exit Sub
Err_cmdClearFilter_Click:
MsgBox Err.Description
Resume Exit_cmdClearFilter_Click
End Sub
This is how I set up basic search forms..