CODEWITHSUNDEEP
vbams-accesssearch
Redoholic
Redoholic

Reputation: 41

Using a textbox with multiple criteria to search multiple fields

Very new to VBA and coding in general. I'm working on a database and I'm trying to build a search for for it.

The database has fields such as document number, document date and different platforms

The search form I'm working on has a subform showing the database, and the database is uodatrd after every key press in the search textbox, kind of like a google search.

The problem is, I need the textbox to do 2 things. First, I need to be able to put multiple criteria in there, separated by commas. For example, I can search parameters such as the date, title and platform the document is based on, ("Android Report 1, 21/01/2020, Android"). Then, I need the textbox to search all the fields in the database and only show me records with all the criteria I have put in.

Sorry if all of this sounds messy, this is my first time asking a question in here and as stated before, I'm a total beginner to coding as a whole.

Thanks in advance

Upvotes: 0

Views: 1089

Answers (2)

Applecore
Applecore

Reputation: 4099

You will need to create a small function that builds the row source on the fly, using the .Text property of the control that is being edited together with the .Value properties of the other controls. The SQL that you build will use LIKE and wildcards. The procedure will look something like:

Private Sub sFindData(strDocument As String, dtmDate As Date, strPlatform As String)
    On Error GoTo E_Handle
    Dim strSQL As String
    If Len(strDocument) > 0 Then
        strSQL = strSQL & " AND DocumentNumber LIKE '*" & strDocument & "*' "
    End If
    If (IsDate(dtmDate)) And (dtmDate <> #12/31/2099#) Then
        strSQL = strSQL & " AND DocumentDate=" & Format(Me!txtDate, "\#mm\/dd\/yyyy\#")
    End If
    If Len(strPlatform) > 0 Then
        strSQL = strSQL & " AND Platform LIKE '*" & strPlatform & "*' "
    End If
    If Left(strSQL, 4) = " AND" Then
        strSQL = " WHERE " & Mid(strSQL, 5)
    End If
    strSQL = "SELECT DocumentNumber, DocumentDate, Platform " _
        & " FROM tblDocument " _
        & strSQL _
        & " ORDER BY DocumentDate, DocumentNumber, Platform;"
    Me!lstSearch.RowSource = strSQL
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sFindData", vbOKOnly + vbCritical, "Error:  & err.n"
    Resume sExit
End Sub

And then this will get called as below:

Private Sub txtDate_AfterUpdate()
    Call sFindData(Nz(Me!txtDocument, ""), Nz(Me!txtDate, #12/31/2099#), Nz(Me!txtPlatform, ""))
End Sub

Private Sub txtDocument_Change()
    Call sFindData(Nz(Me!txtDocument.Text, ""), Nz(Me!txtDate, #12/31/2099#), Nz(Me!txtPlatform, ""))
End Sub

Private Sub txtPlatform_Change()
    Call sFindData(Nz(Me!txtDocument, ""), Nz(Me!txtDate, #12/31/2099#), Nz(Me!txtPlatform.Text, ""))
End Sub

Note that I am not including a partial match on the DocumentDate field as it makes no sense.

Regards,

Upvotes: 1

Applecore
Applecore

Reputation: 4099

Place 3 text boxes (txtDocument, txtDate and txtPlatform), a command button (cmdSearch) and a list box (lstSearch) on an unbound form. Set the list box to have 3 columns. Then, in the OnClick event of the command button place the following VBA code:

Private Sub cmdSearch_Click()
    On Error GoTo E_Handle
    Dim strSQL As String
    If Len(Me!txtDocument) > 0 Then
        strSQL = strSQL & " AND DocumentNumber='" & Me!txtDocument & "' "
    End If
    If IsDate(Me!txtDate) Then
        strSQL = strSQL & " AND DocumentDate=" & Format(Me!txtDate, "\#mm\/dd\/yyyy\#")
    End If
    If Len(Me!txtPlatform) > 0 Then
        strSQL = strSQL & " AND Platform='" & Me!txtPlatform & "' "
    End If
    If Left(strSQL, 4) = " AND" Then
        strSQL = " WHERE " & Mid(strSQL, 5)
    End If
    strSQL = "SELECT DocumentNumber, DocumentDate, Platform " _
        & " FROM tblDocument " _
        & strSQL _
        & " ORDER BY DocumentDate, DocumentNumber, Platform;"
    Me!lstSearch.RowSource = strSQL
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "cmdSearch", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

This checks if there is data in each of the text boxes, and builds up the where part of a SQL string. Notice that it wraps the data for the DocumentNumber and Platform in single quotes as these are text, and it formats the data for the DocumentDate as an unambiguous date and wraps it in octothorpes ("#"). It finally inserts this piece of SQL into a SQL SELECT statement to use as the RowSource of the ListBox.

Regards,

Upvotes: 0

Related Questions