Search code examples
ms-accessvbarecordset

Error on a recordset, but same SQL works elsewhere

Error: "Run-time error '3061' Too few parameters. Expected 2.

I wrote this simple function that returns the remaining percentage calculated for number of records changed. It is supposed to occur when the user updates the field called 'percentage' I am certain the code below should work, but obviously something is wrong. It occurs on the line:

Set rs = db.OpenRecordset("SELECT Tier1, [Percentage], Tier3 AS Battalion, Month " _
    & "FROM tbl_CustomPercent " _
    & "WHERE (((Tier1)=[Forms]![frmEntry]![cmbImport_T1]) AND ((Month)=[Forms]![frmEntry]![cmbMonth]));", dbOpenSnapshot)

I wonder how it could fail when the very same query is what populates the 'record source' for the form with the 'percentage' textbox.

Function RemainingPercentAvailable() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Tier1, [Percentage], Tier3 AS Battalion, Month " _
& "FROM tbl_CustomPercent " _
& "WHERE (((Tier1)=[Forms]![frmEntry]![cmbImport_T1]) AND ((Month)=[Forms]![frmEntry]![cmbMonth]));", dbOpenSnapshot)

Dim CurrentTotal As Single

CurrentTotal = 0

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst

    Do Until rs.EOF = True
        CurrentTotal = CurrentTotal + rs!Percentage
        rs.MoveNext
    Loop

End If


RemainingPercentAvailable = "Remaing available: " & Format(1 - CurrentTotal, "0.000%")

Set rs = Nothing
Set db = Nothing

End Function
Solution

  • Adapt your code to use the SELECT statement with a QueryDef, supply values for the parameters, and then open the recordset from the QueryDef.

    Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Tier1, [Percentage], Tier3 AS Battalion, [Month] " _
    & "FROM tbl_CustomPercent " _
    & "WHERE (((Tier1)=[Forms]![frmEntry]![cmbImport_T1]) AND (([Month])=[Forms]![frmEntry]![cmbMonth]));"

Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSQL )
' supply values for the 2 parameters ...
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
qdf.Parameters(1).Value = Eval(qdf.Parameters(1).Name)
Set rs = qdf.OpenRecordset

    Note: Month is a reserved word. Although that name apparently caused no problems before, I enclosed it in square brackets so the db engine can not confuse the field name with the Month function. It may be an unneeded precaution here, but it's difficult to predict exactly when reserved words will create problems. Actually, it's better to avoid them entirely if possible.