Access Diva Searching Records

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

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

...place...

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.