[AccessD] Repositioning a Continuous Form After Requery

Dan Waters df.waters at comcast.net
Fri Jan 28 19:42:28 CST 2011


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

 

 




More information about the AccessD mailing list