update record# value in the navigation control?

[John Q. Murray]

Hi
I would like to keep track of a record number from the navigation controls
at the bottom of the form and be able to programmatically return to that
record later
Is there a way to read and set the value in the navigation buttons within an
Access 2003 form
(So that after setting the value is that the form will move to and display
data from that record, just as if the user clicked to it or entered it by
hand)
Sample code greatly appreciated

Thanks
JQ

 

[Klatuu]

There is no direct way to do that. The number you see there is the
AbsolutePosition property of the form's recordset. But, in Access, there are
no fixed record numbers. It only shows the relative position of the record
in the recordset. Also, depending on whether your recordset is DAO or ADO,
the numbers will be different. DAO starts with 0 and ends with the number of
records -1. ADO starts with one.

So, you could capture the record's absolute position and use the GotoRecord
method, but it is not reliable. The following can cause the record's number
to change:
1. Add a record
2. Delete a record
3. Set or remove a filter
4. change the Orderby property of the form.

The better way is to capture the record's primary key and store it in a
module level variable that you can then use the FindFirst method to move back
to that record. Assume you have a Long Integer primary key field named
[ClientID] and you have a module level variable named lngSavedClient, you
would do this:

With Me.RecordsetClone
.FindFirst "[ClientID] = " & lngSavedClient
If .NoMatch Then
Msgbox "Record Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

 

[John Q. Murray]

Thank you very much -
Klaatu barada nikto!

--
John Q. Murray
Executive Director
Corporation for Public Community Newspapers

There is no direct way to do that. The number you see there is the
AbsolutePosition property of the form's recordset. But, in Access, there are
no fixed record numbers. It only shows the relative position of the record
in the recordset. Also, depending on whether your recordset is DAO or ADO,
the numbers will be different. DAO starts with 0 and ends with the number of
records -1. ADO starts with one.

So, you could capture the record's absolute position and use the GotoRecord
method, but it is not reliable. The following can cause the record's number
to change:
1. Add a record
2. Delete a record
3. Set or remove a filter
4. change the Orderby property of the form.

The better way is to capture the record's primary key and store it in a
module level variable that you can then use the FindFirst method to move back
to that record. Assume you have a Long Integer primary key field named
[ClientID] and you have a module level variable named lngSavedClient, you
would do this:

With Me.RecordsetClone
.FindFirst "[ClientID] = " & lngSavedClient
If .NoMatch Then
Msgbox "Record Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

Hi
I would like to keep track of a record number from the navigation controls
at the bottom of the form and be able to programmatically return to that
record later
Is there a way to read and set the value in the navigation buttons within an
Access 2003 form
(So that after setting the value is that the form will move to and display
data from that record, just as if the user clicked to it or entered it by
hand)
Sample code greatly appreciated

Thanks
JQ