How to add VBA filters to reports

[clintonf]

Hey all,

I'm a little new to Access 2007 and need help coding filters in VBA.

Right now my report has 3 fields:
FirstName LastName Grade

In the header of my report are 1 checkbox for each grade (9 - 12).

I want to be able to display, for example, only students in Grade 12 when I click the "Grade 12" checkbox.

The ApplyFilter method won't work because, according to this it can only be used on the OnOpen event.

I've seen the Me.FilterOn method used in some forum postings but I'm having difficulty getting that to work. Here's the code that I have:

Private Sub grade_check_Click()
    Dim strFilter As String
    
    strFilter = "[Grade] = '12'"
    Me.Filter = strFilter
End Sub

I'd appreciate suggestions on what to try, or, even better, some websites that that describe doing to this sort of thing.

Thanks!

[NeoPa]

I'm curious to note that the name of the CheckBox for Grade = 12 appears to be Grade_Check. Nothing in there to indicate it's for a value of 12, which worries me. What other traps may there be if this is not logically set up?

You also describe multiple CheckBoxes, but in such a way as to imply only one would ever be set at a time. This would require a different type of control entirely (Radio Buttons within a Group).

With all these uncertainties it's hard to know what to suggest. There are a number of easy answers but without a better understanding of what you're asking (which you need first before including in the question) they could be quite wrong for wahat you actually need.

Whatever it turns out to be though, the solution will include :

1. A comparison of the value that matches the Type of the field ('12' is fine, but only if the field is textual).
2. Setting the report's .FilterOn property to match whether or not there is anything set in the .Filter property.

Although there's some confusion here, you should be reasonably happy with your attempt so far. The code is on the right lines, but computers are unforgiving objects. If it's nearly right but not quite then it won't work (and you don't get to see how close you were).

[clintonf]

Hey NeoPa,

Thanks for the reply.

I'll clarify what I'm trying to accomplish, thanks for your patience.

Each grade (9 - 12) will have its own checkbox. If I check "Grade 12" only Grade 12's will be displayed. If I then check "Grade 10", grade 10's AND grade 12's will be displayed. If I then uncheck the "Grade 12" checkbox only grade 10's will be displayed. At least, that's what I want to happen.

The code I included above was just for the Grade 12 checkbox and rather hastily assembled, without much thought to good naming practices - my apologies for the ambiguity.

I should also mention that I can already do this by right-clicking on the Grade column in the report and toggling filters on. This is a little clunky though and I would like to do it via the checkboxes.

Thanks!

[NeoPa]

If you can tell me what the names of the various CheckBoxes are then, and the field type of [Grade] in your table, I think I can help you.

[clintonf]

Cool!

Let's see then...
The checkboxes:

• grade_12_chk
• grade_11_chk
• grade_10_chk
• grade_9_chk

Grade is a text field, ie. "twelve", "eleven", etc.

Thanks!

[NeoPa]

In that case you need event procedures to handle each CheckBox being changed. Each would call a single procedure that sets the value of your filter string based on ALL the CheckBoxes. The following code is an example of what might work :

Private Sub Grade_9_Chk_AfterUpdate()
    Call SetFilter
End Sub

Private Sub Grade_10_Chk_AfterUpdate()
    Call SetFilter
End Sub

Private Sub Grade_11_Chk_AfterUpdate()
    Call SetFilter
End Sub

Private Sub Grade_12_Chk_AfterUpdate()
    Call SetFilter
End Sub

Private Sub SetFilter
    Dim strFilter As String

    With Me
        If .Grade_9_Chk Then strFilter = strFilter & " OR ([Grade]='Nine')"
        If .Grade_10_Chk Then strFilter = strFilter & " OR ([Grade]='Ten')"
        If .Grade_11_Chk Then strFilter = strFilter & " OR ([Grade]='Eleven')"
        If .Grade_12_Chk Then strFilter = strFilter & " OR ([Grade]='Twelve')"
        .Filter = Mid(strFilter, 5)
        .FilterOn = (strFilter > "")
    End With
End Sub

[clintonf]

Awesome!

Thanks so much; that's very helpful.

[NeoPa]

I was pleased to help Clinton :-)