Filtering a report with checkboxes (1 Viewer)

[govuser1]

what can i add to the code below that will also filter the report by 3 unbound check boxes. My employees have 3 required items to obtain before they are certified and i want to be able to filter my report based on any 1, 2 or all 3 and mix and match the requirements. The code below works great for Employee type as an option group and Office location as a multi select list box. I have done internet search every which way and scoured other example databases and i can't find the coding that successfully adds in 3 unbound check boxes. The 3 values in my table the report is based on is also Yes/No values.

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strChkbox As String <do i need 3 of these? like chkbox1, chkbox2 ect?
Dim strOffice As String
Dim strEmployeetype As String
Dim strFilter As String

' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Travel1") <> acObjStateOpen Then
DoCmd.OpenReport "Travel1", acPreview
End If
' Build criteria string from Check boxes


' Build criteria string from lstOffice listbox
For Each varItem In Me.lstOffice.ItemsSelected
strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) _
& "'"
Next varItem
If Len(strOffice) = 0 Then
strOffice = "Like '*'"
Else
strOffice = Right(strOffice, Len(strOffice) - 1)
strOffice = "IN(" & strOffice & ")"
End If
' Build criteria string from fraemployeetype option group
Select Case Me.fraemployeetype.Value
Case 1
strEmployeetype = "='OFFICER'"
Case 2
strEmployeetype = "='SUPERVISOR'"
Case 3
strEmployeetype = "Like '*'"
End Select

' Build filter string
strFilter = "[PORT] " & strOffice & " AND [Employeetype] " & strEmployeetype

' Apply the filter and switch it on
With Reports![Travel1]
.Filter = strFilter
.FilterOn = True
End With
End Sub

 

[SpentGeezer]

The checkboxes are unbound, so what is the filter going to refer to? You have to filter by something in the query/table that the report is based on.

 

[govuser1]

The filter will look at the checkbox value the user entered? ... take the value the user input (i.e. checked or unchecked? yes/no true/false?) and pass it to the end filter string so that when the report populates it is filtered by one, two or all 3 of the checked values which match my checked values in the report based on the table in addition to the choices the user made in the option group and multiselect box?

not sure if im missing something by what you said. The multiselect and optiongroup on the form with the 3 check boxes are also unbound

 

[govuser1]

Bump...can anyone help with the code needed to Filter a report based on 3 check box values where any or all can be true. The report has these same 3 check box values.

Thank you,

 

[govuser1]

ssanfu from accessforums.net was able to answer this question in no time at all and understood exactly what i wanted from my original post. I knew it couldn't be that hard

Here is the code in case anyone else can use for ideas. Blue was what i was asking for

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strChkbox As String
Dim strOffice As String
Dim strEmployeetype As String
Dim strFilter As String

' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Travel1") <> acObjStateOpen Then
DoCmd.OpenReport "Travel1", acPreview
End If

' Build criteria string from Check boxes
If Me.chkECC = True Then
strChkbox = "chkECC = TRUE AND "
End If
If Me.chkRCPM = True Then
strChkbox = strChkbox & "chkRCPM = TRUE AND "
End If
If Me.chkPP = True Then
strChkbox = strChkbox & "chkPP = TRUE AND "
End If

' Build criteria string from lstOffice listbox
For Each varItem In Me.lstOffice.ItemsSelected
strOffice = strOffice & ",'" & Me.lstOffice.ItemData(varItem) _
& "'"
Next varItem
If Len(strOffice) = 0 Then
strOffice = "Like '*'"
Else
strOffice = Right(strOffice, Len(strOffice) - 1)
strOffice = "IN(" & strOffice & ")"
End If

' Build criteria string from fraemployeetype option groupSelect Case Me.fraemployeetype.Value
Case 1
strEmployeetype = "='OFFICER'"
Case 2
strEmployeetype = "='SUPERVISOR'"
Case 3
strEmployeetype = "Like '*'"
End Select

' Build filter string
strFilter = strChkbox & "[PORT] " & strOffice & " AND [Employeetype] " & strEmployeetype

' Apply the filter and switch it on
With Reports![Travel1]
.Filter = strFilter
.FilterOn = True
End With
End Sub