Too few parameters (Error 3061) problems

[ras81]

hello,
can any1 help me with this problem i don't know what to do
the error message won't stop....
here is my code:

Private Sub _AfterUpdate()
Dim dbsProject As Database
Dim q As QueryDef
Dim res As Recordset
Dim strSQL As String
Set dbsProject = CurrentDb
strSQL = "SELECT Item.ItemPrice FROM Item WHERE ((Item.ItemID)=[Forms]![InsertTreatment]![ItemNum]);"
Set res = dbsProject.OpenRecordset(strSQL, dbOpenSnapshot)
Set MoneyValue.Value = res
 
End Sub

thank u in advance

 

[KenHigg]

If the field is a numeric data type:

"SELECT Item.ItemPrice FROM Item WHERE ((Item.ItemID)=" & [Forms]![InsertTreatment]![ItemNum] & ");"

If the field is a text data type:

"SELECT Item.ItemPrice FROM Item WHERE ((Item.ItemID)='" & [Forms]![InsertTreatment]![ItemNum] & "');"

???

 

[LPurvis]

It is, of course, equally possible to allow form references to remain within the SQL statements (which is very often the case in a saved query definition for example - which you might use for UI elements such as list controls sources of forms themselves as well as in code recordsets).

Rather than use the raw OpenRecordset method you can call a user defined function to open the recordset

e.g.
Set rst = fDAOGenericRst("qryName")
or in your example
Set rst = fDAOGenericRst(strSQL)


It's defined below. The core is just the old enumerating the parameters and forcing an Eval on any named params found. (i.e. it's expecting to find Form control expressions as you have when there's such an issue

Function fDAOGenericRst(strSQL As String, Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                                          Optional intOptions As DAO.RecordsetOptionEnum, _
                                          Optional intLock As DAO.LockTypeEnum, _
                                          Optional pdb As DAO.Database) As DAO.Recordset
    Dim db As Database
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
    
    If Not pdb Is Nothing Then
        Set db = pdb
    Else
        Set db = CurrentDb
    End If
    
    On Error Resume Next
    Set qdf = db.QueryDefs(strSQL)
    If Err = 3265 Then
        Set qdf = db.CreateQueryDef("", strSQL)
    End If
    On Error GoTo 0
    
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
    
    If intOptions = 0 And intLock = 0 Then
        Set rst = qdf.OpenRecordset(intType)
    ElseIf intOptions > 0 And intLock = 0 Then
        Set rst = qdf.OpenRecordset(intType, intOptions)
    ElseIf intOptions = 0 And intLock > 0 Then
        Set rst = qdf.OpenRecordset(intType, intLock)
    ElseIf intOptions > 0 And intLock > 0 Then
        Set rst = qdf.OpenRecordset(intType, intOptions, intLock)
    End If
    Set fDAOGenericRst = rst
    
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
End Function