Navigating a recordset 1

[MikeGeitner]

I'm trying to learn how to use a recordset on an unbound form. Here's what I have and the the first record shows up in the two text boxes. Now all I want to do is to add command buttons to navigate through the records as if this were a bound form. I've added one button with "rs.movenext" in the onclick event, which gives me an error "Compile Error: Variable not defined".

What am I missing? Something simple, I presume?

Public Sub Form_Load()

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set ws = DBEngine(0)
Set db = ws.OpenDatabase("C:\Documents and Settings\Mike\My Documents\Database\Northwind.mdb")
Set rs = db.OpenRecordset("Customers", dbOpenDynaset)


rs.MoveFirst

        Me.txtCity = rs.Fields("City")
        Me.txtCust = rs.Fields("CompanyName")


ws.Close
Set ws = Nothing
Set db = Nothing

End Sub

 

[PHV]

A starting point:

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset

Private Sub Form_Load()
  Set ws = DBEngine(0)
  Set db = ws.OpenDatabase("C:\Documents and Settings\Mike\My Documents\Database\Northwind.mdb")
  Set rs = db.OpenRecordset("Customers", dbOpenDynaset)
  rs.MoveFirst
  Populate_controls
End Sub

Private Sub Populate_controls()
  Me!txtCity = rs.Fields("City")
  Me!txtCust = rs.Fields("CompanyName")
End Sub

Private Sub Next_Click()
  If rs.EOF Then
    rs.MoveFirst
  Else
    rs.MoveNext
  End If
  Populate_controls
End Sub

Private Sub Prev_Click()
  If rs.BOF Then
    rs.MoveLast
  Else
    rs.MovePrevious
  End If
  Populate_controls
End Sub

Private Sub Form_Close()
  rs.Close
  Set rs = Nothing
  Set db = Nothing
  Set ws = Nothing
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeGeitner]

Ahhhhh, declare the objects first, outside of any sub, then use them in subs. Keep them around until you close the form. Am I getting this right?

Mike

 

[PHV]

Am I getting this right?
Seems you've got the idea.

 

[MikeGeitner]

Thanks PHV! A star for you.


Mike