ms access filter subform based on multiple checkboxes

[strive4peace]

hi strive. nothing is happening when i ticked and unticked the checkboxes... is this have to do with line "
.Filter = vFilter
.FilterOn = True "?

No ... did you trigger the code to run on the AfterUpdate event of each of your checkboxes?

put this in the [Event Procedure] code of the AfterUpdate event of each checkbox filter control:
call SetMyFormFilter

 

[Kobs]

yes strive I've already done that with all checkboxes

 

[strive4peace]

yes strive I've already done that with all checkboxes

perhaps then the filter isn't right. You need to change your fieldnames, of course. To test the filter, write its value to the debug window after it is constructed
Debug.Print vFilter
then you can examine it to see if it looks right, but if you can't tell the problem, create a new query on that table. Then edit the SQL statement, adding a WHERE clause with the filter that the form constructed.
Ctrl-G to show the Debug window, which is labeled "Immediate"
then you can fix the problem in the query and adjust your code accordingly

 

[Kobs]

there is only one field which is CRN. Where do i place the "Debug.Print vFilter "? I dont know how to code the query. can you make an example please and where to put it. please help im completely lost and new to access. please be patient with me.

 

[strive4peace]

Kobs, I'm out of time right now ... perhaps someone else can step in?

 

[Kobs]

ok strive. thanks. who can help me?

 

[Kobs]

one thing. i did not put the option explicit because its causing error when compiling. Is that ok?

 

[MajP]

Is that ok?

That is the value of option explicit. Allows you to find problems at compile time.

 

[Kobs]

What I must do now? pls help.

 

[MajP]

Can you post your sample DB? Without seeing what you have we are just guessing at what you problems is.

 

[Kobs]

Here's the code strive taught me.

Private Sub chkIndigent_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkPWD_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkRegular_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkSenior_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkSingleParent_AfterUpdate()
Call SetMyFormFilter
End Sub


Private Function SetMyFormFilter()
'crystal (strive4peace)

Dim vFilter As Variant _
, i As Integer

'initialize value of filter
vFilter = Null

'use controls on the form
With Me
'1
vFilter = (vFilter + " AND ") 'obviously first time, this won't change -- included it for consistency
If Me.chkSenior = False Then
vFilter = vFilter & " NOT "
End If
vFilter = vFilter & " ([CRN] like ""01*"")"
'2
vFilter = (vFilter + " AND ")
If Me.chkPWD = False Then
vFilter = vFilter & " NOT "
End If
vFilter = vFilter & " ([CRN] like ""02*"")"
'3
vFilter = (vFilter + " AND ")
If Me.chkIndigent = False Then
vFilter = vFilter & " NOT "
End If
vFilter = vFilter & " ([CRN] like ""03*"")"
'4
vFilter = (vFilter + " AND ")
If Me.chkSingleParent = False Then
vFilter = vFilter & " NOT "
End If
vFilter = vFilter & " ([CRN] like ""04*"")"
'5
vFilter = (vFilter + " AND ")
If Me.chkRegular = False Then
vFilter = vFilter & " NOT "
End If
vFilter = vFilter & " ([CRN] like ""05*"")"
' ... and so on

'set the filter for the form and turn it on
.Filter = vFilter
.FilterOn = True

End With

End Function

 

[MajP]

Rather see the DB. Hard to troubleshoot naming, events, etc. Hard to see what if any is not working.

 

[Kobs]

What do you mean DB? Sorry I cannot attach the DB here.

 

[Kobs]

There are 5 checkboxes that filter the 1st column CRN and there is a subform containing a table the will be filtered depending on the checkboxes ticked or unticked.

 

[MajP]

Right before the end of the function put
debug.print vFilter

Then look in the immediate window and post here what the filter is.

Why can you not post a sample db?

 

[Kobs]

The immediate window is empty

 

[MajP]

Based on what you show I do not see that as possible, unless the function is not being called. That too would be kind of difficutl. At the beginning of the function put
MsgBox "In Function"
To verify the function is being called.
Recommend you post a stripped version of your database. You only need a few record and those you can scramble and personal data.

 

[Kobs]

How to post sample DB in here?

 

[Kobs]

the MsgBox is not prompting the message

 

[MajP]

Zip your file. And then hit the attach files in the lower left corner.

the MsgBox is not prompting the message

That shows the function is not being called. The only way I can think that can happen is if you typed these handlers into the vbe

Private Sub chkIndigent_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkPWD_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkRegular_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkSenior_AfterUpdate()
Call SetMyFormFilter
End Sub

Private Sub chkSingleParent_AfterUpdate()
Call SetMyFormFilter
End Sub

But in the event properties you do not have the words [Event Procedure]
If you want a detailed explanation of that

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk