Using a textbox with multiple criteria to search multiple fields

Question

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

Whilst this can be done, it is not a very good idea. Better to use 1 control to search 1 field in the table, possibly combo boxes that list the available values. — Applecore
– Applecore, Commented Jun 8, 2020 at 9:50
In that case, is it possible for me to add a couple of textboxes to the search form, and have each of them perform an independent search on all the fields? Basically, instead of using one textbox and separating everything with commas, have multiple textboxes for each criteria instead. — Redoholic
– Redoholic, Commented Jun 8, 2020 at 11:06

Applecore · Accepted Answer · 2020-06-10 08:40:15Z

1

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,

answered Jun 10, 2020 at 8:40

Applecore

4,0792 gold badges11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Redoholic Over a year ago

Sorry for the late reply, this is still a bit different than what I need. I need to have a subform that is bound to the textbox/es I am using to search parameters, and I need the subform to update on every keystroke (assuming keypress is the best method), only showing records that contain fields with exact or similar values. For example, I write "Android" in the textbox, and the subform will update itself every time I add/remove a letter, only showing me fields that contain the parameter I put in, whether the word "Android" was in title, platform, or any other field. Thanks in advance

Applecore Over a year ago

In that case, rather than setting the RowSource of a ListBox, set the RecordSource of the subform.

Redoholic Over a year ago

I'm having some problems with setting the RecordSource of the subform as the SQL line. Other than that, I'm noticing the subform only shows me one record. Am I doing something wrong?

Applecore Over a year ago

What is the code that you are using to set the RecordSource? And is the subform set as continuous?

Redoholic Over a year ago

Never mind, I managed to figure out what the problem was. The code works perfectly. Thank you so much for your help!

Applecore · Accepted Answer · 2020-06-08 15:15:35Z

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,

Hi, thanks for replying. The problem with this code is that it isn't updated with every keystroke. I'm looking for a way to update a listbox or a subform containing the table with every keypress while the user types the parameters he wants to search.

Collectives™ on Stack Overflow

Using a textbox with multiple criteria to search multiple fields

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related