If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > code to filter report based on check boxes

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2012
Posts: 8
Bump anyone?
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,665
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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 14:19.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On