First Visible Record in Continuous Form

[Joel Smith]

Is there any way to determine the record number of the first record visible
on a continuous form. This number is displayed in the tooltip which pops up
when you click or drag the scroll bar. I would like to use this value in
code to reposition the form to the same scrolled position after a requery
instead of having it jump. I have always considered it to be sloppy when a
forms jumps around when a user clicks on a record or updates a field.

Thanks for the help.

Joel Smith
jo...@asystech.com

[Michel Walsh]

Hi Joel,

After a requery, the record "position" may have changed anyhow, or have been
deleted, so better to remember the key value of the primary key field:

Dim tmp As Long
tmp = Me.ControlAssociatedToPrimaryKeyValue

Me.Requery ' also "destroying" bookmarks

With Me.RecordsetClone
.FindFirst "PrimaryKeyFieldName >=" & tmp
Me.Bookmark = .Bookmark
End With

Hope it may help,
Vanderghast, Access MVP

Joel Smith <jo...@asystech.com> wrote in message
news:OWkv72#e#GA.264@cppssbbsa03...

[Joel Smith]

Thanks Michel,
That works and is the technique I have been using, but it is a compromise.
With the form in a scrolled position, when the user clicks on the fifth
record from the top it will magically jump to the top after the requery and
causes them to lose their place in the record set. Apparently the little
record selector arrow showing the current record is no enough of a clue and
they often have to scroll the form to regain their mental position. I would
like to come back to the form with that record again in the fifth position,
hence the need to reference the first visible record if it's possible.

Michel Walsh <Vande...@email.msn.com> wrote in message
news:e#ZPaEEf#GA.260@cppssbbsa03...

[Stephen Lebans]

Hi Joel, the following is definately a visual kludge, but you asked for
results, not good looks. :-)

There is no error checking/handlers for blank recordsets etc. As
documented, it is set to work with a continuous form of 18 visible rows.

*******START CODE*******

'CurSectionTop is declared as a Public variable in your Forms General
Declarations
Dim mytop As Long
Dim mycurtop As Integer
Dim x As Integer
Dim numrows As Integer

'numrows is the number of rows in your continuous form
numrows = 18

mycurtop = CurSectionTop
'CurSectionTop is a Public Variable - Value set on Form's Current event
'from the Current event -> CurSectionTop = Me.CurrentSectionTop

mytop = Me.SelTop
'Current record number

Me.Requery

DoCmd.GoToRecord acDataForm, "YourFormName", acLast
Me.SelTop = mytop

'reset counter
x = 0

If (mytop <= numrows) Then
x = 1
Else: x = (mytop - numrows)
End If

Do Until Me.CurrentSectionTop = mycurtop
DoCmd.GoToRecord acDataForm, "YourFormName", acGoTo, x
Me.SelTop = mytop
x = x + 1

Loop
*******END CODE*******

Unfortunately you cannot turn off screen repainting to speed up the
function. You could easily add logic to reduce the numrow counter
depending on where the physically selected record is in relation to the
top of the continuous form. I coded the worst case obviously.

Is it worth it to jump through all of these hoops to reset the form
exactly to where the user was at Requery time? Probably not, but you can
use this function without the Loop to:
Save current record#
Isssue your Requery
reset the display so that the selected record is the first record
displayed in the continuous form. You could also just as easily place
the selected record in the middle of the form or whatever!

Thanks for the challenge! Have fun!

HTH
Stephen Lebans
maca...@nbnet.nb.ca

Joel Smith wrote in message ...

SNIP

[Joel Smith]

Thanks Stephen, the property CurrentSectionTop actually allows me to
calculate the position of the selected record relative to the top of the
form i.e.

NumRecordsfromTop = Me.CurrentSectionTop / HeightOfRecord
(In my current form the height is 240 twips but it can be calculated).

The best technique so far seems to be to return to the selected record after
the requery using a bookmark and then backing up the current record in one
jump using:

DoCmd.GoToRecord , , acPrevious, NumRecordsfromTop

This causes a flicker as the form moves back and forth but this shouldn't be
objectionable. I'll try this on several of my users and see. They're the
ultimate auhority on what's good or bad.

Joel Smith
jo...@asystech.com

Stephen Lebans <macarth...@nbnet.nb.ca> wrote in message
news:7e5nkc$hep$1...@garnet.nbnet.nb.ca...

[Stephen Lebans]

Hi Joel,
glad I could help. Would you mind Emailing or Posting the code for your
routine when you get done with it.

Thanks Joel.

Stephen Lebans
maca...@nbnet.nb.ca

>Thanks Stephen, the property CurrentSectionTop actually allows me to
>calculate the position of the selected record relative to the top of

the form >SNIP