Parameter Queries

[NeoPa]

I would like to open a parameter query so that it's visible to the operator (not as a recordset in the VBA code) but I also want to set the parameters before it's opened so that I can control what the operator sees when it's run from a form, but so that it's also possible to run the query natively (double-click the querydef object from the database window) and have it ask for the parameters.

Is this possible?

PS. Please request clarification if anything is not clear.

[NeoPa]

An example of a parameter query (for illustration) would be :
[CODE=SQL]PARAMETERS [Branch] Long, [Start Time] DateTime;
SELECT [Sales_Order_Nbr] AS OrderNo,
[TIME_PICKING_NO TE_PRINTED] AS Printed
FROM Tran_PickPack
WHERE [BRANCH_CODE]=Format([Branch],'00')
AND [DATE_PICKING_NO TE_PRINTED]=Date()
AND [TIME_PICKING_NO TE_PRINTED]>Format([Start Time],'Short Time')
ORDER BY TIME_PICKING_NO TE_PRINTED,
[Sales_Order_Nbr][/CODE]

[MMcCarthy]

Try this ...


[CODE=SQL]
PARAMETERS [EnterBranch:] As Branch Long, [Enter Start Time:] As StartTime DateTime;
SELECT [Sales_Order_Nbr] AS OrderNo,
[TIME_PICKING_NO TE_PRINTED] AS Printed
FROM Tran_PickPack
WHERE [BRANCH_CODE]=Format([Branch],'00')
AND [DATE_PICKING_NO TE_PRINTED]=Date()
AND [TIME_PICKING_NO TE_PRINTED]>Format([StartTime],'Short Time')
ORDER BY TIME_PICKING_NO TE_PRINTED,
[Sales_Order_Nbr][/CODE]

[NeoPa]

That's useful to know Mary (about renaming parameters), but I'm really after supplying the values in code that I already know, before then opening the PARAMETER SELECT query for full interaction with the operator.

I can set a PARAMETER value in code by using the :

qdf(ParmName) = Value

...syntax, but that only allows programmatic access to the resultant data. I want it in a query grid as the operator is used to using when opening the query themselves.

PS. This may be entirely unsupported in Access. If so then I must look at alternative approaches of course.

[MMcCarthy]

Where are you getting the values from?

[NeoPa]

I'm not yet, but I was planning to set up an unbound form for them.

I didn't go there as I was trying to avoid any comments along the lines of "Put a reference to the form controls in place of the parameters." which would tie the query down to working only from the form.

[NeoPa]

I'm particularly interested in the concept of doing this as, once done, I will have more scope to design simpler queries rather than always having to turn results out as reports. Thereby reducing development times.

I appreciate this idea would not work in all environments, but my office is somewhere I can provide some basic queries for my IT colleagues to run competently where necessary.

The more they can handle independently, the less involved I need to be with executing noddy queries.

[MMcCarthy]

I'm not yet, but I was planning to set up an unbound form for them.

I didn't go there as I was trying to avoid any comments along the lines of "Put a reference to the form controls in place of the parameters." which would tie the query down to working only from the form.

Not sure if I'm fully following what you are trying to do but you can use "User Defined" funtions in a query. Therefore you could design a function to return for instance the Long value of branch depending on whatever criteria you set up and call the function as follows:

Parametre getBranch() As Branch Long

I have never tried to use a function in this way so I suggest you run a simple test function to try it out. You can use them in the criteria if you can't use them in the Parametres.

Mary

[Denburt]

I'm really after supplying the values in code that I already know, before then opening the PARAMETER SELECT query for full interaction with the operator.

Couldn't you just build the SQL in code as they fill in the forms info then they could click to see the query in it's design state then when they close the form it replaces the actual values with the parameters?

[NeoPa]

I'm trying to make it easier and less complicated Denburt. I don't want users ever to have to redesign the query when using it (or even be able to really). Thanks for the thoughts though.

[MMcCarthy]

OK Try this ...

Using this SQL
[CODE=SQL]
PARAMETERS [EnterBranch:] As Branch Long, [Enter Start Time:] As StartTime DateTime;
SELECT [Sales_Order_Nbr] AS OrderNo,
[TIME_PICKING_NO TE_PRINTED] AS Printed
FROM Tran_PickPack
WHERE [BRANCH_CODE]=Format([Branch],'00')
AND [DATE_PICKING_NO TE_PRINTED]=Date()
AND [TIME_PICKING_NO TE_PRINTED]>Format([StartTime],'Short Time')
ORDER BY TIME_PICKING_NO TE_PRINTED,
[Sales_Order_Nbr][/CODE]

with this code
[Code=vb]
Dim qdf As QueryDef

Set qdf = CurrentDB.Query Defs("Your Query Name")
qdf.Parameters( "Branch") = 3
qdf.Parameters( "StartTime" ) = Now()
qdf.Execute "Your Query Name"
[/code]

[NeoPa]

Everything works fine until the qdf.Execute statement.

Execute Method

Runs an action query or executes an SQL statement on a specified Connection or Database object.

But...

Remarks

The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs.

[MMcCarthy]

Everything works fine until the qdf.Execute statement.

But...

I'll do some more research :)

However, what happens if you try to open the query as normal instead of executing it?

[MMcCarthy]

I'll do some more research :)

However, what happens if you try to open the query as normal instead of executing it?

OK based on further research it looks like you can't pass parameters in this manner except to an action query. For a select query it seems you can only return a recordset.

replace qdf.Execute "Query Name" with qdf.OpenRecords et("QueryName" )

You could then do a workaround and open a datasheet form based on the recordset rather than the query.

[ADezii]

I would like to open a parameter query so that it's visible to the operator (not as a recordset in the VBA code) but I also want to set the parameters before it's opened so that I can control what the operator sees when it's run from a form, but so that it's also possible to run the query natively (double-click the querydef object from the database window) and have it ask for the parameters.

Is this possible?

PS. Please request clarification if anything is not clear.

Comes with no guarantee whatsoever:
[CODE=sql]
PARAMETERS [Branch] Long, [Start Time] DateTime;
SELECT [Sales_Order_Nbr] AS OrderNo,
[Branch] As Param1, [Start Time] As Param2,
[TIME_PICKING_NO TE_PRINTED] AS Printed
FROM Tran_PickPack
WHERE [BRANCH_CODE]=Format([Branch],'00')
AND [DATE_PICKING_NO TE_PRINTED]=Date()
AND [TIME_PICKING_NO TE_PRINTED]>Format([Start Time],'Short Time')
ORDER BY TIME_PICKING_NO TE_PRINTED,
[Sales_Order_Nbr]
[/CODE]