Code needed to filter report from form checkbox values

[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 are also checkbox 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

Thank you

[ssanfu]

Dim strChkbox As String

If "strChkbox" is referring to a check box, you would need to declare it as a a Boolean.

What are the names of the check boxes on the form?
What are the names of the fields in the table?

[govuser1]

Yes i think i had that in my code above already in red. I think i even would need 3 of these one for each check box value so that i can pass the results to the query string at the end but im not sure.

chkECC
chkRCPM
chkPP

are the names of my check boxes on my Form where the report selections are made and also in my Report and the Table the report is sourced to.
I'm just not sure what code will query the selection form and then pass the check box value on to the filter so that it only grabs the records with the same checked item in the database

as i stated before the code snipits i have for the Option value group and Multiselect box work perfectly when i click the filter/open report button it filters the report based on user selections on the report form.

[ssanfu]

I'm not real crazy about opening the report first, then applying the filter. But it is your code .... I've added the check for the check boxes; be aware I have not tested it!!! Step through it to ensure it is correct.

Private Sub cmdApplyFilter_Click()
   Dim varItem As Variant
   Dim strChkbox As String  'OK
   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 Then
      strChkbox = "chkECC = TRUE AND "
   End If
   If Me.chkRCPM Then
      strChkbox = strChkbox & "chkRCPM = TRUE AND "
   End If
   If Me.chkPP Then
      strChkbox = strChkbox & "chkPP = TRUE AND "
   End If
   
   If Len(Trim(strChkbox)) > 0 Then
      'remove the last 5 characters => " AND "
      strChkbox = "AND " & Left(strChkbox, Len(strChkbox - 5))
   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 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 & strChkbox

'---------------------------------------------------
'for debugging - comment out or delete when the filter string is correct
MsgBox strFilter
'---------------------------------------------------
   ' Apply the filter and switch it on
   With Reports![Travel1]
      .Filter = strFilter
      .FilterOn = True
   End With
End Sub

[govuser1]

thank you sooo much for attempting to help me on this ...i feel like I was soooo close to getting it to work but just don't have enough knowledge.

with a little tweaking, in blue and red, of what you posted (and yes i know you didn't test it before posting) I was able to make it work perfectly in my Database. You rock!!! Thank you for timely and effective response to my problem.

I have posted this question on 4 different access forums, few responded and those that did asked some questions that seemed to make no sense at all.


Posting the end results in case someone else can use.

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
' removed the code below.... with my limited knowledge im not sure what this was supposed to do but it was causing an error.
If Len(Trim(strChkbox)) > 0 Then
'remove the last 5 characters => " AND "
strChkbox = "AND " & Left(strChkbox, Len(strChkbox - 5))
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 group
Select 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 & strChkbox <kept getting syntax error here but i moved it to the begining of the string and it works now.

'---------------------------------------------------
'for debugging - comment out or delete when the filter string is correct
'MsgBox strFilter
'---------------------------------------------------

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

[ssanfu]

Hints: If you are in the advanced reply form, there is a "#" in the tool bar. This allows you to post code without changing the formatting and indents.
It adds html tags at the beginning and end of the code. The tags are "[ code ]" and "[ / code ]". Note: To get these tags to display properly, I added spaces. There should be no spaces in the tags)

If you are in the Quick Reply box, can type "[ code ]" and "[ / code ]" (without the spaces and quotes) to get the formatting.
----------------------

' removed the code below.... with my limited knowledge im not sure what this was supposed to do but it was causing an error.

This was because I had a closing parenthesis in the wrong spot. My bad

----------------------

If Me.chkPP = True Then
    strChkbox = strChkbox & "chkPP = TRUE AND "
End If

If Len(Trim(strChkbox)) > 0 Then
    'remove the last 5 characters => " AND "
    strChkbox = "AND " & Left(strChkbox, Len(strChkbox) - 5)
End If

So if the control "Me.chkPP" is checked, "strChkbox" value is "chkPP = TRUE AND ". What you want is "chkPP = TRUE". so you have to remove the last 5 characters.


------------------
But as long as it is doing what you want, I'm happy.

[govuser1]

Thank you for the hint, i will use that in the future.

I actually undid all my changes and put yours back in with the correction below. Still works perfectly.

I think adding the "= True" to the

If Me.chkPP = True Then

as well as deleting the snipit about removing the last 5 characters and putting the Filter value at the beggining of the filter string instead of the end helped get the operators in the right place and not have an extra AND at the end. but ultimately i think i mangled it into submission... i like yours better and now i understand what the snipit is doing which to me is a lot more valuable. then "making it work".

thank you again for solving it and the follow up on the code.

This was because I had a closing parenthesis in the wrong spot. My bad

----------------------

If Me.chkPP = True Then
    strChkbox = strChkbox & "chkPP = TRUE AND "
End If

If Len(Trim(strChkbox)) > 0 Then
    'remove the last 5 characters => " AND "
    strChkbox = "AND " & Left(strChkbox, Len(strChkbox) - 5)
End If

So if the control "Me.chkPP" is checked, "strChkbox" value is "chkPP = TRUE AND ". What you want is "chkPP = TRUE". so you have to remove the last 5 characters.


------------------
But as long as it is doing what you want, I'm happy.

[ssanfu]

Actually, I think your way is cleaner.
If any of the check boxes are TRUE, then the strChkbox value ends with an " AND ". Adding it to the beginning of the filter string has the "AND" in the correct spot. If no check boxes are checked, strChkbox value is an empty string, and doesn't add anything to the filter string, so again, it works correctly.

Good job!