OpenRecordset method is gutting query

[cooke.christopher]

I have a select query that pulls a date criteria from a simple form.

Query: CompleteDate
Field: submittedDate
Criteria: Forms![Form1]![txtStartDate]

This works quite exactly as expected when I open the query normally to
view it.

However, the form also has a command that initiates some VBA code to
cycle through the records in the query and perform an action with each
one. The code fails with the dynamic criteria shown above BUT runs
fine if I manually enter a date as the criteria or omit criteria.

When ever the following line of code is executed

Set rs = db.OpenRecordset("CompleteDate")

I get the error "Run-time error '3061': Too few parameters. Expected
1"

I go into debug and check out the query, all the records are gone/
hidden.

Help would be appreciated

 

[Dirk Goldgar]

I have a select query that pulls a date criteria from a simple form.

Query: CompleteDate
Field: submittedDate
Criteria: Forms![Form1]![txtStartDate]

This works quite exactly as expected when I open the query normally to
view it.

However, the form also has a command that initiates some VBA code to
cycle through the records in the query and perform an action with each
one. The code fails with the dynamic criteria shown above BUT runs
fine if I manually enter a date as the criteria or omit criteria.

When ever the following line of code is executed

Set rs = db.OpenRecordset("CompleteDate")

I get the error "Run-time error '3061': Too few parameters. Expected
1"

I go into debug and check out the query, all the records are gone/
hidden.

When you open the query via the Access user interface, the parameter
"Forms![Form1]![txtStartDate]" is recognized by Access as a reference to a
control on a form and automatically filled in. When you use the DAO
OpenRecordset method, Access is not involved and DAO doesn't know how to
fill in the parameter.

Here's a general-purpose way to get DAO to fill in such parameters in stored
queries:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("CompleteDate")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next Prm

Set rs = qdf.OpenRecordset