Object Missing error on code to apply two filters

[chellebell1689]

Hello,

I've got a form that takes the members from my members table and allows me to take attendance. I have it set up with toggle buttons in the footer (so we can see what class we're currently looking at) and I want to apply two filters when we click on a button. The two filters are "SS_Roll = Yes (or True)" and "SS_Class = AD1 (or whatever the class is)". I did some research and found one code for it, but now that I'm getting the missing object error and upon further research, I'm starting to think the code I found was only an excerpt. Below is the code I currently have. It highlights the first line when I hit debug.

Private Sub OptAD1_Click()
    Table![MembersTable].FilterOn = True
    Table![MembersTable].Filter = "[SS_Roll] = " & True And "[SS_Class] = " & AD1
End Sub

*Edit*
Forgot to say, let me know if you need more information! And thanks in advance for the help. This is the first time I've done script for Access on my own (last time I watched a video on how to do something and didn't really understand what I was doing).

 

[pbaldy]

You'd reference the form, not the table. Also your quotes are off. Try


Me.Filter = "[SS_Roll] = True And [SS_Class] = " & AD1
Me.FilterOn = True

Which assumes SS_Class has a numeric data type.

 

[chellebell1689]

Oh, ok. Thank you!

 

[pbaldy]

Happy to help!

 

[chellebell1689]

So, I tried the fix you gave me, but I still get the error, and when I hit debug it still highlights the first line (I tried with my order of the two lines and the order in the fix). The code I posted is absolutely the only code I have on that form. The form pulls information from my members table (with no filters). I want it set up so that when I hit the "AD1" button, it filter only those who are on the SS Roll (which is a yes/no field) and if they are in that class (which will say "AD1"). I tried doing this in macros, but would only do one filter (not both).

 

[pbaldy]

Clarify whether the field name has an underscore or space in it, and the data type of the class field.

 

[chellebell1689]

On the members table the "SS_Roll" does have the "_" and is a "Yes/No" data type; and the "SS_Class" also has the "_" and is a "Short Text" data type.

 

[pbaldy]

I mentioned the syntax was for a numeric data type. Try

Me.Filter = "[SS_Roll] = True And [SS_Class] = '" & AD1 & "'"
Me.FilterOn = True

If you're saying AD1 is the actual value you want to use

Me.Filter = "[SS_Roll] = True And [SS_Class] = 'AD1'"

 

[chellebell1689]

It works!! YAY! Thank you!

(It was the Me.Filter = "[SS_Roll] = True And [SS_Class] = 'AD1'" that worked.)

 

[pbaldy]

Happy to help. I thought AD1 was a form/field/variable at first.

 

[chellebell1689]

Just out of curiosity, is there a way I could do just one quick code that takes the name/caption of the button and fills in the SS_Class filter? Or do I actually have to apply this code to each button and just replace the filter criteria?

 

[pbaldy]

If you mean it would work for any button, something like:

Me.Filter = "[SS_Roll] = True And [SS_Class] = '" & Screen.ActiveControl.Caption & "'"