Parameter Queries

[epicmove]

Hi there.

I am looking to rationalise some of the queries I have in my access application.

Let's say I have two forms that query the same table based on a value they have in a combo box.

Currently I would have a separate query for each form and set the criteria to the name of the control on the respective form.

My question is whether I can use just 1 parameter query and use this for both forms, passing the necessary criteria - similar to how you would with an SQL Server stored procedure.

I have done this in the past programatically but wondered whether it is possible to do this within the GUI?

One solution is to not have any criteria in my query and then define an SQL statement in the rowsource of my form/control that queries my original query i.e.:
"SELECT FirstName, LastName from Qry_Customers WHERE QryCustomers.CustomerID = ????"

This works but it not as easy to maintain as having separate queries (same reason why I prefer to use stored procedures rather than definining my SQL statements within my code)

Also are there any negative performance impacts in defining the rowsource in this way (opposed to just setting to an existing Table/Query)?

Thanks,

 

[Dennisk]

one possible way may be to create either a global variable or a static variable within a function.

This would be initialised from the form before the query run. Then you can pick up this value in the query using a function.

You may also be able to create the query on the fly and add it to the query collection on a temporary basis. Thereby changing the reference to the appropriate form.