Too few parameters on OpenRecordset 1

[wx5chr]

Larry DeLarvelle is looking at this with me, but I wanted to see of anyone else had any ideas. Can someone tell me why I get an error "too few parameters. Expected 2." at the Set rsTemp = dbTemp.OpenRecordset(strSQL) line in the following code? My field types are as follows:

Rev_Pub_Date is a date field
Proc_ID is numerical
CurrentCk is a checkbox (Yes/No)

Here's the code:

Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String

Set dbTemp = CurrentDb

strSQL = "SELECT Rev_Pub_Date, CurrentCk FROM T_Dates " & _
"WHERE (((T_Dates.Proc_ID)=" & Me.Proc_ID & &quot " & _
"AND ((T_Dates.CurrentCk)=-1)) ORDER BY Rev_Pub_Date"

Set rsTemp = dbTemp.OpenRecordset(strSQL)

If rsTemp.RecordCount > 0 Then
rsTemp.MoveFirst
rsTemp.MoveNext
Do Until rs.Temp.EOF
With rsTemp
.Edit
!CurrentCk = 0
.Update
End With
rsTemp.MoveNext
Loop
End If
rsTemp.Close

 

[RoyVidar]

Hi!

Put in debug.print strSQL before you run the openrecordset.

CTRL+G takes you to the immidiate pane where you can copy the sql statement. Paste it directly into the SQL view of the QBE (or here) and check if it is a valid SQL statement (you'll probably see it too). I'm guessing the form controls does not have values.

Roy-Vidar

 

[wx5chr]

I don't fully understand where you mean when you say, "Paste it directly into the SQL view of the QBE (or here) and check if it is a valid SQL..."

 

[RoyVidar]

If you put in the line I said after the assigning of the strSQL, but before you run the openrecordset, doing CTRL+G should give you the immidiate pane where the sql statment should now be written, with/without values or perhaps wrong values.

Copy the sql statement from the immidiate pane and paste it into the SQL view of a query (New query, do not select any tables, then find the view button and select SQL), paste the sql statement and see if it runs. Or paste it here.

Roy-Vidar

 

[wx5chr]

Here's the SQL statement:

SELECT Rev_Pub_Date, CurrentCk FROM T_Dates WHERE (((T_Dates.Proc_ID)=45) AND ((T_Dates.CurrentCk)=-1)) ORDER BY Rev_Pub_Date;

When I pasted it, I selected to view the data and two parameter boxes came up. One for CurrentCk and the other for T_Dates.CurrentCk.

What I'm trying to do is pull a list of Rev_Pub_Dates for matching Proc_IDs where CurrentCk=-1. Then I want to uncheck all but the most recent date.

 

[RoyVidar]

Usually, when the parameter comes up, the field names contains typos. Are you sure this is the correct field name, and that CurrentCK is a field in the T_Dates table?

Roy-Vidar

 

[wx5chr]

I Feel so dumb. I didn't bother to check the field name. It turns out that for some reason, I used CurrentCk as the caption, but the actual field is Current. It's working now. Thanks.

 

[wx5chr]

New problem. Now I'm getting an "Object Required" error on the line marked below:

Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim strSQL As String

Set dbTemp = CurrentDb

strSQL = "SELECT T_Dates.Rev_Pub_Date, T_Dates.Current FROM T_Dates " & _
"WHERE (((T_Dates.Proc_ID)= " & Me.Proc_ID & &quot " & _
"AND ((T_Dates.Current) = -1)) ORDER BY T_Dates.Rev_Pub_Date DESC;"

Debug.Print strSQL

Set rsTemp = dbTemp.OpenRecordset(strSQL)

If rsTemp.RecordCount > 1 Then
rsTemp.MoveFirst
rsTemp.MoveNext
Do Until rs.Temp.EOF <----This line shows as error
With rsTemp
.Edit
!CurrentCk = 0
.Update
End With
rsTemp.MoveNext
Loop
End If
rsTemp.Close

 

[RoyVidar]

Hi

[tt] rsTemp.EOF [/tt]

- Only one dot, and now, remove the debug.print line.

Roy-Vidar

 

[wx5chr]

Embarassed again. I can't even proof my own code. Thanks for the catch.