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
#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.
Access MVP (2013-2016)