Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Use Form to generate a query, then a report (2007)

    Hello,
    I have a database that I'm trying to generate a report, built from a query. I want to possibly use a form with combo box to filter the report. I can get the form to generate the query, but not sure how to get the report to generate from the query.
    I will attach the database. The report I'm looking to generate is the rptByDepartment, which is derived from the qryBy Department. The frmChooseDepartment will generate the query, but not sure how to get the report to generate from this. I want the combo box used to alleviate the possible errors in entering the abbreviated department name.

    I hope this makes sense.

    Thanks,

    Bart Putnam
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Use Form to generate a query, then a report (2007)

    This makes sense.

    1 You don't need to actually open the query, so you can remove the code that opens the query in the after update of the combo box, and also the code that closes the form.

    2 Instead you just need a command button on the form that opens the report. The report uses the query, which in turn uses the combo on the form.

    When I do this I get some parameter prompts. This tells me that the query has some fields in it that are not in the query. You will need to track these down.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use Form to generate a query, then a report (2007)

    I'd remove the condition from qryByDepartment, but make the Dept field visible.
    You can specify a condition in the instruction used to open the report:

    DoCmd.OpenReport "rptByDepartment", acViewPreview, , "Dept=" & Chr(34) & Me.cboDeptCombo & Chr(34)

    (Chr(34) is the double quote character ", that is needed to enclose the text value of cboDeptCombo)

    See the attached version.
    Attached Files Attached Files

  4. The Following User Says Thank You to HansV For This Useful Post:

    bartputnam (2012-09-13)

  5. #4
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Use Form to generate a query, then a report (2007)

    Thanks John and Hans. Hans your changes work perfectly. I knew you'd come up with a quick fix!
    Thanks again,
    Bart

Posting Permissions

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