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