Example Apps for Businesses, Schools & Developers

First Published 24 June 2022                   Last Updated 14 Feb 2024                   Approx 1.8 MB (zipped)

Section Links:
      Introduction
      No Code Solution
      Code - Continuous Forms
      Code - Datasheet Forms
      Further Update
      Downloads
      Acknowledgements
      Feedback


1.   Introduction                                                                                                                   Return To Top

This example was prompted by a request from AccessForums.net member RMittelman via email and in this thread: GET/SET Subform Horizontal Scrollbar VBA

This was the request:
The form contains 2 subforms which contain too many columns to show them all in my form without scrolling left or right.
Therefore I enable the horizontal scrollbars so we can view all of the fields.

I already have code that automatically scrolls the second subform to the right record based on choosing a record in the first subform. This part works fine.
What I really want to do is find a Windows API solution to programatically scroll the second subform horizontally when I manually scroll the first subform horizontally.

The intent is that when I expose hidden columns in the first subform, the second one will also scroll to the same position horizontally (and if possible, vice-versa).
I would need a subroutine to both get and set the scrollbar position. I used to be able to do this pretty easily in a .Net WinForm, but not sure how to do it in Access.

I would also need an event which fires when the first subform is scrolled horizontally. Since the subform's scrollbar is not really a control (I think), I'm not sure how to do this, or if it can be done.
I've seen reference to Stephen Lebans' code to do this, but that was written prior to Access 2007 and doesn't seem to work with current Access versions.

Does anyone have any ideas how to trap an event when I manually scroll one subform horizontally, GET the new scroll position, then run a subroutine which SETS the horizontal scroll position on the other subform using the Windows API?



The code by Stephen Lebans mentioned above can be found at Set or Get the current position of a ScrollBar Thumb for a Form

Stephen's code allows users to determine the horizontal or vertical position of a form/subform or to move the frm/subform by a specified value.
The idea would then be to move the second subform by exactly the same amount so both appear to move together in unison.

As with most of Stephen Lebans' extremely clever code dating back well over 20 years, his example app:
      a)   was written in Access 97 and needs converting before it can be used in modern versions of Access
      b)   uses a large number of (often obscure) APIs which also need converting to work in 64-bit Access

In other words, following that route would be a huge amount of work.

Luckily there is a MUCH SIMPLER alternative which involves absolutely NO CODE.


2.   No Code Solution                                                                                                             Return To Top

To do this, the two subforms (A and B) are placed in another container subform (C).

Then to synchronise horizontal scrolling:
1. Make sure both subforms A & B are wide enough so no horizontal scrollbars are required.
2. Place both subforms in subform C and set the scrollbar to horizontal only
3. Make the width of subform C equal to the main form but less than the widths of subforms A & B
4. Remove both scrollbars from the main form
5. When the main form is open, subform C's horizontal scrollbar will scroll subforms A and B in unison.

The screenshots below show how this works:

Before Scrolling

Horiz1
After Scrolling Horizontally

Horiz2
If you prefer to see the subform vertical scrollbars without horizontal scrolling, place these on the left by changing the subform orientation to Right to Left:

Vertical Scrollbars on left

LeftScrollbars
Orientation form property

OrientationR2L

A similar approach can be used to synchronise vertical scrolling:

Before Scrolling

Vert1
After Scrolling Vertically

Vert2

Click to download the original example app:   Synchronise Subform Scrolling v2     (zipped)


3.   Code - Continuous Forms                                                                                               Return To Top

UPDATE 1 - 9 July 2022 - Version 1.5
Of course the disadvantage of the above no-code approach is that the vertical scrollbar disappears when doing horizontal scrolling.
Where it is important to see and use both scrollbars, a different approach is needed which does require code

This is based on a significantly modified version of Stephen Lebans’ Get Set Scrollbar Position code originally written for Access 97.
Various modifications were made by Ron Mittelman and published at AccessForums.net
Further changes were made by myself to ensure the API declarations worked correctly with both 32-bit and 64-bit Access.

Version 1.5 was first published here in July 2022.
The code used allows either sub form to be selected as the MASTER.
The SLAVE subform will move when the MASTER subform is scrolled horizontally or vertically
The subform scrollbar positions are updated automatically using a timer event in the main form. Subform 1 as MASTER - vertical scroll

GetSetScrollbars1_v1.5
Subform 2 as MASTER - horizontal and vertical scroll

GetSetScrollbarsDS2_v1.5
The complex code that handles the scrolling is in a class module clsSyncScrollbars with the API declarations in a standard module modSyncScrollbars.

The form code is as follows:

Option Compare Database
Option Explicit

Dim ss As New clsSyncScrollbars

'========================================
Private Sub chkAutoSync_Click()

      'enable/disable controls depending on whether autosync is ticked
      Me.fraSubformMaster.Enabled = Me.chkAutoSync
      Me.txtVert.Enabled = Me.chkAutoSync
      Me.txtHoriz.Enabled = Me.chkAutoSync
End Sub

'========================================
Private Sub Form_Close()
      'tidy up
      Set ss = Nothing
End Sub

'========================================
Private Sub Form_Load()

      With ss
            Set .Form1 = Me.fsubStudents.Form
            Set .Form2 = Me.fsubStudentAddresses.Form
            Me.txtHoriz = .GetScrollbarPos(feForm1, stHorizontal)
            Me.txtVert = .GetScrollbarPos(feForm1, stVertical)
      End With

      Me.fraSubformMaster.Enabled = Me.chkAutoSync

End Sub

'========================================
Private Sub Form_Timer()

If Me.chkAutoSync = True Then

      'update scrollbar positions & sync subforms
      With ss
            If fraSubformMaster = 1 Then 'subform 1 is MASTER
                  Me.txtHoriz = .GetScrollbarPos(feForm1, stHorizontal)
                  Me.txtVert = .GetScrollbarPos(feForm1, stVertical)
                  .SyncScrollbars feForm1, stHorizontal
                  .SyncScrollbars feForm1, stVertical
            Else 'subform 2 is MASTER
                  Me.txtHoriz = .GetScrollbarPos(feForm2, stHorizontal)
                  Me.txtVert = .GetScrollbarPos(feForm2, stVertical)
                  .SyncScrollbars feForm2, stHorizontal
                  .SyncScrollbars feForm2, stVertical
            End If
      End With

End If

End Sub



The first version of my example app based on this code is available below:

Click to download the example app:   Get Set Scrollbars v1.5     ACCDB file     Approx 1.8 MB     (zipped)

UPDATED 14 Feb 2024: to fix error in 64-bit Access

Ron's final version can be found in post #23 of the forum thread: GET/SET Subform Horizontal Scrollbar VBA


4.   Code - Datasheet Forms                                                                                                 Return To Top

UPDATE 2 - 28 July 2022 - Version 1.53
I decided to expand this example to cover datasheet subforms as well.

This followed an issue raised by gsevior in this thread at Utter Access forum: What Datasheet Form Events Fires when setting focus to the Totals Row.
He was using the Form_Current event to synchronise 2 subforms but found that his code failed when end users clicked the Totals row. He wrote:

I have a form with two subforms on it. The top subform has the main (master record in a datasheet format) and a lower subform displays related information for the current record displayed in the top subform. The On current Event of the top form causes the subform to requery. This all works fine until the user clicks into the Totals row at the bottom of the datasheet. In this case, the On Current event does not fire, and hence the bottom subform continues to display the data for the row that previously have a focus in the top subform.

I cannot seem to find any event that fires when the Totals rows receive focus. Does anyone know of an event relating to the totals row of a datasheet, or have an idea of how to circumvent this. Note both the top and bottom subforms are datasheets, amalgamating them is not an option. Is there a method where by the user can be prevented clicking into the totals row (seems unlikely as they can not then set up the totals they want to view)

The simple answer to the question in thee thread title is that there are no events that fire when the Totals row receives focus.

Similarly no events fire when the scrollbar is moved

I thought this example might also provide a solution for datasheet subforms

The basic code was simple enough to adapt but, I also experienced issues when a Totals row was clicked. In my example app, synchronised scrolling still worked using a Form_Timer event but there was a visual glitch due to a form repainting issue

DatasheetScrollGlitch
NOTE:
I reported the issue to Microsoft & was advised that it has been flagged as an issue to be fixed . . . at some point!

Rather than waiting for a fix, I tried a lot of different approaches to the code before finding a solution.

I found that the code used to toggle the Totals row on/off in tables and queries has absolutely no effect in forms.

Despite a lot of effort, I was unable to find any code that correctly detects the Totals row property value as true/false in a form, let alone set its value!

Eventually I found a simple fix to the issue which worked perfectly (thanks to Karl Donaubauer / Gunter Avenius for bringing this to my attention).

In my solution, the totals row state is toggled on/off when the subform is entered and toggled back again when the subform is exited.
Code is also used so the end user isn't aware of either change being made.

The datasheet subforms can now be synchronously scrolled even if a totals row is used & clicked by the end user

Subform 1 as MASTER - vertical scroll

GetSetScrollbarsDS1_v1.53
Subform 2 as MASTER - horizontal and vertical scroll

GetSetScrollbarsDS2_v1.53
The relevant form code (which must be applied for each subform) is as follows:

CODE:

Private Sub fsubStudentsDS_Enter()

On Error Resume Next

      'toggle totals row but don't update display until exit
      Application.Echo False
      Application.CommandBars.ExecuteMso ("RecordsTotals")

End Sub

'=====================================

Private Sub fsubStudentsDS_Exit(Cancel As Integer)

      'toggle totals row and update display on exit
      Application.CommandBars.ExecuteMso ("RecordsTotals")
      Application.Echo True

End Sub



The updated example app includes both continuous and datasheet subforms

MainForm2

Click to download the updated example app:   Get Set Scrollbars v1.53     ACCDB file     Approx 1.8 MB     (zipped)

UPDATED 14 Feb 2024: to fix error in 64-bit Access


5.   Further Update                                                                                                               Return To Top

UPDATE 3 - 21 Oct 2023 - Version 1.57
A fellow Access developer from Spain, Xavier Batlle, recently contacted me with various excellent suggestions to improve this example app.

In the latest version, either subform can be set as the MASTER either by using the option controls or by clicking/moving anywhere in the subform.
This significantly improves the useability of the form.

MainForm156
Continuous Subform 1 as MASTER - vertical scroll

GetSetScrollbars1
Continuous Subform 2 as MASTER - horizontal and vertical scroll

GetSetScrollbars2
The updated code in the main form includes these two events:

Private Sub fsubStudentAddresses_Enter()
      'suggested by Xavier Batlle
      Me.fraSubformMaster = 2
      fraSubformMaster_AfterUpdate
End Sub

Private Sub fsubStudents_Enter()
      'suggested by Xavier Batlle
      Me.fraSubformMaster = 1
      fraSubformMaster_AfterUpdate
End Sub



This ensures that on entering either subform it automatically becomes the MASTER.
Each subform also has additional MouseWheel event code which has the same effect. For example:

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
      'suggested by Xavier Batlle
      Parent.fraSubformMaster = 2
End Sub



Similar code changes were made to the datasheet variant of this app, but caused some issues with the code used to bypass the totals row bug described above by toggling the totals row on/off.

I expect the totals row bug to be fixed in the relatively near future. For now, I recommend removing removing the totals row from both datasheet subforms.

Datasheet Subform 2 as MASTER - horizontal scroll

GetSetScrollbarsDS2_v1.56

Click to download the updated version of the example app:   Get Set Scrollbars v1.57     (zipped)


5.   Downloads                                                                                                           Return To Top

For convenience, all FOUR of the example apps are also available here:

a)   the original no-code example app:                   Synchronise Subform Scrolling v2     (zipped)

b)   the continuous forms example app:                 Get Set Scrollbars v1.5     (zipped)              UPDATED 14 Feb 2024 to fix error in 64-bit Access

c)   the datasheet forms example app :                  Get Set Scrollbars v1.53     (zipped)            UPDATED 14 Feb 2024 to fix error in 64-bit Access

d)   the latest version of the example app :            Get Set Scrollbars v1.57     (zipped)


6.   Acknowledgements                                                                                             Return To Top

I would like to thank both Ron Mittelman and Xavier Batlle for their significant contributions in the development of these example applications


7.   Feedback                                                                                                         Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Do let me know if you find any errors or omissions.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                     Mendip Data Systems                     Last Updated 14 Feb 2024



Return to Example Databases Page




Return to Top