Query returns zero recordsets after being opening as a recordset 2

[TimPen]

I have a parameter query based on fields in a form.

If I populate the form and manually open the query, records are correctly returned.
If I use the query in a recordset (the form is open and correctly populated) the vba opening the recordset returns a run-time error 3061 - Too few parameters. Expected 2
If don't stop the code, and manually open the query, no records are returned.

Why is this?

 

[TheAceMan1]

How are ya TimPen . . .

Typically this error rises when a field is mispelled or there's a datatype problem. Could also be due to improper concatenation of the SQL.

In any case post the SQL.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

You may get the too few parameters message if your query has a criteria with a reference to a control on a form. Could you share your SQL view of the query?

Duane
Hook'D on Access
MS Access MVP

 

[TimPen]

SELECT tblCustomer.EMail
FROM tblCustomer
WHERE (((tblCustomer.EMail) Is Not Null) AND ((tblCustomer.DefaultRepId) Like [Forms]![frmGenerateEMailList]![cboRepresentative]) AND ((tblCustomer.CustomerStatusId) Like [Forms]![frmGenerateEMailList]![cboCustomerStatus]));

 

[PHV]

A starting point to open a recordset from a parametized query:

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

Set db = CurrentDb
Set qdf = db.QueryDefs("NameOfQuery")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrf1xa]

Another thing that has caught me before now has been if the field holding your parameter data on the form still has the focus when you run the query - at this point the field is not recognised as updated so the query treats it as null....

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....