[AccessD] Repositioning a Continuous Form After Requery

Dan Waters df.waters at comcast.net
Sat Jan 29 08:54:00 CST 2011


High Rocky,

If the PK is not a part of the recordset after requerying, the form will
display the top records.

If the action leaves the PK in the new recordset, this will put the record
you were working on at the top of the form, where before the record may have
been lower. 

Dan


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rockysmolin at bchacc.com
Sent: Saturday, January 29, 2011 8:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Repositioning a Continuous Form After Requery

Dan:

Would

       lngSavedID=Me.PKID
       Me.Requery
       Me.RecordsetClone.FindFirst "fldPKID = " & lngSavedID
       Me.Bookmark = Me.RecordsetClone.Bookmark

work?  It would set your recordset back to the record before the
requery.


Rocky


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

Hi Rocky,

Sometimes the record I just worked on would no longer be displayed after
a
user checks it and the form is requeried. But I still wanted the form to
be
positioned back where it was so the before and after records are still
on
the screen.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rockysmolin at bchacc.com
Sent: Friday, January 28, 2011 11:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


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


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


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





More information about the AccessD mailing list