To Few Parameters error 3061

[MajP]

I have a query for a form that works fine 'qryUTable'

SELECT uTABLE.EmpRegNo, uTABLE.EmpJobTransID, [EmpForename] & " " & [EmpSurname] AS Name, uTABLE.JobsID, TESTjobs.JobName, uTABLE.WEdate, uTABLE.strFormInfo
FROM (uTABLE INNER JOIN tblEmployee ON uTABLE.EmpRegNo = tblEmployee.EmpRegNo) INNER JOIN TESTjobs ON uTABLE.JobsID = TESTjobs.JobsID

WHERE (uTABLE.WEdate)=[forms]![frmDemo]![tWEdt])

ORDER BY uTABLE.EmpRegNo, [EmpForename] & " " & [EmpSurname], TESTjobs.JobName, uTABLE.WEdate;

The parameter is:
uTABLE.WEdate)=[forms]![frmDemo]![tWEdt]

Where [tWEdt] is a unbound control for entering a date.

The query works fine and returns the correct records. When I call this query in code:

  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("qryUTable",   dbOpenDynaset)

I get the error 3061 "To few Parameters. Expected 1."

If I take the criteria out of the query
" uTABLE.WEdate)=[forms]![frmDemo]![tWEdt]"

The code runs fine.
Any help would be appreciated.

 

[Remou]

I do not believe that you can use a query that refers to a form in such a way for a recordset.

 

[bubba100]

Try the following:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim prm as Parameter
Dim SelectQry as QueryDef
set db=CurrentDB
set SelectQry=db.QueryDefs("qryUTable")
For Each prm In SelectQry.Parameters
prm.Value=Eval(prm.Name)
Next prm

Set rs=SelectQry.OpenRecordset(dbOpenDynaset)

 

[PHV]

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

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

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

 

[bubba100]

Yes, PHV you are correct. I am still stuck in the past Access97.

 

[MajP]

Thanks gents,
Learned something new.