Access Diva Search Combo with Text

The difficult I do immediately, the impossible takes a little bit longer.

Forms

Searching Records...

Private Sub cboSearchBy_AfterUpdate()
 
    Select Case Me.cboSearchBy
        Case 1
            Me.lblTypeToSearchName.Caption = "Type to Search by Name..."
            strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                        "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                            "ORDER BY fObsolete DESC , fFileName"
        Case 2
            Me.lblTypeToSearchName.Caption = "Type to Search by Keyword..."
            strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                        "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                            "ORDER BY fObsolete DESC , fFileName"
        Case 3
            Me.lblTypeToSearchName.Caption = "Type to Search..."
            strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                        "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                            "WHERE fPartOfQualitySystems = True " & _
                                "ORDER BY fObsolete DESC , fFileName"
    End Select
 
        Me.txtSearch = ""
        Me.cboSAreaID = ""
        Me.lstFileID.RowSource = strSQL
        Me.lstFileID = Me.lstFileID.ItemData(0)
 
End Sub

Private Sub txtSearch_Change()
On Error Resume Next
 
    If Me.cboSearchBy = "" Then
        Me.cboSearchBy = 1
    End If
 
    Select Case Me.cboSearchBy
        Case 1
            If Me.cboSAreaID <> "" Then
                strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                            "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                                "WHERE [fIdentifier] & ' ' & fFileName Like '*" & Me.txtSearch.Text & "*' AND fAreaID=" & Me.cboSAreaID.Value & " " & _
                                    "ORDER BY fObsolete DESC , fFileName"
            Else
                strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                            "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                                "WHERE [fIdentifier] & ' ' & fFileName Like '*" & Me.txtSearch.Text & "*' " & _
                                    "ORDER BY fObsolete DESC , fFileName"
            End If
        Case 2
            If Me.cboSAreaID <> "" Then
                strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                            "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                                "WHERE fkKeywords Like '*" & Me.txtSearch.Text & "*' AND fAreaID=" & Me.cboSAreaID.Value & " " & _
                                    "ORDER BY fObsolete DESC , fFileName"
            Else
                strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                            "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                                "WHERE fkKeywords Like '*" & Me.txtSearch.Text & "*' " & _
                                    "ORDER BY fObsolete DESC , fFileName"
            End If
        Case 3
            If Me.cboSAreaID <> "" Then
                strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                            "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                                "WHERE [fIdentifier] & ' ' & fFileName Like '*" & Me.txtSearch.Text & "*' AND fPartOfQualitySystems = True AND fAreaID=" & Me.cboSAreaID.Value & " " & _
                                    "ORDER BY fObsolete DESC , fFileName"
            Else
                strSQL = "SELECT fFileID, fFileName, IIf([fObsolete]=True,'x',''), fAreaID, fkKeywords, fIdentifier, fPartOfQualitySystems " & _
                            "FROM tblFiles LEFT JOIN tblFileKeywords ON tblFiles.fFileID = tblFileKeywords.fkFileID " & _
                                "WHERE [fIdentifier] & ' ' & fFileName Like '*" & Me.txtSearch.Text & "*' AND fPartOfQualitySystems = True " & _
                                    "ORDER BY fObsolete DESC , fFileName"
            End If
    End Select
 
    Me.lstFileID.RowSource = strSQL
    Forms!frmInputFiles!sfrInputFiles.Form.RecordSource = "tblFiles"
 
End Sub

Combo Box with a Text Box

This one is a little unique in that it uses a field not seen on the Form. That is you can Search By Name which is seen in the image above or Keyword which are stored in a junction table. It allows for multiple Keywords to be attached to one record.

This is best used with a List Box that shows the records returned and upon selection from the List Box shows in a Subform.

(Now, you might think why not just add another Combo Box because Area is already there. Well, if you start adding a Combo Box for every Search By you want to have you will find yourself running out of room to show the actual results. The Search By makes it so you can search by a multitude of things with the advantage of not overtaking the Form.)

So now that you have decided what you want to Search By you will need to create the Row Source for the List Box which will also be used by the Type to Search By Name or... control, see example below. Notice the Select Case statement, this is how it knows whether you are search by Name or Keyword and is tied to the cboSearchBy combo box.

Now we need to add some code to Type to Search By Name or... On_Change event procedure again utilizing a Select Case statement. That's it, you're done!

...Combo or Text Box

Command Button with Combo and Text Boxes...

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