Filter Form Status

[burrina]

frmChangeLogs is Main Form,Unbound
frmChangeLog subform is the subform where the filter needs to run.Bound to qryChangeLog which uses tblChangeLog (Continuous Form)
cbofilter is a unbound Combobox
Row Source is "Active";"Archived";"All"
Row Source Type is Value List

tblChangeLog
cStatus Data Type is Yes/No Format True/False

'Filter form by Status. True for Active,False for Archived.

I want to be able to show all records:
1. Active,i.e. True
2. Archived,i.e. False
3. All Records

 

[vbaInet]

You can use IIF() and BETWEEN.

between -1 and -1
between 0 and 0
between 0 and -1

 

[burrina]

Here is my code to try and accomplish it.

Private Sub txtstatus_AfterUpdate() 'Filter Form by Status.


   On Error GoTo txtstatus_AfterUpdate_Error

 Me.Filter = cStatus = Me.txtstatus
        Me.FilterOn = True

If [Forms]![frmChangeLogs]![Change Log subform].[Form]![txtstatus] = "Active" Then
      DoCmd.ApplyFilter "", "[cStatus]=True, """
      
    ElseIf [Forms]![frmChangeLogs]![Change Log subform].[Form]![txtstatus] = "Archived" Then
      DoCmd.ApplyFilter "", "[cStatus]=False, """
      
    ElseIf [Forms]![frmChangeLogs]![Change Log subform].[Form]![txtstatus] = "All" Then
        DoCmd.ShowAllRecords
   End If

   On Error GoTo 0
   Exit Sub

txtstatus_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure txtstatus_AfterUpdate of VBA Document Form_frmChangeLog subform"
End Sub

 

[vbaInet]

Is this the only criteria field you will be using or are there others?

 

[burrina]

No, that's it. Form is simple. No complex code.

 

[vbaInet]

In that case, write the SQL WHERE clause like I explained in my first post:

field between iif() and iif()

Then simply Requery the subform in the After Update event of the combo box.

OR

Create a table with the following values:
All Records, True
All Records, False
Active, True
Archived, False

... and you know what to do from there.