VBA Search Using Text Box in Access

Question

I'm very new to both Access and VBA. I've created a search button that looks for different items depending on what is selected on the different combo boxes. However, I would like to add another search criteria where if I type text into a texbox called "txtNotes" I can look for records that look like a match on a table field called "Notes" inside the "tbl_ContructionOrders" table. The matches have to be somewhat loose as everyone types notes differently and would like to use this box to find work orders where we had similar issues and maybe have an easier way finding a solution for said problems.

This is what I have so far and it is not working

Private Sub btnLookup_Click()
Dim strWhere As String


Me.Filter = True
strWhere = ""


    If IsNull(Me.txtNotes) Then
    
        If Not IsNull(Me.txtNotes) Then
        
            If strWhere <> "" Then
                strWhere = strWhere & " like [Notes] = """ & Me.txtNotes & """"
            
        Else
                strWhere = strWhere & "[Notes] = """ & Me.txtNotes & """"
             End If
         End If
         
         If Len(strWhere) <= 0 Then
        MsgBox "No Criteria", vbInformation, "No Input."
    Else
        Me.Filter = strWhere
        Me.FilterOn = True
        Me.Requery
    End If
    
    
    If Me.FilterOn Then
        If Me.Recordset.RecordCount = 0 Then
            MsgBox "Nothing Found."
        End If
        
    End If
    End Sub

I suppose I want to filter the records that somewhat match what I type in the text box — Christian Cruz

Kostas K. Kostas K. · Accepted Answer · 2020-10-20T14:56:45

Try this:

Private Sub Command0_Click()

    'empty notes
    If IsNull(txtNotes.Value) Then
        MsgBox "No Criteria", vbInformation, "No Input."
        Exit Sub
    End If
    
    'search
    Filter = "[Notes] Like '*" & txtNotes.Value & "*'"
    FilterOn = True
    
    'count records
    If RecordsetClone.RecordCount = 0 Then
        MsgBox "Nothing Found."
        FilterOn = False
    End If
    
End Sub

If you want to search (filter) as you type, use this:

Private Sub txtNotes_Change()

    'search after x number of chars
    If Len(txtNotes.Text) <= 3 Then
        FilterOn = False
        Filter = vbNullString
    Else
        Filter = "[Notes] Like '*" & txtNotes.Text & "*'"
        FilterOn = True
    End If
    
End Sub

VBA Search Using Text Box in Access

2 Answers