record positioning after requery / continuous forms

[Guest]

I have a continuous form with 2 fields, ID and sortOrder. The
Form_AfterUpdate code does a requery to re-sort the fields (the underlying
recordset is sorted by sortOrder). After the requery I want to position to
the record that was modified. Here's the AfterUpdate code:

Me.Requery
Me.RecordsetClone.FindFirst "ID = " & Me.ID
Me.Bookmark = Me.RecordsetClone.Bookmark

The value for ID in the event handler is correct (it's for the changed
record) but setting Me.Bookmark does not cause positiioning to that record;
it remains in the original spot.

For example: let's say there are 3 records in the form (col1=ID,
col2=sortOrder):
A 1
B 2
C 3

If I change the sortOrder for B from 2 to 4 this is what I see after the
requery:

A 1
C 3
B 4

This is correct, but the focus stays on the 2nd row (C 3) and I want it to
be on the 3rd row (B 4), the record that was changed.

Thanks - Dana

 

[Van T. Dinh]

There are 2 possible problems here:

1. The code may execute asynchronously (?), especially that the Requery may
take some time since this has to access the data in the Table, i.e. hard
disk. I'm not sure whether you will pick up the old RecordsetClone or the
new RecordsetClone in the next statement.

2. After the Form's requery, Access usually make the first Record of the
newly refreshed Recordset as the CurrentRecord and the Form_Current Event
fires at which time Me.ID changes and you may pick up the wrong ID value.

I would code it something like:

****Untested****
Dim rsClone As DAO.Recordset
Dim strHoldForLaterUse as String

strHoldForLaterUse = Me.ID
Me.Requery
DoEvents
Set rsClone = Me.RecordsetClone
rsClone.FindFirst "[ID] = """ & strHoldForLaterUse & """"
If rsClone.NoMatch = False Then
Me.Bookmark = rsClone.Bookmark
End If
****End****

I assume [ID] is of Text type as per your example. You need to modify the
code if [ID]
is numeric.

HTH
Van T. Dinh
MVP (Access)

 

[Guest]

I tried your suggestion (saving the value and adding DoEvents) but it behaved
the same; HOWEVER, more information. The detail section of the continuous
form has record selectors they are a major factor in the problem:

1) If you change sortOrder (col 2) and exit the record by hitting the tab
key, which takes you to the ID of the next record (col1), then the current
record / focus moves to the proper position (i.e., it stays with the record
after the record changes order due to the requery).

2) If you change sortOrder and exit the record by clicking the record
selector (which changes it from a pencil to right arrow), the current record
/ focus stays at that physical row on the form, even after the Requery and
changing the bookmark.

It appears that clicking the record selector overrides the requery / set
bookmark that occurs in the AfterUpdate...?

There are 2 possible problems here:

1. The code may execute asynchronously (?), especially that the Requery may
take some time since this has to access the data in the Table, i.e. hard
disk. I'm not sure whether you will pick up the old RecordsetClone or the
new RecordsetClone in the next statement.

2. After the Form's requery, Access usually make the first Record of the
newly refreshed Recordset as the CurrentRecord and the Form_Current Event
fires at which time Me.ID changes and you may pick up the wrong ID value.

I would code it something like:

****Untested****
Dim rsClone As DAO.Recordset
Dim strHoldForLaterUse as String

strHoldForLaterUse = Me.ID
Me.Requery
DoEvents
Set rsClone = Me.RecordsetClone
rsClone.FindFirst "[ID] = """ & strHoldForLaterUse & """"
If rsClone.NoMatch = False Then
Me.Bookmark = rsClone.Bookmark
End If
****End****

I assume [ID] is of Text type as per your example. You need to modify the
code if [ID]
is numeric.

HTH
Van T. Dinh
MVP (Access)

I have a continuous form with 2 fields, ID and sortOrder. The
Form_AfterUpdate code does a requery to re-sort the fields (the underlying
recordset is sorted by sortOrder). After the requery I want to position to
the record that was modified. Here's the AfterUpdate code:

Me.Requery
Me.RecordsetClone.FindFirst "ID = " & Me.ID
Me.Bookmark = Me.RecordsetClone.Bookmark

The value for ID in the event handler is correct (it's for the changed
record) but setting Me.Bookmark does not cause positiioning to that record;
it remains in the original spot.

For example: let's say there are 3 records in the form (col1=ID,
col2=sortOrder):
A 1
B 2
C 3

If I change the sortOrder for B from 2 to 4 this is what I see after the
requery:

A 1
C 3
B 4

This is correct, but the focus stays on the 2nd row (C 3) and I want it to
be on the 3rd row (B 4), the record that was changed.

Thanks - Dana

 

[Van T. Dinh]

Which Event do you use to run the code?

Van T. Dinh
MVP (Access)

 

[Guest]

Got even more info now. I added a Form_Current event handler and put a
breakpoint there. Here's what happens:

1) Edit sortOrder, click record selector to exit the record / commit the
change.
2) After_Update fires. It executes up to and including the Requery.
3) Before finishing the After_Update, Form_Current fires. The selected
record is the first one.
4) Form_Current fires. The selected record is the edited one in its new
sorted position.
5) Execution resumes in After_Update after the Requery.
6) Form_Current fires. The selected record is now the one corresponding
positionally to the record selector that was clicked, but is not correct
because of the reordering done by the Requery.

Whew. I guess this sorta makes twisted ense, if you consider that you edit
a field, then click the record selector. Seems like Access is considering
this 2 different events, (1) closing the record and (2) selecting a record.
The problem seems to be that the event that fires after closing the record
(After_Update) is not the final event, because Access generates a
Form_Current from the record selector click.

There's got to be some rational way to handle this...?

There are 2 possible problems here:

1. The code may execute asynchronously (?), especially that the Requery may
take some time since this has to access the data in the Table, i.e. hard
disk. I'm not sure whether you will pick up the old RecordsetClone or the
new RecordsetClone in the next statement.

2. After the Form's requery, Access usually make the first Record of the
newly refreshed Recordset as the CurrentRecord and the Form_Current Event
fires at which time Me.ID changes and you may pick up the wrong ID value.

I would code it something like:

****Untested****
Dim rsClone As DAO.Recordset
Dim strHoldForLaterUse as String

strHoldForLaterUse = Me.ID
Me.Requery
DoEvents
Set rsClone = Me.RecordsetClone
rsClone.FindFirst "[ID] = """ & strHoldForLaterUse & """"
If rsClone.NoMatch = False Then
Me.Bookmark = rsClone.Bookmark
End If
****End****

I assume [ID] is of Text type as per your example. You need to modify the
code if [ID]
is numeric.

HTH
Van T. Dinh
MVP (Access)

I have a continuous form with 2 fields, ID and sortOrder. The
Form_AfterUpdate code does a requery to re-sort the fields (the underlying
recordset is sorted by sortOrder). After the requery I want to position to
the record that was modified. Here's the AfterUpdate code:

Me.Requery
Me.RecordsetClone.FindFirst "ID = " & Me.ID
Me.Bookmark = Me.RecordsetClone.Bookmark

The value for ID in the event handler is correct (it's for the changed
record) but setting Me.Bookmark does not cause positiioning to that record;
it remains in the original spot.

For example: let's say there are 3 records in the form (col1=ID,
col2=sortOrder):
A 1
B 2
C 3

If I change the sortOrder for B from 2 to 4 this is what I see after the
requery:

A 1
C 3
B 4

This is correct, but the focus stays on the 2nd row (C 3) and I want it to
be on the 3rd row (B 4), the record that was changed.

Thanks - Dana

 

[Van T. Dinh]

1. In point 1, I assume you clicked the Record Selector of another Record,
not the edited Record?

2. Remember the Requery will make the 1st Record of the newly refreshed
Recordset the CurrentRecord (I mentioned this earlier). Hence, there is a
Form_Current Event fires straight away after the Requery.

3. When the code Me.Bookmark = ... is executed, you get another Form_Current
Event.

4. The fact that you click the RecordSelector creates aonther Form_Current
also, I *think*.

The fact that the user click the RecordSelector means that a Record (by
position on the Form) has been nominated to be the next CurrentRecord. The
problem is the Requery in code actually moves the Records. The CurrentRecord
(nominated by position on the Form) overides the Bookmarking since this is
the last action in the sequence.

I rarely use or show the RecordSelector to the users and almost all of my
Forms in CtsFormView are for viewing only so I am not entirely sure about
the behaviour of the Requery / RecordSelector in CtsFormView. However, I am
sure the 3 Form_Current Events are fired due to (in chronological order):

1. Requery action in code
2. Bookmarking action in code
3. User action in the GUI which starts the whole process but the user's
selection (by position) is the last action to be processed.

 

[Guest]

Van - I've come to the same conclusion as you:

The fact that the user click the RecordSelector means that a Record (by
position on the Form) has been nominated to be the next CurrentRecord.

Clicking the RecordSelector is causing 2 actions: (1) exiting the current
record and causing AfterUpdate to fire (which is where the Requery etc. is
located), and (2) changing the current record, which fires FormCurrent an
extra time (in addition to the Requery and setting the Bookmark). The
current record is apparently identified positionally, so after Requery
executes, it goes to the same position in the recordset, which is now a
different record after the reordering from the Requery.

Any ideas on a better way to do this? I like RecordSelectors because they
indicate that an edit is in progress (pencil image) but am open to any better
ways to do it.

 

[Van T. Dinh]

The RecordSelector is not the problem. The use can simply click anywhere
(any Control) on a different "row" to trigger the same sequence of actions.

The user's action will override any actions performed by code since it is
the last action to be done.

Perhaps, you can create a "Save & Requery" CommandButton in the Form Header.
When the user click this button, he/she *does not* nominate a new
CurrentRecord and then you AfterUpdate Event code should work.