Error of number of parameters on OpenRecordset method

[Robert Vivian]

I have a pecurliar problem with opening a recordset using a Querydef. I am
running Access 2000 under XP. The essense of the code follows. I am getting
a message that the OpenRecordset method requires 1 parameter but I am
already supplying 1 parameter. Does anyone have any ideas?

Public Sub FormatDetails(lngKey As Integer)

Dim qdfDetail As DAO.QueryDef
Dim rstTemp As DAO.Recordset

Set qdfDetail = dbsAlert.CreateQueryDef("", "select
CDate,TDate,Type,When,Interval,Title from " _
+ "Alert where Key = " + str$(lngKey))
With qdfDetail
Open Recordset from QueryDef.
Set rstTemp = qdfDetail.OpenRecordset(dbOpenSnapshot)
...
rstTemp.Close
End With
End Sub

 

[Tim Ferguson]

Dim qdfDetail As DAO.QueryDef
Dim rstTemp As DAO.Recordset

Set qdfDetail = dbsAlert.CreateQueryDef("", _
"select " & _
" CDate, " & _
" TDate, " & _
" Type, " & _
" When, " & _
" Interval, " & _
" Title " & _
"from Alert where Key = " + str$(lngKey))

With qdfDetail
Open Recordset from QueryDef.
Set rstTemp = qdfDetail.OpenRecordset(dbOpenSnapshot)

Missing parameter errors are nearly always due to misspelt field names. In
this case, I would be more worried about "CDate", "Type", and "When" which
are all reserved words in VBA, SQL or both. At least, I would enclose them
in [brackets], but better would be to rename them. Similar danger-words are
Text, Date, Count and so on.

HTH


Tim F

 

[Douglas J. Steele]

What data type is Key? If it's text, you need to enclose your value in
quotes:

Set qdfDetail = dbsAlert.CreateQueryDef("", "select
CDate,TDate,Type,When,Interval,Title from " _
& "Alert where Key = " & Chr$(34) & str$(lngKey) & Chr$(34))

(Chr$(34) = ")

And I have to agree with Tim: most of your field names should probably be
renamed.