Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2011
    Location
    IL, USA
    Posts
    10

    Question Unanswered: Connecting a Form, Report and Query together

    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.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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?
    Paul

  3. #3
    Join Date
    Mar 2011
    Location
    IL, USA
    Posts
    10
    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.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db?
    Paul

  5. #5
    Join Date
    Mar 2011
    Location
    IL, USA
    Posts
    10
    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.
    Attached Files Attached Files

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  7. #7
    Join Date
    Mar 2011
    Location
    IL, USA
    Posts
    10
    Paul, You are a Savior!! Thanks a lot for your help.

    Romit.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  9. #9
    Join Date
    Mar 2011
    Location
    IL, USA
    Posts
    10
    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!!

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •