Hi, in my main form I have company information (Name, Address, City, State, etc). I am searching a subform. My subform contains Contact information (First Name, Last Name, Title, etc). I searched for several weeks on this issue and I have had no success. This is my code:
Currently, after the search is performed, the searched word is found with the rest of the contacts that belong to the specific company. And the first record is selected; not the matched record. What would be the code so the matched record is selected after the search?
This portion of the code doesnt work and I want to edit this so it does work. Thanks.
Code:
Dim strSQL As Variant
Dim l_strAnd As String
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
If (Me.txtSearchClientID & "" = "") And (Me.txtSearchFirst & "" = "") And (Me.txtSearchLast & "" = "") And (Me.txtSearchCompany1 & "" = "") Then
' If the search criteria are Null, use the whole table as the RecordSource.
Me.RecordSource = "Accounts"
Else
l_strAnd = ""
strSQL = "SELECT distinctrow Accounts.* " _
& "FROM Accounts INNER JOIN ClientInformation " _
& "ON Accounts.[Account ID] = ClientInformation.[Account ID] " _
& "WHERE "
If Me.txtSearchClientID.Value & "" <> "" Then
strSQL = strSQL & l_strAnd & "ClientInformation.[Client ID] = " & Me.txtSearchClientID
l_strAnd = " AND "
End If
If Me.txtSearchCompany1.Value <> "" Then
strSQL = strSQL & l_strAnd _
& "Accounts.[Company] Like '" _
& Me.txtSearchCompany1.Value & "*'"
l_strAnd = " AND "
End If
If Me.txtSearchFirst.Value <> "" Then
strSQL = strSQL & l_strAnd _
& "ClientInformation.[First Name] Like '" _
& Me.txtSearchFirst.Value & "'"
l_strAnd = " AND "
End If
If Me.txtSearchLast.Value <> "" Then
strSQL = strSQL & l_strAnd _
& "ClientInformation.[Last Name] Like '" & Me.txtSearchLast.Value & "'"
End If
On Error Resume Next
Me.RecordSource = strSQL
If Me.Recordset.RecordCount = 0 Then
MsgBox "Your search returned no results. Please check the spelling and try again.", vbInformation, "No Records"
Me.RecordSource = "Accounts"
RS.Close
Set RS = Nothing
End If
If Me.Recordset.RecordCount > 0 Then
Me.ClientInformation_Subform1.SetFocus
DoCmd.GoToRecord , , "[Last Name] = '" & Me.txtSearchLast.Value & "'"
Currently, after the search is performed, the searched word is found with the rest of the contacts that belong to the specific company. And the first record is selected; not the matched record. What would be the code so the matched record is selected after the search?
Code:
DoCmd.GoToRecord , , "[Last Name] = '" & Me.txtSearchLast.Value & "'"
This portion of the code doesnt work and I want to edit this so it does work. Thanks.