Solved Query/combobox help! (1 Viewer)

[pgilmour24]

Hi there,
I am all very new to this and am slightly struggling with queries.

A bit of background on the project I am currently working on - I am a lighting designer and I want to streamline creating lighting schedules and product specification sheets etc. We have several projects we are working on at a time so we need to switch between projects easily and I want to display only the lighting products that are specific to that project.
I have created a table with the projects on (Projects) - they each have their own project number which is obviously unique and is the primary key, the project name and address.
I have created another table with the circuit schedules on (Circuit Schedules)- each light has a project circuit number which is unique and some other basic text and number data and the project number and name which I have linked with the project table.

I have tried to create a query that when you run it, a parameter value box pops up and you choose the project number and name from a dropdown box. Following the tutorials I found online, I created a form (CircuitScheduleTest) which contained a combo box (cmbProjectNumber) with the project number and name in - that's pretty straightforward. I then created a query which should pull up the records according to the combo box entry selected. I did this by opening the query in design view, in the project number field entered the criteria [Forms]![CircuitScheduleTest]![cmbProjectNumber]. When I run this query, a pop up parameters box appears but it only repeats what I've put in the criteria field - see photo - with no drop down box. Incidentally if I type in the project number and name it does generate all of the correct records however I am not the only end user and I need it to be easy and straightforward to use.

Any advice would be very grateful! Again I am new to this and thank you very very much in advance.
Best

 

[theDBguy]

Hi. Welcome to AWF!

You will make your life easier and avoid problems like this if you don't use spaces when naming your objects.

 

[pbaldy]

The parameter prompt is Access telling you it can't find the combo. Either something is spelled wrong, or the form isn't open when the query runs (it needs to be).

 

[arnelgp]

the Form must be Open (not in design view), to be able the
Query to "see" the criteria.

if the form is already open and still asking for parameter,
on your VBA:

docmd.SetParameter "[Forms]![CircuitScheduleTest]![cmbProjectNumber]", Me![cmbProjectNumber]
docmd.OpenQuery "yourQueryname"

 

[pgilmour24]

The parameter prompt is Access telling you it can't find the combo. Either something is spelled wrong, or the form isn't open when the query runs (it needs to be).

Thank you! I didnt realise the form had to be open.

 

[pgilmour24]

the Form must be Open (not in design view), to be able the
Query to "see" the criteria.

if the form is already open and still asking for parameter,
on your VBA:

docmd.SetParameter "[Forms]![CircuitScheduleTest]![cmbProjectNumber]", Me![cmbProjectNumber]
docmd.OpenQuery "yourQueryname"

Thanks! This is very helpful.

 

[pbaldy]

Thank you! I didnt realise the form had to be open.

Happy to help!

 

[mike60smart]

Thanks! This is very helpful.

Hi
Would you not be better off with the standard Main Form for your Project and a Continuous Subform for the Lighting associated with the Project?