Parameter Queries 1

[gavinjb]

Does anyone know how to change a Parameter in either ADO/DAO.

I have a query Called qryDailyReport which has a Parameter Called StartDate, which I need to be able to Change in Code, after searching Help I can find plenty of information on how to Create Parameter Queires from scratch but nothing on how to set the vlaues in one.

Gavin,

 

[BobStubbs]

I use this technique in my queries. For example, I have a query which lists information for one employee, taken from an Employees table. I pass this query a parameter taken from a 'chooser' form - but you could set the value in VBA code any way you like.

First, create a public variable in the (declarations) section of a module:

Public strChooserID As String

Now, create a small public function in the module:

Public Function GetChooserID()
    GetChooserID = strChooserID
End Function

In the query design view, set the Criteria cell for the field to be filtered equal to the function name. In my example, I set the EmployeeID field criteria to:

GetChooserID()

You can now set the value of the public variable when the user clicks a button, chooses an option from a drop-down list etc. The value of this variable will be passed via the function to the query.

I hope that this helps.


Bob Stubbs

 

[gavinjb]

Thanks, that worked perfectly for my requirements