requery after update

[Garret]

I was wondering if there was a way to resort by the way its already
sorted (using ascendingly by Primary Key field) after a record is
changed. For example:
Records:

Bob
John
Zack
....
User updates Bob to be "William", so it should now be:

John
William
Zack

but it keeps the updates where Bob was, at the same position.

 

[Dirk Goldgar]

I was wondering if there was a way to resort by the way its already
sorted (using ascendingly by Primary Key field) after a record is
changed. For example:
Records:

Bob
John
Zack
...
User updates Bob to be "William", so it should now be:

John
William
Zack

but it keeps the updates where Bob was, at the same position.

Sure, you can use the form's AfterUpdate event to requery the form, but
that will reposition the form to the first record. Assuming your form
has a primary key field, you can save that and use it to reposition the
form to the same record, or to the next record after that in the
requeried recordset. It might look like the following "air code":

'----- start of example code -----
Private Sub Form_AfterUpdate()

Dim varID As Variant

varID = Me!ID
Me.Requery
Me.Recordset.FindFirst "ID = " & varID

End Sub
'----- end of example code -----