Parameter Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The second line of this produces an error - Too few parameters (Expected 2) - this has run many times in the past
The set command only prompts for the OpenRecordset method. What's happening

Set dbb04 = CurrentD
Set rstb04 = dbb04.OpenRecordset("QRY-B04"

Thanks for helping out.
 
Your basing your recordset on a parameter query, so you
need to base it on a QueryDef object, not a Database
object. Here's an example...

Sub TestIt(strQryName As String, _
strParam1 As String, strValue1 As String, _
strParam2 As String, strValue2 As String)
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strQryName Then
Exit For
End If
Next qdf
qdf.Parameters(strParam1) = strValue1
qdf.Parameters(strParam2) = strValue2
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
'do something
rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

-----Original Message-----
The second line of this produces an error - Too few
Click to expand...
parameters (Expected 2) - this has run many times in the
past.
 
vtj said:
The second line of this produces an error - Too few parameters (Expected 2) - this has run many times in the past.
The set command only prompts for the OpenRecordset method. What's happening?

Set dbb04 = CurrentDb
Set rstb04 = dbb04.OpenRecordset("QRY-B04")
Click to expand...


The VBA environment does not know how to reslove query
paramters like the Access environment does. You have to
resolve the parameters in the procedure. How you'll do that
depends on what type of parameters you're using. For
example, if you're using a prompt type parameter, then you
could use the InputBox to prompt the user for the parameter
value.

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("QRY-B04")
Set qdf.Parameters(0).Value = InputBox("Start Date")
Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .
rs.Close: Set rs = Nothing
Set db = Nothing
 
The second line of this produces an error - Too few parameters (Expected 2) - this has run many times in the past.
The set command only prompts for the OpenRecordset method. What's happening?

Set dbb04 = CurrentDb
Set rstb04 = dbb04.OpenRecordset("QRY-B04")

Thanks for helping out.
Click to expand...

What's the SQL of query QRY-B04? That would appear to be where the
problem lies.
 
Back
Top