How do you set a recordset's bookmark?

[Webtechie]

Hello,

I have a userform in Excel. I am creating a recordset to an Access database.

I've created the recordset and can now populate my fields. However, I need
the user to be able to go back word and forward.

I am using a spin control on a userform to naviate. When the user clicks
up, I need to get the previous record.

I can't figure out how, using an ADO recordset on the , to get the previous
record.

dim iCurrentRecord as integer

'Create the recordset
'********************
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 = " & thisDept

Set rsGuestData.ActiveConnection = myConnection

iCurrentRecord = frmKeyLeads.spincontrol.value

With rsGuestData
.Source = mySQL
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Options:=adCmdText
.bookmark = iCurrentRecord - errors
..moveprevious
End With

 

[Ken Snell MVP]

Is iCurrentRecord the value of the primary key in the recordset? If yes, you
don't use the Bookmark, instead you "move" the recordset to that record via
the Find method.

However, it appears that you want to move to the record previous to the
current record. There is no "order" in a recordset or table unless you
provide it via an Order By clause in the query used to create the recordset.
Your MySQL string does not include an Order By clause, so you have no
assurance of what is the order of data.

So,
(1) Add an ORDER BY clause to your MySQL string.
(2) Use the .Find method of the recordset to move the recordset to the
current record, using the value in iCurrentRecord variable if it's the
primary key.
(3) Use the .MovePrevious method to move to the record just before the
current record.

Or,
(1) Store the value of the previous record's primary key into a
variable.
(2) Add your new record.
(3) Use the .Find method of the recordset to move the recordset to the
previous record, using the value in this previous record variable.

 

[Dirk Goldgar]

Hello,

I have a userform in Excel. I am creating a recordset to an Access
database.

I've created the recordset and can now populate my fields. However, I
need
the user to be able to go back word and forward.

I am using a spin control on a userform to naviate. When the user clicks
up, I need to get the previous record.

I can't figure out how, using an ADO recordset on the , to get the
previous
record.

dim iCurrentRecord as integer

'Create the recordset
'********************
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 = " & thisDept

Set rsGuestData.ActiveConnection = myConnection

iCurrentRecord = frmKeyLeads.spincontrol.value

With rsGuestData
.Source = mySQL
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Options:=adCmdText
.bookmark = iCurrentRecord - errors
.moveprevious
End With

Am I right in thinking that iCurrentRecord is basically a record number --
not a key value, but an ordinal position in the recordset? Depending on the
CursorType and provider, you may well be able to assign it directly to the
recordset's AbsolutePosition property, and therby position the recordset to
that record:

With rsGuestData
.Source = mySQL
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Options:=adCmdText

' ...

.AbsolutePosition = iCurrentRecord

End With

 

[Webtechie]

Ken,

I went to the microsoft site to study up on recordsets. It said the .find
could not be used with a variable.

I could store the primary key (current record), find it in the recordset and
then moveprevious. But how would I do that if I can't use variables?

Tony

 

[Webtechie]

Dirk,

Thank you, thank you and thank you!

That worked.

 

[Ken Snell MVP]

Yes, you can use a variable in a Find method, for example:

..Find "PrimaryKeyFieldName = " & NameOfNumericVariable