Cursor position in a Continuous form after a Requery

[Guest]

greetings all
I have a continuous form with many records. The form's AfterUpdate event
has Me.Requery, causing the records to be sorted alphabetically after every
change. My problem is that the Requery always resets the cursor to the 1st
record. Since I am usually editing several records in the same 'vicinity', I
have to keep refinding the area where I was working. I'd like the records
requeried after an edit, but I'd like the cursor to stay at the edited
record. Also, if a new record is added, it would be nice if the cursor
'followed' the new record to its requeried position. Is this possible? Is
there a good way to do this?

hanks

 

[Albert D. Kallal]

Quite a common request.

Normally, I would suggest using a bookmark, but those get lost and become
invalid after a re-query.

So, here is the code I use:

Dim lngPos As Integer

lngPos = Me.Recordset.AbsolutePosition
Me.Requery
If lngPos > (Me.Recordset.RecordCount - 1) Then
lngPos = Me.Recordset.RecordCount - 1
End If
Me.Recordset.AbsolutePosition = lngPos


The test for record count being reduced can be removed if you not doing
deletes. (it is there in case you are on the last record, it id deleted, and
then you do a requery).

You might be able to just use:

Dim lngPos As Integer

lngPos = Me.Recordset.AbsolutePosition
Me.Requery
Me.Recordset.AbsolutePosition = lngPos

It all depends if that other code does any deletions....

 

[Guest]

Hi Sarah,

Before the me.requery, record the ID of teh record you are on. After the
me.requery, use the following code:
me.recordsetclone.findfirst "ID = " & ID
me.recordset.bookmark = me.recordsetclone.bookmark

All this goes in the after update event.

Damian.

 

[Stephen Lebans]

Hi Albert,
if you want to go one step further, and keep the rows displayed exactly as
they were, in terms of position of the ScrollBar, see the SelTop method
here:
http://www.lebans.com/setgetsb.htm
SetGetSB.zip is a database containing functions to allow a user to Set or
Get the current position of a ScrollBar Thumb for a Form.

NEW - Apr. 02/2000 The current ScrollBar position is equal to the current
Record Number being displayed at the Top of the Form.

Works in Form or Datasheet view.

Ver 1.7

Fix bug in SelTop method. Now works with first page of rows properly and
sets the Top row correctly when moving forward in the recordset one row at a
time.

Ver 1.6

Use SelTop to save Restore current row's position after a Requery.

Ver 1.5

Added support for Horizontal ScrollBars.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.