Filtering a form

[Guest]

I had a friend write the following code to filter report output based on the
state of two check boxes in the form. The code is stored in the On Click
event of the button that runs the report.

I would like to know how to apply these filters to records that appear in
the form itself. Where should I paste the code and do I need to change the
code at all?

Private Sub Report_Click()
On Error GoTo Err_Report_Click
Dim sWhere As String
Dim stDocName As String

If Me.chkShowStale = True Then
sWhere = "StaleFlag = 'Current'"
End If
If Me.chkHideInactive = True Then
If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
sWhere = sWhere & "[Majorstatusgroup] <> 'Inactive'"
End If
stDocName = "ReportingQuery"
DoCmd.OpenReport stDocName, acPreview, , sWhere

Exit_Report_Click:
Exit Sub

Err_Report_Click:
MsgBox Err.Description
Resume Exit_Report_Click

End Sub

 

[Nikos Yannacopoulos]

Paste the following sub in the form's module:

Private Sub Filter_Form()
Dim sWhere As String

Me.FilterOn = False
Me.Filter = ""

If Me.chkShowStale = True Then
sWhere = "StaleFlag = 'Current'"
End If
If Me.chkHideInactive = True Then
If Len(sWhere) > 0 Then sWhere = sWhere & " AND "
sWhere = sWhere & "[Majorstatusgroup] <> 'Inactive'"
End If
If IsNull(sWhere) Then Exit Sub
Me.Filter = sWhere
Me.FilterOn = True

End Sub

Then call it from both checkbox's On Change event, like:

Filter_Form

and the form filter will adjust on the fly as you check/uncheck each.

HTH,
Nikos