As long as the contact address data is included in the query, you can search the query for it the same as any other.
But because the address data is not part of the main form, you cannot use it in the criteria to open the form. What you can do is this:
- Include the address in the search criteria as you did for the other two
- Instead of DCount, use DLookup with the Nz function to return a CustomerID that matches, or 0 if none is found
- Open the form using the customer ID as the criteria
I'll let you modify the initial strCriteria string to include the address, then do something like this:
Code:
Private Sub btnSearch_Click()
Dim strCriteria As String, MatchID as Integer
'
' Modify this part to include address
'
strCriteria = "[CustomerID]='" & Replace(Me.txtSearchBox, "'", "''") & _
"' OR [CustomerName]='" & Replace(Me.txtSearchBox, "'", "''") & "'"
MatchID = nz(DLookup("[CustomerID]", "qrySearch", strCriteria),0)
If MatchID > 0 Then
strCriteria = "[CustomerID] = " & MatchID
DoCmd.OpenForm "frmCustomersViewer", acNormal, , strCriteria
Else
MsgBox "No results!"
End If
End Sub
This assumes you cannot have a CustomerID = 0!
If more than one record in the query matches the criteria, you will get the first one it finds.
HTH
John