pass parameter from one query to another query?

  • Thread starter Thread starter SWu
  • Start date Start date
S

SWu

Hi all,

What is the correct way to code queryA if it wants to call queryB? queryB
requires a parameter. I do NOT want to get the dialog box asking me for the
value of the parameter -- queryA already knows what this value is and so
should be able to pass it along with the call.

Any help would be greatly, greatly appreciated.

Thanks,
Stephen
 
The simplest approach is to use a form where the value is entered, and then
have both queries pick up the parameter from the form.

For example, you might set the Criteria under the City field in your query
to:
[Forms]![Form1]![txtCity]

If the field is a date or a number, you need to take steps to ensure Access
understands the data type correctly. Set the Format property of the text box
to Short Date, General Number, or whatever. Then declare your parameter
(Parameters on Query menu). For example you might enter the parameters:
[Forms]![Form1]![txtStartDate] Date


If you need to supply the parameters in code:
Dim qdf As DAO.QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("MyQuery")
qdf.Parameters("[Forms]![Form1]![txtCity]") = [Forms]![Form1]![txtCity]
 
thanks but that's not quite what I'm after. I want to have a query that
many other queries can call. The called query is a parameter query. Each
of the calling queries will know what value they want to pass. I would
prefer not to have the value come from a form. Is this possible?

thanks for your help so far.
stephen

Allen Browne said:
The simplest approach is to use a form where the value is entered, and then
have both queries pick up the parameter from the form.

For example, you might set the Criteria under the City field in your query
to:
[Forms]![Form1]![txtCity]

If the field is a date or a number, you need to take steps to ensure Access
understands the data type correctly. Set the Format property of the text box
to Short Date, General Number, or whatever. Then declare your parameter
(Parameters on Query menu). For example you might enter the parameters:
[Forms]![Form1]![txtStartDate] Date


If you need to supply the parameters in code:
Dim qdf As DAO.QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("MyQuery")
qdf.Parameters("[Forms]![Form1]![txtCity]") = [Forms]![Form1]![txtCity]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SWu said:
What is the correct way to code queryA if it wants to call queryB? queryB
requires a parameter. I do NOT want to get the dialog box asking me for the
value of the parameter -- queryA already knows what this value is and so
should be able to pass it along with the call.

Any help would be greatly, greatly appreciated.

Thanks,
Stephen
Click to expand...
Click to expand...
 
No. Each instance of the query that gets loaded into memory will have its
own independent parameter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SWu said:
thanks but that's not quite what I'm after. I want to have a query that
many other queries can call. The called query is a parameter query. Each
of the calling queries will know what value they want to pass. I would
prefer not to have the value come from a form. Is this possible?

thanks for your help so far.
stephen

Allen Browne said:
The simplest approach is to use a form where the value is entered, and then
have both queries pick up the parameter from the form.

For example, you might set the Criteria under the City field in your query
to:
[Forms]![Form1]![txtCity]

If the field is a date or a number, you need to take steps to ensure Access
understands the data type correctly. Set the Format property of the text box
to Short Date, General Number, or whatever. Then declare your parameter
(Parameters on Query menu). For example you might enter the parameters:
[Forms]![Form1]![txtStartDate] Date


If you need to supply the parameters in code:
Dim qdf As DAO.QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("MyQuery")
qdf.Parameters("[Forms]![Form1]![txtCity]") = [Forms]![Form1]![txtCity]

SWu said:
What is the correct way to code queryA if it wants to call queryB? queryB
requires a parameter. I do NOT want to get the dialog box asking me
Click to expand...
for
the
value of the parameter -- queryA already knows what this value is and so
should be able to pass it along with the call.

Any help would be greatly, greatly appreciated.

Thanks,
Stephen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top