Changing Search Criteria

[glamster7]

Ok I have the following code which allows the users to search by Client Id No -- but they would prefer to search by Client_First_Name & Client_Surname (which are two fields in tblClient Details). I have been having a go at this & can change the search to search on Client_Surname but this only shows the first record with that surname. What I would like is for the user to enter First Name Surname in the text box(es) & click the search button & pull back all the clients with that name. Is it possible to convert what I have code wise to do this??

Private Sub Command53_Click()
Dim strClientIdNo As String
    Dim strSearch As String
 
    If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![Text54].SetFocus
    Exit Sub
End If
 
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Client_Id_No")
    DoCmd.FindRecord Me!Text54
 
    Client_Id_No.SetFocus
    strClientIdNo = Client_Id_No.Text
    Text54.SetFocus
    strSearch = Text54.Text
 
    If strClientIdNo = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        Client_Id_No.SetFocus
        Text54 = ""
 
         Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            Text54.SetFocus
    End If
End Sub

[glamster7]

Forgot to mention I posted this a little while a go with a slightly different title but I seem to have posted it under Access Articles --- could one of the mods please delete that one for me

[Jim Doherty]

Ok I have the following code which allows the users to search by Client Id No -- but they would prefer to search by Client_First_Name & Client_Surname (which are two fields in tblClient Details). I have been having a go at this & can change the search to search on Client_Surname but this only shows the first record with that surname. What I would like is for the user to enter First Name Surname in the text box(es) & click the search button & pull back all the clients with that name. Is it possible to convert what I have code wise to do this??

Private Sub Command53_Click()
Dim strClientIdNo As String
    Dim strSearch As String
 
    If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![Text54].SetFocus
    Exit Sub
End If
 
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Client_Id_No")
    DoCmd.FindRecord Me!Text54
 
    Client_Id_No.SetFocus
    strClientIdNo = Client_Id_No.Text
    Text54.SetFocus
    strSearch = Text54.Text
 
    If strClientIdNo = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        Client_Id_No.SetFocus
        Text54 = ""
 
         Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            Text54.SetFocus
    End If
End Sub

If you send me your email by PM I will mail you a fully functional demo of how you can search fields in combination with each other using wildcards also with the ability to output the returned dataset to excel if you wish and also add to the same table of people all from the same form. It has a number of techniques you might find useful in your learning process

Regards

Jim