Recordset and moving to record problems

[bdsii]

Hello all....I am using Access 2007 and trying to teach myself about creating recordsets and then using them to navigate to the previous or next records in a filtered form. I know something similar can be accomplished with a command button but I want to learn how to code this correctly. I have researched recordsets and move functions from the web and am getting an error. I am not sure if it is because of the references that are selected in the object library or if I just missed something.

I am hoping somoene here can spot the error I have made and point it out to me. I may be placing the code in the wrong event for the form.

I placed the Dim Set statements in the Form_Load events

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblTEMPEntryReview", dbOpenDynaset)
End Sub


When the "Previous" button is clicked I am testing to see if the current record is already BOF and if not, it moves to the previous record

Private Sub btnPrevRecord_Click()
If rs.BOF = False Then
rs.MovePrevious
Else
MsgBox "You are at the first record"
End If
End Sub




I closed the recordset when the form is closed. Do I need to close anything else ?

Private Sub Form_Close()
' closes recordset
rs.Close
End Sub



Any ideas ?

thanks !

[snorkyller]

Your 2 variables, db and rs, are only available in Form_Load. To be available in all subs, you have to declare them as public variables at the top of the page, like this:
Public db As DAO.Database
Public rs As DAO.Recordset

Also, I think you should set these variables to nothing when you close the window:
Set db = Nothing
Set rs = Nothing

[bdsii]

hhhhmmmmm......good catch! Will give that a shot. Thanks snorkyller
:-)

[snorkyller]

Correction: You don't have to declare them Public. If you use rd and db only inside that form, use Dim, but as I said, declare them at the top of the page (below "Option Compare Database"), and not inside a sub, and they will be available at any time inside this form.