The difficult I do immediately, the impossible takes a little bit longer.
Searching Records...
Click to send feedback...
Private Sub cmdSetFilter_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Dim ctl As Control
    'Look at each search box, and build up the criteria string from the non-blank ones.
    If Me.cboSAreaID <> "" Then
        strWhere = strWhere & "([fAreaID] = " & Me.cboSAreaID & ") AND "
    End If
    If Me.cboSFileCodeID <> "" Then
        strWhere = strWhere & "([fFileCodeID] = " & Me.cboFileCodeID & " OR [fFileCodeID] = 3) AND "
    End If
    If Me.txtSFileName <> "" Then
        strWhere = strWhere & "([fFileName] Like ""*" & Me.txtSFileName & "*"") AND "
    End If
    If Me.txtSIdentifier <> "" Then
        strWhere = strWhere & "([fIdentifier] Like ""*" & Me.txtSIdentifier & "*"") AND "
    End If
    If Me.cboSFileTypeID <> "" Then
        strWhere = strWhere & "([fFileTypeID] = " & Me.cboSFileTypeID & ") AND "
    End If
    If Me.cboSProcessID <> "" Then
        strWhere = strWhere & "([aProcessID] = " & Me.cboSProcessID & ") AND "
    End If
    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.cboSObsolete = True Then
        strWhere = strWhere & "([fObsolete] = True) AND "
    ElseIf Me.cboSObsolete = 0 Then
        strWhere = strWhere & "([fObsolete] = False) AND "
    End If
    If Me.cboSPartOfQualitySystems = True Then
        strWhere = strWhere & "([fPartOfQualitySystems] = True) AND "
    ElseIf Me.cboSPartOfQualitySystems = 0 Then
        strWhere = strWhere & "([fPartOfQualitySystems] = False) AND "
    End If
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
On Error Resume Next
    Select Case Me.cmdShowAll.Caption
        Case ShowFiltered
            Me.RecordSource = "qryActiveFiles"
            Me.cmdShowAll.Caption = ShowAll
            Me.cmdShowAll.ControlTipText = "Click to Show All"
            Me.cmdShowAll.BorderColor = RGB(167, 218, 78)
        Case ShowAll
            Me.RecordSource = "qryFileList"
            Me.cmdShowAll.Caption = ShowFiltered
            Me.cmdShowAll.ControlTipText = "Click to Show Filtered"
            Me.cmdShowAll.BorderColor = RGB(255, 194, 14)
    End Select
Command Buttons with Combo and Text Boxes
Showing records in a continuous Form is quite common.  The only caveat is if you have a few hundred records or more.  No one wants to scroll the Form to find a record.  To handle this I use two different types of searches on the same Form.  The Form opens to *live* or *active* records and all archived records are *hidden* (because I don't believe in deleting anything).

The first part of this Search, the blue boxes at the top of Form pictured below, utilizes Allen Browne's Search Criteria code which has a sample download here.

Amended code for blue boxes used on the example pictured below...
Now to add a way to toggle back and forth between active or live and archived (all).  At the very top of the Form's Module under...

Option Compare Database
Option Explicit

Const ShowAll = "SHOW ALL"
Const ShowFiltered = "FILTERED"

Now create your Command Button and name it cmdShowAAll.  (Note, you can change the name but then don't forget to change the code.)  To identify when the filter is on (orange) or off (green) I use the border color.  However, you can set yours to show the Command Button's Caption and then just remove the lines that changes the border color.
Once you have your command button on your Form place the below in the On_Click event procedure of said button.  Now your Users can toggle back and forth between live records or all the records.
...Combo Box with Text Box
Search Form...
Be sure to change the Row Sources, Forms and Controls to match your own!
Tips (Main)
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
Drill Down (for Continuous Forms)
Highlight Required Controls (End-User Defined)
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.