Problem with OpenRecordset with query 1

[Inky2201]

Hello,
I have a problem that I cannot overcome.
I am trying to retrieve records from a query using the code:

Set rcd = db.OpenRecordset ("qryCustomer", dbOpenDynaset)

but it comes up with the error "Expected parameters 2" why is this and what do I do do get over this.

Thanks

I

 

[RoyVidar]

Probably because the query contains parameters. If they are parameters from forms, you could probably do something like this:

[tt]dim qdf as dao.querydef
dim rs as dao.recordset
dim prm as dao.parameter
set qdf=currentdb.querydefs("qryCustomer")
for each prm in qdf.parameters
prm.value=eval(prm.name)
next prm
set rs=qdf.openrecordset()[/tt]

Roy-Vidar

 

[Inky2201]

Thankyou Roy,
I will try it out.
I

 

[Inky2201]

Thankyou Roy, it worked a treat.
Regards to you
I

 

[akcam2006]

Inky,

Thank you for asking this question. I have been having the same problem.

Roy,

When I used the above fix in my project it fixed the paramitor error but now I get a data type conversion error. Any ideas?

Here is the code I am using:

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rsJobs As DAO.Recordset
Dim bJobCount2 As Byte
Set qdf = CurrentDb.QueryDefs("qryQAFilterTA")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rsJobs = qdf.OpenRecordset("Select Count([Kit_Type])as Jobs From qryQAFilterTA WHERE Kit_Type = " & Chr(34) & Me![Kit_Type] & Chr(34))
bJobCount2 = Nz(rsJobs!Jobs)
Me.Text26 = bJobCount2
rsJobs.Close
Set rsJobs = Nothing
Set qdf = Nothing

Thanks

 

[RoyVidar]

I don't know much about DAO, but I thought you'd need to use the qdf.openrecordset() without adding additional parameters. Someone else may perhaps step in on that.

Perhaps try a .findfirst on the recordset after opening it?

Anyway, data type mismatch usually means there's a mismatch between the datatype of the criterion and the field. Here you're treating Kit_Type as numeric, if it is text, you'll need single quotes (or double quoting):

[tt]...WHERE Kit_Type = '" & Me![Kit_Type] & "'") ' or
...WHERE Kit_Type = """ & Me![Kit_Type] & """")[/tt]

Also bJobCount2 is defined as byte, is that enough (0-255), or should you use integer/long?

Roy-Vidar

 

[akcam2006]

Roy,

I'll give it a shot and see if it fixes the problem.

As for the use of byte, good call. Didn't even think about that.

akcam2006