How do I search on 2 fields in MS Access?

[Desmond Gordon]

I copied the following from a database I have:

Private Sub Combo41_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[First Name] = '" & Me![Combo41] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

It finds the record. But 2 people with the same first name, it finds only the 1st one. I tried adding the [Last Name] in various ways but I get different error messages. Can you search on first and Last name? If so What is the syntax?
Help

[TheSmileyCoder]

If we assume you have a second combobox for the lastname, and add a button to start the search instead of using the afterupdate, it could look like this:

Private strCrit as String
Private Sub btn_Search_Click()
'Error checking
If Isnull(me.cmbFirstName) and isnull me.cmbLastName) then
  Msgbox "No search criteria entered"
  Exit Sub
End If
 
dim strNewCrit as string
If isNull(me.cmbFirstName) then
  'Only search on lastName
  strNewCrit="[LastName]='" & me.cmbLastName & "'"
elseIf isNull(me.cmbLastName) Then
  strNewCrit="[FirstName]='" & Me.cmbFirstName & "'"
else
  strNewCrit="[LastName]='" & me.cmbLastName & "'" & " AND " & "[FirstName]='" & Me.cmbFirstName & "'"
end If
 
 
If strNewCrit=strCrit Then
  'Search allready done once before, move to next record matching
  Me.RecordSet.FindNext strCrit
Else
  strCrit=strNewCrit
  Me.RecordSet.FindFirst strCrit
End If
 
 
If me.RecordSet.NoMatch Then
  Msgbox "No more records matching criteria
end if
End Sub

As a general note: The first time you use a control in code ALWAYS ALWAYS ALWAYS and did I mention ALWAYS give it a meaningful name. Combo41 does not count as a meaningful name.

Hope you can use this.

[NeoPa]

An alternative approach might be to filter the data (See Cascading Combo/List Boxes) rather than moving directly to a record. This would then make all records available that matched the first name. It doesn't really answer your question I know, but it may be something to think about anyway.