How to filter multiple fields in a report using comboboxes in a form

Hi guys, here's my problem. I have a report based on a query that I put inside a form. What I want to do is create 4 comboboxes from which users can choose options that will filter the report without leaving the form or opening the report in a separate window. Also, I want to make the filters dependent on each other, meaning if a select option A on combobox 1, the options i will select on combobox 2 will only be based on the earlier filter on combobox 1 (sorry if I can't explain it clearly.)

So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.

I'm really a beginner in VBA (almost 0 knowledge) but here's my 2 codes so far:

=======
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
Else
Me.qrySalesByLocation.Report.Filter = "[Location]=" & Me.cboLocation
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit

End Sub

==========
Private Sub cboProduct_AfterUpdate()

On Error GoTo Proc_Error
If IsNull(Me.cboProduct) Then
Me.qrySalesByLocation.Report
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery

Else
Me.qrySalesByLocation.Report.Filter = "[Product]=" & Me.cboProduct
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit

End Sub
=====

the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.

Thanks in advance for the help!
Cyrian AgujoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I am afraid that the solution requires some reading-googling
First search for "cascading comboboxes"
Then search for "custom shortcut menu" so that you can right click on your report to open the "filter form"
PatHartmanCommented:
When you have dependent criteria, it is wasteful to apply the filter in the after update event since you have to run it 4 times.  I also don't use filters because I use primarily SQL Server linked tables and since filtering is a local operation, it forces the server to retrieve all the rows and then filter locally.  A more efficient method is to use criteria in your query.  Since either method works with Jet/ACE BE's, I've standardized on the query method.  That way I always do the same thing and don't have to change methods depending on the BE.  It also means that my apps can easily be upsized to SQL Server if the time comes.

Cascading combos:
The RowSource of the dependent combo refers to the parent combo.
Select ... From ... Where SomeFK = Forms!frmYourName!cbo1
Then in the afterUpdate event of cbo1, you clear cbo2 and requery cbo2
me.cbo2 = null
Me.cbo2.Requery
If cbo 3 is dependent on 2 you need to also clear and requery 3 when you select something from 1.

Report RecordSource query criteria:
Where (fld1 = Forms!frmYourName!cbo1 OR Forms!frmYourName!cbo1 Is Null)
AND  (fld2 = Forms!frmYourName!cbo2 OR Forms!frmYourName!cbo2 Is Null)
AND  (fld3 = Forms!frmYourName!cbo3 OR Forms!frmYourName!cbo3 Is Null)
AND  ...
This method allows the criteria to be optional so any or all of the four can be used.  Use a button on the form to run the report.
Cyrian AgujoAuthor Commented:
Hi sir PatHartman, this is I think a good solution. However, is there a way that the report only opens inside the report (not on a new window) after choosing the criteria? The problem I'm having is that the enter parameter dialog pops up when I open the form displaying the report. I can actually live with this as after I ignore it and choose an item from the parent combo, it shows the correct results, but I do want to eliminate it for my users. I assume this is because the query needs an input from the parent combo before running the query?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Cyrian AgujoAuthor Commented:
The process I want is like this:

1. I open the form. It displays the report showing all the records.
2. In the form header, there will be 4 combo boxes that will allow the user to filter the records.
3. Then, after one (or multiple) selections from the combo boxes is (are) made, the report is sorted without leaving the form or opening in a new window.

Sorry if it's a little convoluted :(
Cyrian AgujoAuthor Commented:
Sir ParHartman, did I understand you correctly regarding this:


"Report RecordSource query criteria:
Where (fld1 = Forms!frmYourName!cbo1 OR Forms!frmYourName!cbo1 Is Null)
AND  (fld2 = Forms!frmYourName!cbo2 OR Forms!frmYourName!cbo2 Is Null)
AND  (fld3 = Forms!frmYourName!cbo3 OR Forms!frmYourName!cbo3 Is Null)
AND  ...
This method allows the criteria to be optional so any or all of the four can be used.  Use a button on the form to run the report."

That I should put each "Forms!frmYourName!cbo3 OR Forms!frmYourName!cbo3 Is Null" under criteria of each field in the query? or do i put it via vba?
PatHartmanCommented:
For this type of complex, compound criteria, I prefer to work with the query in SQL view.  Although you can put the expressions on the same criteria line under each column if the designer is in QBE view, Access will rewrite the expressions when the query is saved and the results will be complex and unrecognizable.  For queries with this type of criteria, I always use SQL view only.  If you save in SQL view, Access won't rewrite the criteria string.

I've never embedded a report in a form so I'm not sure what problem is causing the prompt.  It is probably a typo.  If Access doesn't recognize a reference in a query, it assumes it should prompt.  As long as the query references controls on an OPEN form, you shouldn't be prompted.
Cyrian AgujoAuthor Commented:
Actually Sir, when the form is open, it doesn't prompt for the parameter. However, I also want to place the form in a navigation form, and when I do, that's when it prompts. I've tried entering codes and macros in the "on load" and "on open" events of the navigation form but haven't been successful.
PatHartmanCommented:
Putting the form on a navigation form makes it a subform and that changes the format of the reference.

Forms!mainformname!subformname.Form!subformcontrolname

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cyrian AgujoAuthor Commented:
thanks so much Sir, that did the trick!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.