Query with parameters from form fields doesn't work in code

  • Thread starter Thread starter Jane
  • Start date Start date
J

Jane

To run reports in our application we have a form with fields in which users
enter criteria, like startdate, enddate, state, etc. Our queries on which
the reports are based have criteria like 'Between
[Forms]![Report]![fldStartDate] And [Forms]![Report]![fldEndDAte]'. Works
great for the actual report, but when I access this query in code, like
'Select * from qsFinancialOwes' (which has the above criteria for one of the
fields) I get a message 'Expecting 2 parameters'. BTW, my report form is
loaded and fldStartDate and fldEndDate both have values; the report runs, but
any code access this query in the same routine doesn't.

I believe I've had this work before but can't remember what I did. Any ideas?
 
Jane said:
To run reports in our application we have a form with fields in which
users
enter criteria, like startdate, enddate, state, etc. Our queries on which
the reports are based have criteria like 'Between
[Forms]![Report]![fldStartDate] And [Forms]![Report]![fldEndDAte]'. Works
great for the actual report, but when I access this query in code, like
'Select * from qsFinancialOwes' (which has the above criteria for one of
the
fields) I get a message 'Expecting 2 parameters'. BTW, my report form is
loaded and fldStartDate and fldEndDate both have values; the report runs,
but
any code access this query in the same routine doesn't.

I believe I've had this work before but can't remember what I did. Any
ideas?
Click to expand...


By "access this query in code", I take it you mean using DAO or ADO to open
a recordset. In such cases, the query is processed by the database engine
without any pre-processing by Access, and so the parameters (which is what
the form/control references are) aren't filled in unless you fill them in
before opening the recordset. Hence the message about missing parameters.

You can automatically fill in the parameters like this:

' Note: this code is for DAO.

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("qsFinancialOwes")

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

Set rs = qdf.OpenRecordset
 
Back
Top