Example Apps for Businesses, Schools & Developers

Version 1.1           Last Updated 24 June 2022                 Approx 1 MB (zipped)


This example was prompted by a request from AccessForums.net member RMittleman 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.

To do so, 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                             Click any image to view a larger version

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                             Click any image to view a larger version

Vert1
After Scrolling Vertically

Vert2



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



NOTE: In the next few days, I intend to upload a video to YouTube demonstrating this approach.



Colin Riddington           Mendip Data Systems                 Last Updated 24 June 2022



Return to Example Databases Page




Return to Top