Parameter query with Drop Down Options

[noboffinme]

Hi

I have read about the need to create a form with an unbound field reading the values available to do this, but does anyone have a working example?

The steps I've taken;

• Create the query
• Create a form with an unbound field that has the options available as a drop down
• I leave that form open when the query runs

Is there a way to seamlessly get this to run the query showing the drop down options or do I need to write the vba to get the query & form opening manually.

I ultimately want to have a user select a report (from the query) & have the drop down options available to them to choose from.

I've seen lots of good advice & tried to replicate but an example would be great.

Thanks

 

[Beetle]

Some of what you've said doesn't quite make sense, for example;

"Is there a way to seamlessly get this to run the query showing the drop down options ...."

The drop down options aren't shown in the query, they are shown in the form.

"I ultimately want to have a user select a report (from the query)...."

You don't select a report from a query. The query would typically be the record source for the report.

The typical scenario is this;

1) You create a form with one or more unbound controls (text boxes, combo boxes, etc.) where a user can enter or select some values to be used as criteria for a query.

2) You create a query, and in the criteria row of certain fields in the query you enter references to the form controls, such as;

[Forms]![YourFormName]![YourControlName]

*You said you created a query but it's not clear if you entered any references for criteria

3) You create a report that uses the query as its record source. This report would typically be opened via a command button (or some other method) on the form.

So the form provides criteria for the query, the query provides the records for the report and the report displays the desired results to the user.

 

[noboffinme]

Thanks Beetle

I think you have answered my question as I had the expectation that I would see a drop down box available from the query prompt itself.

Instead, I realise the user will see a report format once they select an option from the form.

So what I've done so far is;

• Created a form with an unbound combo box that has the drop down options
• Create a query that references that field
• Make a selection on the forms unbound field
• Run the query which brings up the result selected on the form

Is this the best way to achieve this?

I noticed the report built from this query also works.

So I'm thinking I could code this in vba to open the form, ask the user to make a choice & then run the query or report, then close the form that's what I meant about seamlessly, so the user does have to go through all these steps.

Is this the best Access can do for this process?

 

[Beetle]

If you set this up correctly, the only thing the user would have to do is make the appropriate selection(s) on the form and click a button. In fact, you could even skip the button if you wanted to code it so that the report opens as soon as the user makes the last selection on the form.

From the users perspective, things can't get much easier than that, so I'm not sure what you mean when you say "Is this the best Access can do for this process?". Access can do almost anything you can think of from a UI perspective, you just have to have the right knowledge to make it happen.

 

[noboffinme]

Thanks again

"Is this the best Access can do for this process?" means I would like to see a drop down list straight ffrom the parameter query which Access can't do.

Cheers

 

[boblarson]

means I would like to see a drop down list straight ffrom the parameter query which Access can't do.

Which is something that doesn't exist for any database system of which I'm aware.

 

[neilhwilliams]

Hi Chaps,

I followed the advice here and can make this work beautifully when I use text boxes (unbound); but I have absolutely no idea how to use this with a combo box as you suggest here. How would I pre populate the combobox drop down with the values in my source qry ?!

Regards,
Neil

 

[rodmc]

Hi Chaps,

I followed the advice here and can make this work beautifully when I use text boxes (unbound); but I have absolutely no idea how to use this with a combo box as you suggest here. How would I pre populate the combobox drop down with the values in my source qry ?!

Regards,
Neil

with a combo its the exact same as a text box, you just reference your control in the criteria row, as has been stated above:
[Forms]![Formname]![YourComboName]

To populate your combo, select the combo and then in the properties sheet use an SQL statement in the row source to pull in your combo vales

 

[neilhwilliams]

Thanks RODMC, I did realise where I was going wrong. I was trying to populate the box with values from the qry that obviously hadn't yet been executed !! I'm a bit rusty. I've now populated the boxes with the correct values from the root tables and they work fine. Well, they would if the tables were normalised and not all over the place.

Thanks for your help

 

[ChrisMan]

Some of what you've said doesn't quite make sense, for example;

"Is there a way to seamlessly get this to run the query showing the drop down options ...."

The drop down options aren't shown in the query, they are shown in the form.

"I ultimately want to have a user select a report (from the query)...."

You don't select a report from a query. The query would typically be the record source for the report.

The typical scenario is this;

1) You create a form with one or more unbound controls (text boxes, combo boxes, etc.) where a user can enter or select some values to be used as criteria for a query.

2) You create a query, and in the criteria row of certain fields in the query you enter references to the form controls, such as;

[Forms]![YourFormName]![YourControlName]

*You said you created a query but it's not clear if you entered any references for criteria

3) You create a report that uses the query as its record source. This report would typically be opened via a command button (or some other method) on the form.

So the form provides criteria for the query, the query provides the records for the report and the report displays the desired results to the user.

Hello all

I have got the report working great by following the advice above, but am finding that my source table for the drop down menu gets modified.

I have a table for “Supplier List” which has Supplier Name, ID number, Service Provided, Mailing Address etc. I then use a query to show only the Name, which is what the drop down menu uses.

The data in the report comes from a different table “Material Discrepancy” which lists the Supplier Name, Part Supplied, Non-conformance, Qty Inspected, etc.

So when I go through the motions, print the report, close out the form, and get back to the main menu, the supplier that was #1 in my table has been renamed to the supplier I ran the report for. But only the Supplier Name field is changed. Address, ID, etc are all still the same.

How is this report modifying that table?