Full Version: passing parameters from a form to a query
UtterAccess Forums > Microsoft® Access > Access Forms
I have a query that is used by lots of other queries and also other forms, this query has two parameters that the user need to enter, I am developing several forms for input parameters, each of these forms would need to pass the parameters to this query (actually several reports use this query in combination with other queries to generate reports, I will be using the following to run reports from forms that use parameter queries "DoCmd.OpenReport reportName, acPreview"). So, Each of these reports would need parameter entry forms, each form would have several different parameters. How do I pass parameters from a Form to a query to produce a report?.

Note: "I know how to code reference to controls on the form in the query, but this one query is used by many forms, so I cannot hard code form name in the query (i.e.for eg Form!orderentry!param, this would be suitable for orderentry form only)".
Appreciate any help. Sam
The fact that several forms use the query is not critical. What is critical is that the query always use the same form for its parameters. When you 'code reference to controls' in a query you are specifying where the query should look for its parameter(s) not the form or report that uses the query as its data source. For example I have a form to specify beginning and ending dates for a reporting period. I have a query that looks to that form (via a hard coded reference to the 'parameter' form.). The query doesn't 'know' what reports will be using it as there data source.
CAn alternative approach would be base you output forms and reports on sql queries that you build in code based on the values in the parameter controls.
Hi Glenn,
Thanx for the quick reply. I cannot use the alternative approach at this point (i.e. all code based). It seems like the only way is to 'code reference to controls' in a query pointing to single form. I have some reports that require the two common parameters and additional parameters that are used by other queries, so I suppose I use one form for the common parameters and from there I could pop up another form where other parameters are required.
Thank you very much.
You are welcome, Sam.
You could make one form to query for all possible parameters. Then you could decide on which prompts (controls) to display based on a value passed to the form in the OpenArgs parameter. For example, if the OpenArgs value is equal to 1 prompt for a Date, if 2 prompt for a Date and ID, etc. - you define what happens.
oCmd.OpenForm "frmUserParameters", , , , , acDialog, 1
Note that the acDialog causes code execution to stop here until this form is closed or hidden.
In the form's Load event you can use the OpenArgs value to determine what controls are hidden or displayed.
Select Case Me.OpenArgs
  Case 1:
    Me!txtDate.Visible = True
    Me!txtID.Visible = False
  Case 2:
    Me!txtDate.Visible = True
    Me!txtID.Visible = True
End Select

When the user select your "Close" button, the code behind the Click event should hide the form
Me.Visible = False
which causes the code in the calling procedure to continue execution. Now you access the control's values in the frmUserParameter form. Then you close it
nID = Forms("frmUserParameters)!txtID
dDate = Forms("frmUserParameters)!txtDate
DoCmd.Close acForm, "frmUserParameters"
Now you have values to plug into some SQL.
Odon't know of a way to accomplish this without using some coding. If there is it wouldn't be a flexible method and what you want is the flexibility to use the same form for multiple forms/reports.
Hope that helps.
When the user has entered the values
Re-reading your post, I'd put the code to open the user parameter form in a reports Open event. After the user enters values and "closes" the form, you then hide the form. Its values will still be "available" for use in a query.
How write the query to use parameters that reference that user parameter form. In the reports Close event you can throw the DoCmd.Close to close the form.
Thanx John.
Hope that helped. Good luck on the project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.