Parameters in a DAO parameter query

As you probably know, in Access a parameter query displays one or
more predefined dialog boxes that prompt you for a parameter value.
In most cases, you use a parameter query to determine criterion. For
example, if you limited a query's result by the parameter, <[Please
Enter A Date], then when you ran the query Access would display a
prompt and ask you to Please Enter A Date and filter the records
accordingly.

When you manipulate these queries in DAO through VBA, however, you'll
need to supply the parameter value before you open the recordset
object. If you don't, DAO generates an error.

To do so, access the Parameters collection of the DAO querydef
object. A querydef is a query's definition, or blueprint. It tells DAO
what the query's layout should be, while a recordset contains the
results generated by the querydef. To illustrate, take a look at the
following code:

Private Sub cmbRunParam_Click()
Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef

Set wrk = CreateWorkspace("", "Admin", "", dbUseJet)
'Change database path
Set db = wrk.OpenDatabase("D:Examplesdb1.mdb")
Set qdf = db.QueryDefs("qryParamQuery")

qdf.Parameters("[Please Enter a Date]") = #8/15/2000#
Set rst = qdf.OpenRecordset()
rst.MoveLast

MsgBox "There are " & rst.RecordCount & " projects to" _
& vbCr & "complete before this date."

Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set wrk = Nothing
End Sub

You might also like...

Comments

ElementK Journals

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates