How to make a search result blank if the search fields do not contain the data that was searched fo

[donnie.atchison]

I have a form that has three fields that are searched, Tracker Assigned 1, Tracker Assigned 2 and Tracker Assigned 3. I use Text 19 drop down as the search string. I am using the below code to conduct the search. My problem I need help with is when the search text that I am looking for is not in the table it shows a record still. What do I need to do when a search is null to have the fields Tracker Assigned 1, Tracker Assigned 2, and Tracker Assigned 3 show blank.

Private Sub Text19_AfterUpdate()
Dim strFilter As String
Dim sSearch As String
On Error Resume Next


If Me.Text19 <> "" Then
sSearch = "'*" & Replace(Me.Text19.Text, "'", "''") & "*'"
strFilter = "[Tracker Assigned 1] Like " & sSearch & " OR [Tracker Assigned 2] Like " & sSearch & " OR [Tracker Assigned 3] Like " & sSearch
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If


If Me.Recordset.RecordCount = 0 Then 'new line of code
Me.Filter = "" 'new line of code
Me.FilterOn = False 'new line of code
Me.Text19.SetFocus 'new line of code
Me.Text19.Text = "" 'new line of code
Exit Sub 'new line of code
End If 'new line of code


With Me.Text19
.SetFocus
.SelStart = Len(Me.Text19.Text)
End With
End Sub

[davegri]

Try this: I renamed text19 to txtSearch. I also assumed that if not found you want to enter a new record.
Commented out 'ON ERROR RESUME NEXT'. When debugging, that will make it almost impossible to spot ANY kind of error, and in production it's also a very bad idea.

Private Sub txtSearch_AfterUpdate()    
    Dim strFilter As String
    Dim sSearch As String
    'On Error Resume Next
    
    If Me.txtSearch <> "" Then
        sSearch = "'*" & Replace(Me.txtSearch, "'", "''") & "*'"
        strFilter = "[Personnel-unit] Like " & sSearch & " OR [Personnel_Surname] Like " & sSearch & " OR [Personnel_L6] Like " & sSearch
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    
    If Me.Recordset.RecordCount = 0 Then
        If MsgBox("Search not found, Enter new record?", vbInformation + vbYesNo, "   N E W   R E C O R D ?   ") = vbNo Then
            Me.FilterOn = False
            txtSearch = Null
            Exit Sub
        End If
        Me.FilterOn = False
        DoCmd.GoToRecord , , acNewRec
        Exit Sub
    End If
'        Me.Filter = "" 'new line of code
'        Me.FilterOn = False 'new line of code
'        Me.txtSearch.SetFocus 'new line of code
'        Me.txtSearch = "" 'new line of code
'        Exit Sub 'new line of code
'    End If 'new line of code
    
    With Me.txtSearch
        .SetFocus
        .SelStart = Len(Me.txtSearch)
    End With
End Sub

[donnie.atchison]

Thanks this works gret. You saved me so much time.

[davegri]

Glad to help. Good luck with the rest of your project.