Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    8

    code to filter report based on check boxes

    Need help I am using the Onclick event of a button to open an unfiltered report and filter based on the criteria selected on the form with the button. I have successfully added an Option group and a MultiSelect box and it works perfectly. The record set i am using has 3 values set as check boxes. I want to incorporate this into the Filter but i am unsure how to accomplish this. I have researched anything related i can think of to point me in the right direction.

    chkECC
    chkRCMP
    chkPASSPORT

    As each one is selected it will filter to employees that have completed more of the requirements

    Here is the code i am using so far for the multiselect and Option group

    Private Sub cmdApplyFilter_Click()
    Dim varItem As Variant
    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 chkbox


    ' 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

  2. #2
    Join Date
    May 2012
    Posts
    8
    Bump anyone?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,520
    so what is happenign or not happening
    what is the value of strFilter
    what is going wrong

    I see lots of code, I see little commeting
    I see no idea of what the problem is

    Speaking entirely for myself if I see lots of code, no comments and no clear idea of where or what the problem is I'm not interested. there's better things to do with my time than trying to dust down a crystal ball and work out what the problem(s) isare
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2012
    Posts
    8
    Reposting with more about what i need since it seemed unclear to the previous person that replied to my post.

    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 to travel 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.

    The green commented items work fine to filter the report.. only posting so you can see the total of what i am using.

    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
    Last edited by govuser1; 12-11-12 at 15:19.

  5. #5
    Join Date
    May 2012
    Posts
    8

    solved by ssanfu from accessforums.net

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •