Moving up and down in a recordset to set userform values?

[Webtechie]

Hello,

I am trying to populate a user form.

I've got to where I can populate the userform fields using a recordset. Now
I need the user to be able to get to the next record.

I have a spin control on the form. When they press up or down, I need the
recordset to advance and the text fields to show the next record.

I tried to save the bookmark of the recordset to a hidden field.

But when I try to set the bookmark, I'm getting an error message. I'm
getting a "False" when I try to set the bookmark.

Anyone have an idea of why I am getting an error message?


Set rsGuestData = New ADODB.Recordset

'Is connection open?
'*******************
If myConnection Is Nothing Then
ConnectToDatabase
End If

'Get connection from the pool
'****************************
myConnection.Open

'Create SQL Statement
'********************
mySQL = "select * from tblGuest " _
& " where employeeid = " & thisEmployeeID _
& " and deptID = " & thisDeptID

With rsGuestData
.CursorLocation = adUseClient
.Open mySQL, myConnection, adOpenDynamic, adLockOptimistic
.Bookmark = frmMyUserForm.spnDataEntry.Value
End If
End With

Thanks

Tony

 

[Patrick Molloy]

I have four buttons....move first, move previous, next and last, here's the
code, and rst is the ADODB recordset object. see if this helps ...


Private Sub cmd_MoveFirst_Click()
rst.MoveFirst
ShowData
End Sub

Private Sub cmd_MoveNext_Click()
rst.MoveNext
If rst.EOF Then
rst.MoveLast
End If
ShowData
End Sub

Private Sub cmd_MovePrevious_Click()
rst.MovePrevious
If rst.BOF Then
rst.MoveFirst
End If
ShowData
End Sub

Private Sub cmdMoveLast_Click()
rst.MoveLast
ShowData
End Sub

 

[Patrick Molloy]

oh

ShowData

this is a private sub on the form that loads recordset data into text boxes
etc on the form

SUB ShowData()
' example
Dim fls As Long
For fls = 1 To 4
Controls("txtData" & fls).Text = rst.Fields(fls).Value
Next
lblIndex = rst.AbsolutePosition


END SUB