Too few parameters 1

[bulldawg15]

I have a module that I use for running a report and changed the WHERE clause in the SQL in the module and I'm now getting a 3061 Too Few Parameters error. I'm sure I'm just referencing incorrectly in my WHERE clause. I have posted below the code for the module. Sorry if it's a little lenghty. Thanks in advance.

Function BlendTheData() As String
On Error GoTo Err1
Dim ComboSQL As String, Combo As DAO.Recordset, Db1 As DAO.Database
Set Db1 = CurrentDb()
ComboSQL = "SELECT Rank.Class_ID, Rank.Heat FROM Rank"
Set Combo = Db1.OpenRecordset(ComboSQL, dbOpenSnapshot)
Do Until Combo.EOF

Dim SQL As String, Rs As DAO.Recordset, Db As DAO.Database
Set Db = CurrentDb()
SQL = "SELECT Rank.Rank, Driver.Number, Driver.Name, Home.Address " & _
"FROM (Home INNER JOIN (Class INNER JOIN Driver ON Class.Class_ID = Driver.Class_ID) ON Home.Home_ID = Driver.Home_ID) INNER JOIN Rank ON (Driver.Driver_ID = Rank.Driver_ID) AND (Class.Class_ID = Rank.Class_ID) " & _
"WHERE (((Rank.Class_ID)= " & (Combo!Class_ID) & ") AND ((Rank.Heat)= " & (Combo!Heat) & "));"
Set Rs = Db.OpenRecordset(SQL, dbOpenSnapshot)
If Rs!Address = "x" Then
BlendTheData = BlendTheData & "; " & Rs!Rank & ". " & Rs!Number & " " & Rs!Name
Else
BlendTheData = BlendTheData & "; " & Rs!Rank & ". " & Rs!Number & " " & Rs!Name & ", " & Rs!Address
End If
Rs.MoveNext
Do Until Rs.EOF
If Rs!Address = "x" Then
BlendTheData = BlendTheData & "; " & Rs!Rank & ". " & Rs!Number & " " & Rs!Name
Else
BlendTheData = BlendTheData & "; " & Rs!Rank & ". " & Rs!Number & " " & Rs!Name & ", " & Rs!Address
End If
Rs.MoveNext
Loop
Combo.MoveNext
Loop

Exit1:
Exit Function

Err1:
MsgBox Err.Number & " " & Err.Description, vbInformation, "An error has occured..."
Resume Exit1
End Function

 

[PHV]

If either Class_ID or Heat isn't defined as numeric in Rank then you have to surround the corresponding value with single quotes, e.g.:
& ") AND ((Rank.Heat)=[highlight]'[/highlight]" & (Combo!Heat) & "[highlight]'[/highlight]));"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bulldawg15]

Thanks PHV! That was the issue. However, I'm not out of the woods yet. Now I'm receiving Error 3021 No current record. Any ideas?

 

[PHV]

First, why having 2 recordset ?
Second, in your inner loop you test Rs.EOF too late.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bulldawg15]

Thanks for the help PHV. Fixing the testing of Rs.EOF cleaned up my problems greatly. I now have everything running smoothly. Thanks again.