Synchronizing subforms - What record is displayed on top of a continuous subform after using scrollbar?

Over the years, I've seen posts on various websites and in other online forums where users asked how to determine the top record displayed in a continuous sub-form, but I don't recall ever seeing an answer before,  

As mentioned above, I'm working on an appointment scheduler for a small clinic in my area.  They have a number of health care providers who need to be scheduled simultaneously, and when someone walks in, without an appointment, or calls in for an "urgent appointment", they need to be able to see all of the available care providers, to see who is available.  

The image above provides an example of what I'm doing, but synchronizing those sub-forms proved to be much more challenging than I anticipated, for two reasons:

1.   There is no way, within Access to determine what record is displayed at the top of a sub-form.

2.  There are no events associated with a sub-forms scrollbar;  there is no way, within the form to determine that the Up or Down buttons in the scrollbar were clicked or that the scrollbar was dragged up or down.

How do you determine what record is on top of a sub-form?

Fortunately, an MVP buddy of mine (Jim Dettman) pointed me to a sample database posted by the renowned Access guru Stephen Lebans, back in 2000.  In that article, Stephen talks mostly about re-querying a continuous form and then setting the focus back to the same record, in the same position within the sub-form as it was in prior to the re-query.  I found this interesting, so I downloaded the database and played with all of the forms, only one of them (frmSetScrollBarUsingSelTop) worked in my A2007 environment.  Realizing that his database was created in 2000, I started tinkering around and found one line of code:

If fGetClassName(hWnd_VSB) = "scrollBar" Then

which needed to be changed in two procedures (fIsScrollBar and fIsScrollBarNz).  It appears that sometime between Office 97 and Office 2007, the value of the classname for the scrollbar changed from "scrollbar" to "NUIscrollbar".  So, after changing those two lines of code to:

If fGetClassName(hWnd_VSB) = "NUIscrollBar" Then

all of his forms worked again.   I don't claim to understand how the Windows API functions (apiGetScrollInfo, apiSetScrollInfo) work, but Stephen's code makes it possible to identify and set the record visible as the top row of a continuous sub-form, which solved problem #1.  I've been unable to reach Stephen so I am not posting a new copy of his sample database within this article but you can download it from here and make the changes I mentioned above.

Detecting when the scrollbar is used:

Having solved problem #1, I had to determine how to detect movement of the scrollbar.  Unfortunately, there is no event associated with either clicking the Up or Down buttons on a vertical scroll bar, or with dragging the scrollbar.  Lebans resolved this problem by adding a TimerEvent to his forms, so I did likewise.

Warning: adding timers to forms adds a lot of overhead and can have unexpected results.  In this case, I want to keep the subforms well synchronized and am not going to be doing any edits on this particular form or the other subforms, so setting a timer interval of 500 (0.5 seconds) seems to work reasonably well.

Getting the application to run in 64 bit Office:

The sample database has been modified to work in both 32 and 64-bit versions of Office.  In addition to changing the class name of the scrollbar window in Lebans' code, I had to use conditional compilation to reformat all of the declarations and some of the functions in the modGet-SetScrollInfo module.  Note the insertion of "PtrSafe" in the declaration and that some variables declared as Long in the original declaration have to be declared as LongPtr in the Win64 version. 

Two examples of this are:

#If Win64 Then
    Declare PtrSafe Function GetParent Lib "user32" (ByVal hWnd As LongPtr) As LongPtr
#Else
    Declare Function GetParent Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

and

#If Win64 Then
    Private Function fIsScrollBar(frm As Form) As LongPtr
    Dim hWnd As LongPtr
    Dim hWnd_VSB As LongPtr
#Else
    Private Function fIsScrollBar(frm As Form) As Long
    Dim hWnd As Long
    Dim hWnd_VSB As Long
#End If

I'd like to thank fellow Access MVP, Jack Stockton, for help in resolving a compile error I was having with my conditional compilation statements.

Note: In this latter example, I included both the function declaration statement as well as the dimension statements for several of the variables within the conditional compilation #If / #Else / #End If construct.  I could have split that into two separate #IF statements, one for the function declaration and one for the variables, but elected not to.

Sample database: SynchronizeSubforms.accdb

The sample database contains a main form with three continuous sub-forms.  

When you move the scrollbar in any of the continuous sub-forms the top record in the other sub-forms is adjusted to display the same record on top.   I've also configured the sample so that whatever record is selected in one sub-form gets selected in the others.

The code to actually implement this synchronization turns out to be quite simple, once you add Lebans code into the database.  The sub-form has a timer event that checks the state of the scrollbar and the selected record every 1/2 second (timer interval = 500).  

Public CalledBy As Integer
Public MyTop As Long
Public MyRow As Long

Private Sub Form_Timer()

    Dim lngTop As Long
    Dim lngRow As Long
   
    Me.TimerInterval = 0
    lngTop = fGetScrollBarPos(Me)
    lngRow = Me!row
    If lngTop <> MyTop Or lngRow <> MyRow Then
        MyTop = lngTop
        MyRow = Me!row
       Call Me.Parent.SynchSubforms(Me.CalledBy, Me.MyTop, Me.MyRow)
    End If
    Me.TimerInterval = 500
   
End Sub

This procedure compares the current top and row values to the previous ones, stored in the form level variables,  and if they have changed, resets the previous values and calls the main forms SynchSubforms( ) procedure.  The SynchSubforms procedure on the main form loops through each of the sub-forms, skipping the one that called it and performs a series of steps to:

1.  Set the sub-forms TimerInterval to zero. I did this to prevent that event from firing while I'm making adjustments to the sub-from.

2.  Adjust the top position and the selected record in the sub-form

3.  Adjust the form level variables (MyTop and MyRow) to prevent the next timer interval from detecting the change

4.  Resets the TimerInterval to 500

Public Sub SynchSubforms(CalledBy As Integer, PassedTop As Long, PassedRow As Long)

    Dim intLoop As Integer
    Dim frm As Form
   
    For intLoop = 1 To 3
        If intLoop <> CalledBy Then
            Set frm = Me.Controls("sub_" & intLoop).Form
            frm.TimerInterval = 0
            With frm.RecordsetClone
                .FindFirst "Row = " & PassedRow
                If Not .NoMatch Then
                    frm.Bookmark = .Bookmark
                End If
            End With
            fSetScrollBarPos frm, PassedTop
            frm.MyTop = PassedTop
            frm.MyRow = frm!row
            frm.TimerInterval = 500
        End If
    Next
       
End Sub

In my next article, I'll discuss how to configure the sub-forms to display the appointment schedules as depicted in the image at the top of the page.

Note: Stephen Lebans is no longer active in the Access community and does not respond to emails, but he has a collection of really neat tricks he developed over the years on his website.

Dale Fye
Access MVP (2013-2016)