Toggling filter on subform (1 Viewer)

[Johnny Drama]

Hi all:

I've searched and reviewed other posts related to my problem, and I can't seem to figure it out. :banghead:

I've got a form with a subform. In the subform properties a filter is run on load. The filter is Year([Course Date]) = Year(Date()) to filter for all courses taken in the current year.

What I'm trying to do is place a checkbox on the parent form that when checked will remove the filter and requery the subform to show the unfiltered results. I don't think this is difficult, at least for those of you who actually know what you're doing.

Any help would be appreciated. Thanks in advance for your time.

 

[Minty]

On the after update event of your check box something like;

If me.YourCheckBoxName = 0 Then
        With Me.your_SubFormNameHere
       .Form.Filter = ""
       .Form.FilterOn = False
Else
       With Me.your_SubFormNameHere
       .Form.Filter = "Year([Course Date]) = Year(Date())"
       .Form.FilterOn = True
End If

 

[arnelgp]

put code on On Click Event of your checkbox:

Private Sub yourCheckBoxName_Click()
Me.yourSubformName.Form.FilterOn = (Not Me.yourCheckBoxName)
End Sub

or you may elect to change to the recordsource of your form, when it loads:

Select * From yourTableName Where Year([Course Date]) = Year(Date())

and then on your checkbox On Click event:

Private Sub yourCheckBoxName_Click()
If yourCheckBoxName Then
Me.yourSubform.Form.RecordSource="Select * From yourTableName"
Else
Me.yourSubform.Form.RecordSource="Select * From yourTableName Where Year([Course Date]) = Year(Date())"
End If
End Sub

that way you can further filter your subform.

 

[Johnny Drama]

Hi Minty,

I tried using the code you suggested by I receive the following error: Compile error: Else without If.

Not sure what's going on.

 

[Johnny Drama]

Ok,
I managed to get it working by referencing some other posts. This is what I have:

Private Sub FilterCheck_Click()
If Me.FilterCheck = -1 Then
Me.sbfCourses.Form.Filter = ""
FilterOn = False

Else
If Me.FilterCheck = 0 Then
Me.sbfCourses.Form.Filter = "Year([Course Date]) = Year(Date())"
FilterOn = True
End If
End If
End Sub

That works for removing the filter when the check box is checked by the user, but what I also need it to do is reapply the filter when the user unchecks the check box.

Any thoughts?

 

[sneuberg]

Try Changing

FilterOn = False

To

FilterOn = True

in the If part

 

[arnelgp]

Private Sub FilterCheck_Click()
If Me.FilterCheck = -1 Then
Me.sbfCourses.Form.Filter = ""
Me.FilterOn = False

Else
If Me.FilterCheck = 0 Then
Me.sbfCourses.Form.Filter = "Year([Course Date]) = Year(Date())"
Me.FilterOn = True
End If
End If
End Sub

 

[Cronk]

Eh?

I thought if the filter is being applied to the subform the filter on should be applied to the sub for too.

 

[Galaxiom]

There is no need to remove the Filter when you don't want to use it. Simply turn FilterOn to False.

This part of the code can be greatly simplified to the single lines to toggle the filter.

Me.FilterOn = Not Me.FilterCheck

The filter performance can also be greatly improved.

Year([CourseDate]) = Year(Date()) requires every record to be processed.

Instead use:

[CourseDate] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)

This will allow an index on the CourseDate field to return the records with no actual record processing.

 

[Johnny Drama]

Okay,

So I've updated the code which now looks like this:

Private Sub FilterCheck_Click()
If Me.FilterCheck = -1 Then
Me.sbfCPECourses.Form.Filter = ""
Me.FilterOn = Not Me.FilterCheck

Else
If Me.FilterCheck = 0 Then
Me.sbfCPECourses.Form.Filter = "[Course Date] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)"
Me.FilterOn = Not Me.FilterCheck
End If
End If
End Sub

When I check the box the filter is turned off and I can see all records, but when I uncheck the box the filter is not applied...at least I don't think it is because the records in the subform do not go back to being filtered. **sigh**

 

[sneuberg]

Please post the SQL of the Record Source for this sub form or if it is table then a screen shot of the table in design view.


Please disregard. On second look this probably won't help. I was just wonder if the field name [Course Date] was right, but since some of this code has been working then that's not the issue.

 

[Minty]

I would move back to the beginning and start from scratch using a majority of what Galaxiom suggested. (my method was not very streamlined...

Make sure that this filter is saved in the forms filter property;

[CourseDate] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31)

If that works then your code literally needs to only be;

Private Sub FilterCheck_Click()

Me.FilterOn = Not Me.FilterCheck

End Sub

As this will simply turn the filter on or off as desired.

 

[Johnny Drama]

Well, Galaxiom's code for the filter works fine on form load, but the code for the check box toggling of the filter on and off doesn't do anything. Does the subform need a requery command or something?

 

[sneuberg]

It seems like that code should work without a requery. Could you upload your database or a stripped down portion of it. I would like to see what is going on.

 

[Minty]

Well, Galaxiom's code for the filter works fine on form load, but the code for the check box toggling of the filter on and off doesn't do anything. Does the subform need a requery command or something?

Possibly - I'm on my tablet and can't check but

Forms("Your Parent Form name in here").sbfCPECourses.Form.Requery

Should do the job.