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

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.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"Webtechie" <(E-Mail Removed)> wrote in message
news:16976FE5-F730-400F-934C-(E-Mail Removed)...
> 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
>
>

"Webtechie" <(E-Mail Removed)> wrote in message
news:16976FE5-F730-400F-934C-(E-Mail Removed)...
> 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


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

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

"Ken Snell MVP" wrote:

> 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.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
>
> "Webtechie" <(E-Mail Removed)> wrote in message
> news:16976FE5-F730-400F-934C-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

Dirk,

Thank you, thank you and thank you!

That worked.



"Dirk Goldgar" wrote:

> "Webtechie" <(E-Mail Removed)> wrote in message
> news:16976FE5-F730-400F-934C-(E-Mail Removed)...
> > 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
>
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>

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

..Find "PrimaryKeyFieldName = " & NameOfNumericVariable
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Webtechie" <(E-Mail Removed)> wrote in message
news:71A3FF9D-D564-4224-833A-(E-Mail Removed)...
> 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
>
> "Ken Snell MVP" wrote:
>
>> 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.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>>
>> "Webtechie" <(E-Mail Removed)> wrote in message
>> news:16976FE5-F730-400F-934C-(E-Mail Removed)...
>> > 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
>> >
>> >
>>
>>
>>