Hi,

I am new to MS Access and so I am facing this problem. Basically, I have a form with a combo box that the user must fill out and depending on the value that the user picks from the combo box, I need to generate a union query and put the result of that query into a report.

I have the union query set up i.e. every time I run the query on its own, it asks me for the value of '[Forms]![Form11]![Combo0]' and displays the required result properly. However, I can't seem to get my form to connect to the report which runs the query and displays the result.

Whenever the user selects a value from the combo box of the form, they are directed to the report, however, the report does not run the query for some reason and hence does not display any data on the report.

My form name: form11
Report Name: Report1
query name: union_query

Please help me out!! I would really appreciate it.

Thanks,

Romit.

The form needs to be open when the query runs; the query won't open it for you. A typical setup would be to open the report from the form; that ensures the form input is available for the query. For the report to reflect the values in the query, the query would need to be the record source of the report. Is it? And if so, do text boxes in the detail section have control sources of the query fields?

Hi Paul,

Thanks for the quick reply. As soon as the user picks a value from the combo box of the form, it opens the report and then closes the form. In the report, the recordsource is indeed the union-query and the control source are the fields from the query.

However, after choosing an option from the combo box of the form, when the report opens up, it does not run the query and as a result it does not display any data.

I guess my form is already open when the report opens up and then closes once the report is open.

I guess I'm doing something wrong. please help!!

Thanks,

Romit.

Can you post the db?

Hi Paul,

Sorry for the delay. Here you go, I have attached the database here. I really really appreciate your help!!

Thanks a lot!!

Romit.

Your query never returns any values because the bound column of the combo is the id field rather than the part id. If you change that to 2, your query/report returns values.

Paul, You are a Savior!! Thanks a lot for your help.

Romit.

Happy to help! Gotta be honest; I'm not a fan of the design. Any time you have numbered fields (qty1, qty2...) you've almost certainly violated normalization rules:

Fundamentals of Relational Database Design -- r937.com

You should likely have a related table for the component parts.

Yea, there is a related table. But, at the moment, I'm just trying to learn Access and trying to accomplish all of the things I am required to do in the main project. Thats why the database looked kinda raw, cuz I've been trying to play around with it just to get acquainted to it.

Btw, quick question if you don't mind, once I set up the relationship table, is there a way to display the names corresponding to the ID number in the report?

Thanks!!

Presuming you have a transaction table of some sort with a part ID, and a parts table with the ID and a description, you'd base the report on a query that joined the two tables together on the ID field.