none
Using Query Parameters in Reports RRS feed

  • Question

  • Hello,

    I may have some questions in regards to query parameters in Access.

    How could I use them in Reports design view?

    Here is a screenshot:

    What it does not appear in reports design view is the saved query table.  So far, I have tried to set the control source property

    to qrySelectedSales.  Is there something missing in the steps that I have tried to perform in the Query Parameters dialog?

    JohnDBCTX

    Thursday, April 7, 2016 4:19 AM

Answers

  • It's unclear from you question just what you are trying to do.  If you wish to restrict a report's output on the basis of parameters entered at runtime then create the query with parameters, and set the report's RecordSource property (NB: not ControlSource) to the query's name.  Alternatively build the query as the report's RecordSource property in report design view.  Rather than using crude system generated parameter prompts, however, a better solution is to reference controls in an unbound dialogue form as the parameters.  You can then either open the form first, and open the report via a button on the form, or you can set the report up to automatically open the dialogue form when the report is opened.  For examples of the latter see ReportDialogue.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates two methods for automatically opening a dialogue form when a report is opened.

    Ken Sheridan, Stafford, England

    Thursday, April 7, 2016 11:38 AM

All replies

  • It's unclear from you question just what you are trying to do.  If you wish to restrict a report's output on the basis of parameters entered at runtime then create the query with parameters, and set the report's RecordSource property (NB: not ControlSource) to the query's name.  Alternatively build the query as the report's RecordSource property in report design view.  Rather than using crude system generated parameter prompts, however, a better solution is to reference controls in an unbound dialogue form as the parameters.  You can then either open the form first, and open the report via a button on the form, or you can set the report up to automatically open the dialogue form when the report is opened.  For examples of the latter see ReportDialogue.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates two methods for automatically opening a dialogue form when a report is opened.

    Ken Sheridan, Stafford, England

    Thursday, April 7, 2016 11:38 AM
  • Hello, Ken

    I'll take your word for it.  Can you tell me which folder in your OneDrive, which is a cloud server backup drive?

    Thanks for your responses in advance.

    JohnDBCTX

    Thursday, April 7, 2016 11:06 PM
  • It’s the one I gave you the link to.

    Ken Sheridan, Stafford, England

    Friday, April 8, 2016 6:43 AM