Object variable or With block variable not set error

[dBNovice]

The following code results in the error above:
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.RecordCount = 0 Then
If Not (rs!ACCEPTEDASPROPOSED = True Or rs!MODIFIED = True Or
rs!POSTER = True Or rs!WITHDRAWN = True Or rs!REJECTANDREUSE = True)
Then
DoCmd.OpenForm "frmModifySession"
ElseIf rs!ACCEPTEDASPROPOSED = True Or rs!MODIFIED = True Then
Response = MsgBox("Action has been taken on this
proposal. Do you still want to continue?", vbYesNo)
If Response = vbYes Then
DoCmd.OpenForm "frmModifySession"
End If

Can anyone tell me what I am doing wrong. I think there is a problem
when I try to set rs to open based on the SQL string. Some who tried
to help said I need a line of code like: Set db = CurrentDb (Set rs =
?????). I am in dire need. Please!!!!!!!!!!

 

[RobFMS]

Here is a simple template you can follow on how to use the Database and
Recordset object

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDB
Set rs = DB.OpenRecordset( ~~~~ )

With rs

Do While not (.BOF and .EOF)

{ insert code here to process in loop }

.MoveNext
Loop

End With

Set db = Nothing
Set rs = Nothing





--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Danny J. Lesandrini]

Yes you need it to look more like this ...

Dim db As DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String

strSQL = "Select Name, Type FROM MsysObjects"
Set db = CurrentDB()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF

rs.MoveNext
Loop

Set rs = nothing
Set db = nothing


This error would also show if the Set rs method failed. What does
your strSQL look like? If you cut and pasted it into a query, would
it run?

 

[Dirk Goldgar]

Here is a simple template you can follow on how to use the Database
and Recordset object

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDB
Set rs = DB.OpenRecordset( ~~~~ )

With rs

Do While not (.BOF and .EOF)

{ insert code here to process in loop }

.MoveNext
Loop

End With

Set db = Nothing
Set rs = Nothing

That's not quite right, Rob; I think you made an error in your loop
condition. Instead of this:

Do While not (.BOF and .EOF)

I suggest this:

Do While not (.BOF Or .EOF)

Also, it's a good policy to explicitly close the recordset, though it
may not be strictly necessary:

 

[RobFMS]

Dirk

What's an "AND" here or an "OR" there ...... (laugh)

Thanks for the clarification Dirk.

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com