Dan Waters
df.waters at comcast.net
Sat Jan 29 07:42:55 CST 2011
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