[AccessD] Repositioning a Continuous Form After Requery

I always took a bookmark or saved the primary key in the current event
so that after a requery I cold reposition the record pointer to the
record that was last worked on.

Rocky

-------- Original Message --------
Subject: [AccessD] Repositioning a Continuous Form After Requery
From: "Dan Waters" <df.waters at comcast.net>
Date: Fri, January 28, 2011 6:42 pm
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>

Hello to All!

 

In a few of my apps there is a continuous form which has far more rows
than
the form is high. Typically these have a name and a checkbox for various
reasons. Users will want to scroll down and check off names from the top
to
the bottom. However, if the form's recordset needs to be changed or
requeried between each check in the checkbox, the form will redisplay at
its
very top, and the user has to scroll back down to where they were
before.
For one of my customers with over 300 people to go through, this was
mind-numbing.

 

I've been using an API named GetScrollInfo with good success. But this
customer was using Access 2007, and it wasn't working. I was able to
carefully step through my code using Access 2007 to discover that the
structure of an Access 2007 form is different enough from an Access 2003
form to prevent the GetScrollInfo API from working.

 

After much internet searching I found enough information to put together
a
method that works with Access 2003 and Access 2007. Perhaps someone can
use
this.

 

'---------------------------------------------------------------------------
-----------------------

'--- Use this in a continuous form when the form's recordset needs to be
requeried or redefined.

Private Sub chkBox_AfterUpdate()

 

 Dim lngPositionTop As Long

 

 lngPositionTop = GetFormVerticalPosition(Me)

 

 '..... Code Here

 

 Me.Requery

 '-- OR

 Me.Recordset = "something"

 

 Call SetFormVerticalPosition(Me, lngPositionTop)

 

End Sub

'---------------------------------------------------------------------------
-----------------------

Public Function GetFormVerticalPosition (frm As Form) As Long

 

 '-- Purpose: This will provide the number of the row at the top of the
screen _

 so that the form's code can put the form back in its
previous position after _

 the form is requeried. This uses a ratio of heights in
twips of different areas _

 on the form as part of the calculation.

 

 Dim lngRowCount As Long

 Dim lngPositionTop As Long

 

 lngRowCount = (frm.CurrentSectionTop - frm.Section(acHeader).Height) /
frm.Section(acDetail).Height

 lngPositionTop = frm.SelTop - lngRowCount

 

 GetFormVerticalPosition = lngPositionTop

 

End Function

'---------------------------------------------------------------------------
------------------------

Public Sub SetFormVerticalPosition (frm As Form, lngPositionTop as Long)

 

 '-- Purpose: This is used to reset a continuous form's vertical
position after requerying the form.

 

 If frm.Recordset.RecordCount > 0 And lngPositionTop > 1 Then

 frm.Recordset.MoveLast

 frm.SelTop = lngPositionTop

 End If

 

End Sub

 

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com