June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
2 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Filter query from access form using variable
Posted on 2012-12-31
I would like to limit the output of a query based on the value of a variable on an acces form.
I am sure the answer is simple but the syntax i don't know.
The original query is named qry_PDM_Measures and includes...
- Project Category
- Measure
The combo boxes i want to control are on a form named frm_PDM_Results.....
cboProjectCategory
cboMeasure
When a value is selected from cboProjectCategory i want to limit the list in cboMeasure to those measures where Me.ProjectCategory on the form is equal to Project Category in the query.
Any help most welcome.
Thank you.
I am sure the answer is simple but the syntax i don't know.
The original query is named qry_PDM_Measures and includes...
- Project Category
- Measure
The combo boxes i want to control are on a form named frm_PDM_Results.....
cboProjectCategory
cboMeasure
When a value is selected from cboProjectCategory i want to limit the list in cboMeasure to those measures where Me.ProjectCategory on the form is equal to Project Category in the query.
Any help most welcome.
Thank you.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
5 Comments
Active today
This is called cascading combo boxes. In order to make this work, you must have a table where you have both sets of values. You need to set the RowSource for the 2nd combo to:
SELECT DISTINCT Measure
FROM qry_PDM_Measures
WHERE [Project Category] = Forms!frm_PDM_Results.cboP
You then need to add code two two events, the first is to the cboProjectCategory_AfterUp
Private Sub cbo_ProjectCategory_AfterU
me.cboMeasure.Requery
End Sub
The other is in the Form_Current event. This will make sure that if you go to a record that already has values in those fields, the combo box will display the correct list of items for the already selected Project Category
Private Sub Form_Current
me.cboMeasure.Requery
End Sub
SELECT DISTINCT Measure
FROM qry_PDM_Measures
WHERE [Project Category] = Forms!frm_PDM_Results.cboP
You then need to add code two two events, the first is to the cboProjectCategory_AfterUp
Private Sub cbo_ProjectCategory_AfterU
me.cboMeasure.Requery
End Sub
The other is in the Form_Current event. This will make sure that if you go to a record that already has values in those fields, the combo box will display the correct list of items for the already selected Project Category
Private Sub Form_Current
me.cboMeasure.Requery
End Sub
SweetingA ,
Hey, I have not heard from you in a while.
Nice of you to post on the last day of the year...
;-)
Anyway, here are some popular links to investigate as well:
http://fontstuff.com/access/acctut10.htm
http://www.candace-tripp.net/download/CascadeCombo2007.zip
http://www.candace-tripp.net/download/cascadecombo2k.zip
http://www.candace-tripp.net/download/2table_cascadecombo2k.zip
JeffCoachman
Hey, I have not heard from you in a while.
Nice of you to post on the last day of the year...
;-)
Anyway, here are some popular links to investigate as well:
http://fontstuff.com/access/acctut10.htm
http://www.candace-tripp.net/download/CascadeCombo2007.zip
http://www.candace-tripp.net/download/cascadecombo2k.zip
http://www.candace-tripp.net/download/2table_cascadecombo2k.zip
JeffCoachman
Hello Jeff,
I am not an access programmer as you may have gathered, i am just a person that likes to dabble for fun now and again but i do like to see who is doing well in the league table.
Regards
I am not an access programmer as you may have gathered, i am just a person that likes to dabble for fun now and again but i do like to see who is doing well in the league table.
Regards
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month2 days, 10 hours left to enroll
696 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.