Access Diva Search Combo Box

Searching Records...

Sometimes you want to create a Search Form where Users can select specific criteria to drill down their data.
There are a number of ways to add a search feature; we are going to look at a few...

Combo or Text Box
Combo Box with a Text Box
Command Buttons with Combo and Text Boxes
Separate Form

Combo Box

Text Box

  'If the combo box is cleared, clear the form filter.
  If Nz(Me.cboFilter.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
 
  'If a combo box item is selected, filter for an exact match.
  'Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.cboFilter.ListIndex <> -1 Then
    Me.Form.Filter = "[fSubject] = '" & _
                     Replace(Me.cboFilter.Text, "'", "''") & "'"
    Me.FilterOn = True
 
  'If a partial value is typed, filter for a partial company name match.
  Else
    Me.Form.Filter = "[fSubject] Like '*" & Replace(Me.cboFilter.Text, "'", "''") & "*'"
    Me.FilterOn = True
 
  End If
 
  'Move the cursor to the end of the combo box.
  Me.cboFilter.SetFocus
  Me.cboFilter.SelStart = Len(Me.cboFilter.Text)

Private Sub cboSAreaID_AfterUpdate()
 
    If Me.cboSearchBy = "" Then
        Me.cboSearchBy = 1
    End If
 
    strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                    "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                        "WHERE fAreaID = " & Me.cboSAreaID.Value & " " & _
                            "ORDER BY fObsolete DESC , fFileName"
    Me.lstFileID.RowSource = strSQL
    Me.lstFileID = Me.lstFileID.ItemData(0)
 
End Sub

Property	Value
Combo Box Row Source	SELECT FAQ.fSubject FROM FAQ;
Auto Expand	No
Limit to List	No

Private Sub txtSearch_Change()
 
    Me.lstTaskID.RowSource = "SELECT tTaskID, tTask, IIf([tDone]=True,'C',''), tSortOrder " & _
                                "FROM tblTasks " & _
                                    "WHERE tTask Like '*" & Me.txtSearch.Text & "*' And tProjectID = " & Me.txtProjectID & " And tDelete = False " & _
                                        "ORDER BY tSortOrder"
 
End Sub

This is a way to Search as you Type on a continuous form posted on the Microsoft 365 Blog. Place you Combo Box in the Form Header and set the properties listed below.

Then add the code below to the On_Change event procedure of your Combo Box.

Or we can use the Combo Box (named cboSearch) to shorten the list of records. In this example we're filtering a List Box. You'll notice this in the After_Update event of the Combo Box. Once you make a selection the List Box re-queries to reflect the results.

This search uses the On_Change event procedure of a Text Box. The list re-filters with each letter you type. In this example a List Box is used but you can easily use this on a continuous form, see example B below.

On Error Resume Next
 
    Me.Form.Filter = "[fFileName] Like '*" & Replace(Me.txtSFileName.Text, "'", "''") & "*'"
    Me.FilterOn = True
 
    Me.txtSFileName.SetFocus
    Me.txtSFileName.SelStart = Len(Me.txtSFileName.Text)

To use Search as you Type on a continuous form, place the code below in the On_Change event procedure of you search control.

Be sure to change the Row Sources, Forms and Controls to match your own!

Combo Box with a Text Box...