The difficult I do immediately, the impossible takes a little bit longer.
Forms
Searching Records...
Click to send feedback...
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!
Tips (Main)
Home
Let your Client customize the form labels
Linking Subforms to an Unbound Control(s) on the Main (Parent) Form
Customize your Main Menu or Switchboard
Auto-Add new child records when adding a new record on the Main (Parent) Form
Simple Bar Chart using Shapes
Simulate a Drop Down box (not a Combo Box) as seen on Web Pages
Implement a Log-On form to control what the User sees
Make Controls resize with a Form
Switch Windows
Assign *Permissions* based on Users Levels
New User Form
Add *Favorites* to a Continuous Form
Custom Record Selectors
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.