Navigation

[gibbo1715]

All

Im looking for the best approach to navigating through a recordset using a userform form I have created in Excel

I ve been using the code below which seems to work fine but im not closing my recordset (How will this effect other users also accessing the recordset at the same time?)

If I close my connection after each move (Can i even do this) how can I locate the current record to continue my navigation?

Can someone talk me through the best approach please or point me in the direction of where to look

(ADO only please)

cheers

gibbo

Private Sub CommandButton2_Click()

'previous
rs.MovePrevious
intRecNumber = intRecNumber - 1

If Not rs.BOF Then
Call Navigation
Me.Label1.Caption = CStr(intRecNumber) & " of " & rs.RecordCount
Else
CommandButton5_Click
End If
End Sub
Private Sub CommandButton3_Click()
'next
rs.MoveNext
intRecNumber = intRecNumber + 1
If Not rs.EOF Then
Call Navigation
Me.Label1.Caption = CStr(intRecNumber) & " of " & rs.RecordCount
Else
CommandButton6_Click
End If
End Sub

Private Sub CommandButton5_Click()
'first
rs.MoveFirst
intRecNumber = 1

Call Navigation

Me.Label1.Caption = "1 of " & rs.RecordCount
End Sub
Private Sub CommandButton6_Click()
'Last
rs.MoveLast
intRecNumber = rs.RecordCount
Call Navigation
Me.Label1.Caption = CStr(intRecNumber) & " of " & rs.RecordCount
End Sub

Private Sub Navigation()
'populate textboxes on userform
Me.TextBox1.Value = rs.Fields("FieldName1").Value
Me.TextBox2.Value = rs.Fields("FieldName2").Value
Me.TextBox3.Value = rs.Fields("FieldName3").Value
Me.TextBox4.Value = rs.Fields("FieldName4").Value
Me.TextBox5.Value = rs.Fields("FieldName5").Value
End Sub