• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Filter query from access form using variable

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.
0
SweetingA
Asked:
SweetingA
  • 3
1 Solution
 
Dale FyeCommented:
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.cboProjectCategory

You then need to add code two two events, the first is to the cboProjectCategory_AfterUpdate event.  it should look like:

Private Sub cbo_ProjectCategory_AfterUpdate

    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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
SweetingAAuthor Commented:
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
0
 
SweetingAAuthor Commented:
Hello Fyed,

The result earns the points thats for sure but can you just add one tiny extra bit to the criteria

....And WHERE [Measure] <> Forms!frm_PDM_Results.cboProjectCategory....

or something similar

Regards
0
 
SweetingAAuthor Commented:
Solved, just a couple of bracket problems

WHERE (([Project Category] = Forms!frm_PDM_Results.cboProjectCategory) And ([Measure] <> Forms!frm_PDM_Results.cboProjectCategory))

Will post the points now

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now