Cascading filtering

[stevekos07]

I have a split form frmVolunteerRoster. On this form is a combobox cboSelectHub which allows the user to filter a list of roster dates for volunteers based on their location (Hub). The combobox filters the list fine.

I also have a cmdSearchDate button which has an OnClick event that filters the list based on a date selection in text box txtSearchDate.

What I want to do is to restrict the date search recordset to the filtered list based on the combobox selection.

What is the best way to do this?

 

[stevekos07]

See if my logic is ok here.

I am thinking of something like:

cmdSearchDate_Click()

If Is Null txtSearchDate Then

DoCmd.runSQL SELECT "*" FROM tblVolunteerRoster

Else DoCmd.runSQL SELECT "*" FROM tblVolunteerRoster
WHERE Hub = Me.cboSelectHub

End If

Me.requery

Is this somewhere in the ballpark?

 

[pbaldy]

Uh, no. You can't "run" a select query, only an action query (append, update, delete). What is your existing code? You likely need to add one to the other, using AND.

 

[stevekos07]

Uh, no. You can't "run" a select query, only an action query (append, update, delete). What is your existing code? You likely need to add one to the other, using AND.

Here is the code I have on the OnClick event now:

If IsNull(txtSetRosterDate) Then
Me.RecordSource = "select * from qryVolunteerRoster"
Else: DoCmd.ApplyFilter "", "[RosterDate]=[Forms]![frmVolunteerRoster]![txtSetRosterDate]", ""

End If

 

[pbaldy]

Perhaps

"[RosterDate]=[Forms]![frmVolunteerRoster]![txtSetRosterDate] And Hub = [Forms]![frmVolunteerRoster]!cboSelectHub"

Though I always concatenate form values into the string.

 

[stevekos07]

Perhaps

"[RosterDate]=[Forms]![frmVolunteerRoster]![txtSetRosterDate] And Hub = [Forms]![frmVolunteerRoster]!cboSelectHub"

Though I always concatenate form values into the string.

That didn't work. Maybe I need to set the recordset for the second stage filter based on the value of cboSelectHub? I'm still a little green as far as working out the code to do that.

 

[stevekos07]

Finally got it! I just needed to use a . instead of a ! in the code.

Here is the final code:

If IsNull(txtSetRosterDate) Then
Me.RecordSource = "select * from qryVolunteerRoster"
Else: DoCmd.ApplyFilter "", "[RosterDate]=[Forms]![frmVolunteerRoster]![txtSetRosterDate] And [Base] = [Forms]![frmVolunteerRoster].cboSelectHub", ""

End If

It filters either by Hub or by Date, and if the "Apply Dual Filter" button is clicked this code triggers.

Thanks all!

 

[pbaldy]

Glad you got it sorted; sorry about the cut/paste goof.