Solved

Filter query from access form using variable

Posted on 2012-12-31
5
380 Views
Last Modified: 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.
0
Comment
Question by:SweetingA
  • 3
5 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:SweetingA
Comment Utility
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
 

Author Comment

by:SweetingA
Comment Utility
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
 

Author Comment

by:SweetingA
Comment Utility
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now