Page 1 Page 2 Page 3


Version 3.94                     First Published 10 Mar 2019                              Last Updated 9 Aug 2023                           Difficulty level:     Moderate

Section Links (this page):
        New Features
        High Resolution Monitors
        Portrait Monitors
        Stretch/Shrink Forms
        Refresh Display
        Image Scaling on Command Buttons
        Additional Templates
        Using AFR in your own applications
        Downloads
        YouTube Videos
        Summary/Review
        Feedback


1.   New Features - Version 3.94                                                                                                Return To Top

This article discusses several significant new features added to the example application in version 3.90.
Further changes have been made in version 3.94 to simplify the code and fix an issue with boolean variables for non-English language users. No new features were added.

The example database wassignificantly updated with many new features including:

a)   support for high resolution monitors e.g. 3840 x 2160 to handle possible issues related to the maximum permitted form width
b)   improved support for portrait monitors e.g. 1080 x 1920 resolution
c)   added code to allow forms & controls to be stretched/shrunk 'on the fly'
d)   added code to refresh display when forms are moved to a secondary monitor with a different size/resolution
e)   improvements to zoom form code - already covered in the third part of this article
f)   added code to allow scaling of command button images
g)   two additional template forms

Each of these new features is discussed below:



2.   High Resolution Monitors                                                                                                       Return To Top

Increasingly, large screen high resolution monitors e.g. 3840 x 2160 are being used.
This poses a new challenge for automatic form resizing as one of the limitations of Access is that forms cannot be more than 22.75 inches (57.79 cm) wide
The reason for this restriction is that form dimensions are integer values measured in TWIPS (One TWentieth of an Imperial Point Size)

The integer datatype value range is +32767 to -32768 and there are 1440 twips in one inch.

So a form width of 22.75 inches corresponds to 1440*22.75 = 32760 twips. Any attempt to enlarge a form beyond this width will fail

Attempting to do so using automatic form resizing will result in objects being wrongly placed on the screen.
Once the integer limit is reached, Access will then cycle throughthe negative integer values.
The result is that form controls on the right edge of the form will be incorrectly shifted to the left making the form potentially unusable

For example, this is the main form from an early version (since updated) of my Wizhook Object example app in design view.
It is designed with a base resolution of 1024x768 and a form design width = 10.48 inches (26.619) cm

Wizhook24Design
It displays correctly when viewed on a monitor at e.g. 1920x1080 where the scaling factor is 1920/1024 = 1.875
In that case, AFR successfully scales up the form width to 10.48 x 1.875 = 19.65 inches (49.91 cm)

Wizhook24Form
However on a high resolution monitor at e.g. 3840x2160, the scaling factor = 3.75 (using the original code)
This resulted in the old AFR code trying to scale the form width up to 10.48 x 3.75 = 39.3 inches (99.82 cm) . . . which isn't allowed.

The result was a mess with various controls from the right side of the form shifted left and superimposed on other controls!

Wizhook24Error
To fix this issue, the first section of the Resize procedure in modResizeForm was modified.
The modified code ensures that forms cannot be scaled above the maximum allowed width.

Fixing this issue required just one line of additional code (shown in RED below)

Public Sub Resize(sngFactor As Single, frm As Access.Form)

'---------------------------------------------------------------------------------------
' Procedure :                   Resize
' DateTime :                     27/01/2003 with many updates by Colin Riddington
' Authors :                       Jamie Czernik / Colin Riddington
' Purpose :                       Routine re-scales the form sections and controls.

' Modifications by Colin Riddington:
'04/03/2019 :                Fixed issue with font size issue on some controls
'02/10/2021 :                Updated code to handle acNavigationControl & acNavigationButton
'09/11/2022 :                Added check to ensure resized form width doesn't exceed integer limit - needed for high resolution monitors
'18/11/2022 :                 No further changes needed for new code in v3.78
'---------------------------------------------------------------------------------------

Dim ctl As Access.Control, sngNCW As Single

On Error Resume Next

      With frm
            'First check resized form doesn't exceed maximum form width (32767 twips) to prevent display issues
            'Set limit to 32000 to allow some leeway - then if necessary adjust scaling factor so it fits
            If .Width * sngFactor > 32000 Then sngFactor = 32000 / .Width

            'Now resize width/height for each section:
            .Width = .Width * sngFactor
            .Section(Access.acHeader).Height = .Section(Access.acHeader).Height * sngFactor
            .Section(Access.acDetail).Height = .Section(Access.acDetail).Height * sngFactor
            .Section(Access.acFooter).Height = .Section(Access.acFooter).Height * sngFactor
      End With

      ' . . . . REST OF CODE UNCHANGED



NOTE: The full code for the Resize procedure is listed in the third part of this article.

The result is that the MAXIMUM form width after AFR scaling is limited to 32000 twips = 22.22 inches (56.44 cm) ensuring no visual glitches occur



3.   Portrait Monitors                                                                                                                           Return To Top

Although the vast majority of Access users have landscape monitors of various shapes and sizes, some users do have portrait monitors.
Also those with Windows tablets can rotate the screen to portrait mode

This can be challenging for any Access application designed for use on landscape monitors whether or not automatic form resizing (AFR) is used
This is because much of the form width may only be accessible by using the horizontal scrollbar.
In addition, there may be a lot of empty space in the lower part of the screen.

I decided to address this issue in the latest version of the AFR code.

The updated code is the GetCurrentFactor procedure which is referenced in multiple places in the main Resize procedure
NOTE: This code completely replaces theGetFactor procedure used until version 3.75

Public Function GetCurrentFactor() As Single

'---------------------------------------------------------------------------------------
' Procedure :                 GetCurrentFactor
' DateTime :                 18/11/2022
' Authors :                     Colin Riddington
' Purpose :                    Function returns the value that the form's/control's height, width, top
'                                       and left should be multiplied by to fit the current screen resolution.

'NEW CODE v3.78     Replaces older GetFactor (now removed)
'---------------------------------------------------------------------------------------

Dim sngFactorP As Single, intWidth As Integer, intHeight As Integer

On Error Resume Next

      If GetCurrentScreen("dpiwin") <> 96 Then
            sngFactorP = DESIGN_PIXELS / GetCurrentScreen("dpiwin")
      Else
            sngFactorP = 1 'error with dpi reported so assume 96 dpi
      End If

      '============================================================
      'NOTE: After further tests, this section can hopefully be simplified to Case "Widesceen" & Case Else
      Select Case FormFactor

      Case "4:3"       '1.33
            GetCurrentFactor = (lngH / DESIGN_HORZRES) * sngFactorP

      Case "5:4"       '1.25
            GetCurrentFactor = (lngH / DESIGN_HORZRES) * sngFactorP

      Case "Widescreen"       'e.g. 16:10 = 1.66 or 16:9 = 1.78
            GetCurrentFactor = (GetBaseFormFactor * lngV / DESIGN_HORZRES) * sngFactorP

            'Modified by Colin Riddington v3.6 23/11/2021
            'This fixes very rare issue where value of GetCurrentFactor < 1 causes forms to shrink not grow!
            'NOT WIDELY TESTED AS YET AS VERY RARE!
            If GetCurrentFactor < 1 Then GetCurrentFactor = (lngH / DESIGN_HORZRES) * sngFactorP

      '======ADDED Portrait v3.80=============
      Case "Portrait"      'e.g. 1080x1920
           GetCurrentFactor = (lngH / DESIGN_HORZRES) * sngFactorP
            ' GetCurrentFactor = (lngV / DESIGN_HORZRES) * sngFactorP

      Case "Other"       'e.g. split screen @1720x1440
            GetCurrentFactor = (lngH / DESIGN_HORZRES) * sngFactorP

      End Select
      '============================================================

End Function


Initial tests indicate that this works well. However I would appreciate feedback from other users

Below is a template form designed in portrait mode

PortraitFormDesign
When viewed on a portrait monitor, it fills the screen both horizontally and vertically

PortraitForm
Standard forms designed for use in landscape monitors fill the screen horizontally leaving empty space below

MainFormPortrait
NOTE: The above screenshots were all done on a 12 inch tablet and in each case all the text was legible with no scrolling needed



4.   Stretch/Shrink Forms On the Fly                                                                                     Return To Top

Almost all forms used in the earlier versions of the AFR example app were designed to be automatically resized on form load

The form zoom code also allows individual users to scale up/down standard forms (including datasheets) to suit their own needs/preferences.

Until now, it hasn't been possible to stretch or shrink forms 'on the fly'. This feature has now been added in example form frmExample4

In this case, AFR is NOT used at Form_Load. When the form is opened, its initial size is identical to that of the form design.

ExampleForm4
In this example, the form and its controls are stretched/shrunk by holding the left mouse button on the form border and dragging with the mouse to resize.
Code is used to maintain the form aspect ratio (width : height) to ensure no distortion occurs

For example, here the form has been stretched to 1.5 times its original size

ExampleForm4x1.5
And again, this time to 2.15 times the original size. The position and scale of all items remains in proportion

ExampleForm4x2.15
You can, at any time, click the Restore Original Size button to revert to the original dimensions

Although less useful, you can also shrink the form to less than its original size. For example, at 0.7x original size

ExampleForm4x0.7
The form and its contents have remained in propertion.

However, as was made clear earlier in this set of articles, form resizing works well when scaled up but less so when scaled down.

This is also true in this case. At very small sizes, you will begin to notice that form sections don't remain in proportion
For example, at 0.4x original size, the form header and footer are proportionately taller than they should be

ExampleForm4x0.4
To handle this, I have added the option to prevent forms being shrunk below the original size.
Tick the checkbox on the form to implement this feature

Any attempt to shrink the form below its original size, will result in this message

ExampleForm4ShrinkMsg
Click OK to restore the original form size

Here is a short YouTube video (05:40) demonstrating this feature:

       



At its simplest, the code used to manage the stretch/shrink feature requires just the following lines of code

Private Sub Form_Load()

      'get form dimensions on load
      GetOriginalSize Me

      'resize form depending on resolution & monitor size
      ResizeForm Me

End Sub

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

Private Sub Form_Resize()

      'maintain the width:height ratio when the form is resized using a mouse
      StretchShrink Me

End Sub



The above code first calls the GetOriginalSize procedure from modResizeForm

Sub GetOriginalSize(frm As Access.Form)

'---------------------------------------------------------------------------------------
' Procedure :                     GetOriginalSize
' DateTime :                      23/12/2022
' Author :                           Colin Riddington
' Purpose :                         Used to get original size of form & its sections before resizing using a mouse
'---------------------------------------------------------------------------------------

On Error Resume Next

      DoCmd.Restore

      'get form dimensions on load
      getOrigWindow frm

      'get height of each section
      lngHeaderHeight = frm.FormHeader.Height
      lngFooterHeight = frm.FormFooter.Height
      lngDetailHeight = frm.Detail.Height

      'on form load set both lngNew & lngOld values to equal lngOrig values
      lngNewWidth = lngOrigWidth
      lngOldWidth = lngOrigWidth

      lngNewHeight = lngOrigHeight
      lngOldHeight = lngOrigHeight

End Sub


The StretchShrink procedure (also from modResizeForm) is used to maintain the form aspect ratio (width:height ration) when it is resized using the mouse:

Sub StretchShrink(frm As Access.Form)

'---------------------------------------------------------------------------------------
' Procedure :                     StretchShrink
' DateTime :                      23/12/2022
' Author :                           Colin Riddington
' Purpose :                         Used to maintain width:height ratio when the form is resized using a mouse
'---------------------------------------------------------------------------------------

On Error Resume Next

      Dim sngStretchWidth As String, sngStretchHeight As String
      Dim blnRestore As Boolean

      Application.Echo False

      'ensure width & height are changed in proportion as form is stretched / shrunk
      GetResizedWindow frm

      sngStretchWidth = lngNewWidth / lngOldWidth
      sngStretchHeight = lngNewHeight / lngOldHeight

      If sngStretchWidth >= sngStretchHeight Then
            If sngStretchWidth >= 1 Then
                  sngStretchHeight = sngStretchWidth
                  lngNewHeight = lngOldHeight * sngStretchHeight
            Else
                  sngStretchWidth = sngStretchHeight
                  lngNewWidth = lngOldWidth * sngStretchWidth
            End If

      Else
            If sngStretchHeight >= 1 Then
                  sngStretchWidth = sngStretchHeight
                  lngNewWidth = lngOldWidth * sngStretchWidth
            Else
                  sngStretchHeight = sngStretchWidth
                  lngNewHeight = lngOldHeight * sngStretchHeight
            End If

      End If

      DoEvents
      'move to top left & update form size
      DoCmd.MoveSize 0, 0, lngNewWidth, lngNewHeight

      'adjust height of each section in proportion
      If GetStretchFactor <> 1 Then       'resize necessary
            frm.Detail.Height = lngDetailHeight * GetStretchFactor
            frm.FormHeader.Height = lngHeaderHeight * GetStretchFactor
            frm.FormFooter.Height = lngFooterHeight * GetStretchFactor

            'resize all controls
            Resize GetStretchFactor, frm

      End If

      'set old values to equal new values (used in GetStretchFactor)
      lngOldWidth = lngNewWidth
      lngOldHeight = lngNewHeight
      Application.Echo True

End Sub



However, the StretchShrink procedure cannot also modify the contents of any subforms when the form is resized using the mouse.

Where there is a subform involved, instead use a local version of the code called MaintainAspectRatio in the form itself and reference the subform by name

Private Sub MaintainAspectRatio()

'---------------------------------------------------------------------------------------

' Procedure :                     MaintainAspectRatio
' DateTime :                      22/11/2022
' Author :                           Colin Riddington
' Purpose :                         Used to maintain width:height ratio when the form is resized using a mouse

' LastUpdated :                 23/12/2022
'---------------------------------------------------------------------------------------

On Error Resume Next

      Dim sngStretchWidth As String, sngStretchHeight As String
      Dim blnRestore As Boolean

      Application.Echo False

      'ensures width & height are changed in proportion as form is stretched / shrunk
      GetResizedWindow Me      'procedure in modResizeForm

      'check if shrunk below original size & whether that is restricted
      If GetPreventFormShrinkValue = "Yes" And blnRestore = False Then
            If lngNewWidth < lngOrigWidth And lngNewHeight < lngOrigHeight Then
            FormattedMsgBox "WARNING:" & _
                  "@Cannot shrink form less than its original size @", vbInformation, "Form at original size"
                  cmdRestore_Click
            End If
      End If

      'v3.90 - If code gets to here, all is OK so carry on ...
      sngStretchWidth = lngNewWidth / lngOldWidth
      sngStretchHeight = lngNewHeight / lngOldHeight

      If sngStretchWidth >= sngStretchHeight Then
            If sngStretchWidth >= 1 Then
                  sngStretchHeight = sngStretchWidth
                  lngNewHeight = lngOldHeight * sngStretchHeight
            Else
                  sngStretchWidth = sngStretchHeight
                  lngNewWidth = lngOldWidth * sngStretchWidth
            End If

      Else
            If sngStretchHeight >= 1 Then
                  sngStretchWidth = sngStretchHeight
                  lngNewWidth = lngOldWidth * sngStretchWidth
            Else
                  sngStretchHeight = sngStretchWidth
                  lngNewHeight = lngOldHeight * sngStretchHeight
            End If

      End If

      DoEvents

      'move to top left & update form size
      DoCmd.MoveSize 0, 0, lngNewWidth, lngNewHeight

      'adjust height of each section in proportion
      If GetStretchFactor <> 1 Then       'resize necessary
           Me.Detail.Height = lngDetailHeight * GetStretchFactor
           Me.FormHeader.Height = lngHeaderHeight * GetStretchFactor
           Me.FormFooter.Height = lngFooterHeight * GetStretchFactor

           'resize all controls in main form by the stretch factor
           Resize GetStretchFactor, Me

           'resize all controls in named subform by the stretch factor
           Resize GetStretchFactor, subfrmControlTypes.Form
      End If

      'v3.90 - update textbox display
      Me.txtStretchFactor = Round(lngNewWidth / lngOrigWidth, 3)

      'v3.90 - set old values to equal new values (used in GetStretchFactor)
      lngOldWidth = lngNewWidth
      lngOldHeight = lngNewHeight

      'next 2 lines help ensure no over enlargement of subform
      Me.subfrmControlTypes.Width = Me.Graph67.Width
      Me.subfrmControlTypes.Left = Me.Graph67.Left

      Application.Echo True

End Sub


The above code is called from the Form_Resize event:

Private Sub Form_Resize()

      MaintainAspectRatio

End Sub



Three 'helper' procedures from module modResizeForm are used in the code for one or both of the almost identical StretchShrink and MaintainAspectRatio procedures

i)   GetResizedWindow

Public Sub GetResizedWindow(frm As Access.Form)

'---------------------------------------------------------------------------------------
' Procedure :                   GetResizedWindow
' DateTime :                    28/11/2022
' Author :                         Colin Riddington
' Purpose :                       Routine stores the resized window dimensions - Currently only used in frmExample4
'---------------------------------------------------------------------------------------

On Error Resume Next

      ResizedWindow.Height = frm.WindowHeight
      ResizedWindow.Width = frm.WindowWidth

      lngNewWidth = ResizedWindow.Width
      lngNewHeight = ResizedWindow.Height

End Sub


ii)   GetPreventFormShrinkValue

Function GetPreventFormShrinkValue()
      GetPreventFormShrinkValue = Nz(DLookup("ItemValue", "tblSettings", "ItemName='PreventFormShrinking'"), "No")
End Function


iii)   GetStretchFactor

Public Function GetStretchFactor() As Single
'---------------------------------------------------------------------------------------
' Procedure :                     GetStretchFactor
' DateTime :                      28/11/2022
' Author :                           Colin Riddington
' Purpose :                         Routine determines the stretch/shrink factor in form frmExample4 - Not currently used elsewhere
'---------------------------------------------------------------------------------------

'Original idea was to get the smaller change in case form not resized evenly in each direction
'However this seems to work well enough - v3.90

      If lngNewWidth / lngOldWidth > 1 Then       'stretch
            GetStretchFactor = lngNewWidth / lngOldWidth       'use width
      Else       'shrink
            GetStretchFactor = lngNewHeight / lngOldHeight
      End If
End Function





5.   Refresh Display                                                                                                                               Return To Top

The latest version contains code to allow form resizing to be refreshed when moving to another monitor with a different size or resolution
Each form now includes a textbox giving the current resolution and form factor:

FormRefresh1
After moving to a different monitor (or changing the resolution/scaling in Windows settings), click the Refresh button to update the form

FormRefresh2
This is done by storing information about each connected monitor when the startup form is loaded

For example, this is my dual monitor setup in Windows settings:

DisplaySettings
That information is stored in the table tblMonitors:

MonitorInfo
The data includes the dimensions and position of each monitor with the primary monitor also identified
The CurrentMonitor field indicates the screen on which the Access window is currently located

That information is then displayed both on the form textbox and on the status bar
When the Refresh button is clicked, code is used to determine which monitor the current form is being displayed on and updates the CurrentMonitor field in the table.
In addition, it resizes the form and updates the form resolution information accordingly

The whole process is instantaneous

The code used to check the monitor info is:

Public Sub CheckMonitorInfo()

'---------------------------------------------------------------------------------------
' Procedure :                   CheckMonitorInfo
' DateTime :                    18/11/2022
' Author :                         Colin Riddington
' Purpose :                       Used to detect position, size and resolution of all connected monitors
'                                         Data stored in tblMonitors ; Called in form load of startup form before resizing
'---------------------------------------------------------------------------------------

      Dim bytNumOfMonitors As Byte

      CurrentDb.Execute "DELETE * FROM tblMonitors", dbFailOnError
      DoEvents
      bytNumOfMonitors = CountMonitorId
      For I = 1 To UBound(MonitorID)
            Call SaveMonitorInfo(MonitorID(I), bytNumOfMonitors)
      Next I

End Sub


This is called in the click event for the Refresh button:

Private Sub cmdRefresh_Click()

      'NEW v3.78

      Application.Echo False

      Dim strOldRes As String, strNewRes As String, strName As String

      strOldRes = Me.lblResolution.Caption
      strName = Me.Name

      'update
      CheckMonitorInfo
      SetStatusBarText
      GetCurrentResolution

      'display screen resolution
      strNewRes = GetResolution() & " : " & GetScreenShape() & " (" & GetFormFactor() & ")"
      Me.lblResolution.Caption = strNewRes

      If strNewRes <> strOldRes Then
            UnReSizeForm Me
            DoCmd.Close acForm, strName
            DoCmd.OpenForm strName
            SetStatusBarText
      End If

      Application.Echo True

End Sub


The above code uses three 'helper functions' to refresh the display resolution
The UnReSizeForm code is discussed in detail in the third part of this article

The other helper functions used when refreshing the display resolution are as follows

i)   SetStatusBarText (in the module modResizeForm)

Public Sub SetStatusBarText() 'v3.77

'---------------------------------------------------------------------------------------
' Procedure :                     SetStatusBarText
' DateTime :                      12/11/2022
' Author :                           Colin Riddington
' Purpose :                         Display resolution of current monitor on status bar
'---------------------------------------------------------------------------------------

      Dim strMsg As String, strR As String, strP As String
      I = Nz(DLookup("MonitorID", "tblMonitors", "CurrentMonitor=True"), 0)
      strP = IIf(IsMainMonitor, "(Primary Monitor)", "(Secondary Monitor)")
      strR = "Resolution " & Nz(DLookup("HRes", "tblMonitors", "MonitorID = " & I), 0) & " x " & Nz(DLookup("VRes", "tblMonitors", "MonitorID = " & I), 0)
      If I <> 0 Then strMsg = "Display " & I & " " & strP & " : " & strR
      StatusBar strMsg
End Sub



ii)   GetCurrentResolution (also in the module modResizeForm)

Public Function GetCurrentResolution() 'v3.77

'---------------------------------------------------------------------------------------
' Procedure :                     GetCurrentResolution
' DateTime :                     18/11/2022
' Author :                           Colin Riddington
' Purpose :                         Function returns the height, width and dpi for current monitor.

'                                          New code v3.78 to replace older GetScreenResolution (now removed)
'                                          Detects resolution of current monitor
'                                          Data obtained using CheckMonitorInfo & retrieved from tblMonitors
'---------------------------------------------------------------------------------------

      Dim I As Integer, strResolution As String, sngFormFactor As Single, lngH As Long, lngV As Long

      With NewRes
            I = Nz(DLookup("MonitorID", "tblMonitors", "CurrentMonitor=True"), 0)

            lngH = Nz(DLookup("HRes", "tblMonitors", "MonitorID = " & I), 0)
            lngV = Nz(DLookup("VRes", "tblMonitors", "MonitorID = " & I), 0)

            strResolution = lngH & " * " & lngV

            Select Case (lngH / lngV)
                  Case 4 / 3
                        FormFactor = "4:3"
                  Case 5 / 4
                        FormFactor = "5:4"
                  Case Is > 4 / 3
                        FormFactor = "Widescreen"
                  Case Is < 1
                        FormFactor = "Portrait"
                  Case Else 'v3.80
                        FormFactor = "Other" 'e.g. split screen
            End Select

      End With

End Function





6.   Image Scaling on Command Buttons                                                                           Return To Top

As is the case with checkboxes and option buttons, command button images cannot be resized by standard methods
For checkboxes and option groups, the work-round is to use textbox controls and e.g. a Wingdings font to simulate resizing

For example, form frmExample2 includes both real and simulated (dummy) checkboxes and option groups which are identically sized in design view

ButtonImageDesign
In form view, the dummy controls are resized (as for any textbox)

ButtonImageForm

The same form also contains two buttons with identical images. The top button image is not resized in form view so is proportionately smaller than the button caption text after form resizing.

However, the lower button image has been 'resized' in proportion.
More accurately, that appears to be the case but in reality it has been replaced by a different, larger version of the same image depending on the scaling factor

Change back to form design view and click on Insert Image in the form design ribbon. The Image Gallery opens

ImageGallery
Several versions of the same shared image have been stored to handle any resolution from 800x600 to e.g. 3840x2160
An appropriately sized image is used depending on the current resolution

Private Sub ResetButtonImage()

'NEW v3.82 28/11/2022
'replaces image on button cmdBtnImage2 according to scaling factor (GetCurrentFactor)
'error 2467 occurs as image not loaded - ignore it!
On Error Resume Next

      Select Case GetCurrentFactor
      'only tested up to 1.8 - may need adjustment for higher values!
      Case Is < 1
            Me.cmdBtnImage2.Picture = "ColourWheel16"
      Case Is <= 1.33
            Me.cmdBtnImage2.Picture = "ColourWheel20"
      Case Is <= 1.6
            Me.cmdBtnImage2.Picture = "ColourWheel24"
      Case Is <= 2
            Me.cmdBtnImage2.Picture = "ColourWheel28"
      Case Is <= 2.4
            Me.cmdBtnImage2.Picture = "ColourWheel32"
      Case Is <= 3
            Me.cmdBtnImage2.Picture = "ColourWheel36"
      Case Is <= 3.5
            Me.cmdBtnImage2.Picture = "ColourWheel40"
      Case Else
            Me.cmdBtnImage2.Picture = "ColourWheel44"

      End Select

End Sub


The method works well. However, the obvious downside is the need to store several versions of the images used on command buttons.
Of course, this method cannot be used with the built-in Access button images which are one size only.



7.   Additional Template Forms                                                                                                 Return To Top

The example app includes five template forms to help you get started with automatic form resizing in your own projects

TemplateForm
Each template has been designed so it will approximately fill the screen if displayed at its design 'base resolution'
At higher resolutions, it will be 'scaled up' proportionately

NOTE:
You MAY need to do some MINOR 'tweaking' of the dimensions of the various template forms for your own setups

The first two forms were designed at low resolutions with a 4:3 form factor. The 800x600 template was used for all forms in this example app

The next 2 forms are designed for two different widescreen form factors (16:9 and 16:10)
The final template is for a portrait display (9:16 form factor)

IMPORTANT: For any template form to work correctly, you MUST set the DESIGN_HORZRES and DESIGN_VERTRES constants in modResizeForm to match the template design values

Template4Design



8.   Using AFR in your own applications                                                                           Return To Top

The automatic form resizing (AFR) code has been in regular use with frequent updates over a 20 year period.
It is in widespread use in many databases around the world including many commercial applications.

Although much of the code is very complex, you do NOT need to understand it fully in order to use it in your own applications.
At its simplest, you just need to add one line of code to the Form_Load event of any forms to be resized.

        ResizeForm Me

However, in order to use the code successfully it is STRONGLY recommended that you first study all the features in the example app and then apply these to a new blank database. Using AFR on an existing database not designed for its use may sometimes lead to less than satisfactory outcomes.

I recommend you use one of the template forms as a starting point making sure that you set the DESIGN_HORZRES and DESIGN_VERTRES constants in module modResizForm to match the template used. Remember that AFR works best when scaled up. It is less successful when forms are scaled down.

IMPORTANT:
The new features added to the latest version require the use of Access 2010 or later.
To make use of all the latest features, you will, at a minimum, need to import the following items into your own apps:
a)   Module modResizeForm - this is ESSENTIAL
b)   Module modFormInfo - used to centre forms on the screen
c)   Modules modMonitorInfo and modMetrics together with table tblMonitors - used from version 3.90 onwards to refresh monitor resolution
d)   Table tblUsers - if you want to store user preferences for the Zoom form code

However, if you don't need any of the latest features, fewer items need to be imported into your own apps.

Similarly if you have Access 2007 or earlier, you can still use automatic form resizing provided you use the supplied code designed for your version of Access.

To make things simpler, I have also supplied four separate versions of the module code required for different versions of Access (see below)

Only the FOUR standard VBA references used with ACCDB files are needed for this code.

References

The example app also uses an additional reference Microsoft Visual Basic Applications Extensibility 5.3 (highlighted above) together with code in module modVBE to allow easy access to the code. Neither of these are required to use AFR with your own apps

Please also review my recent article Making Automatic Form Resizing Work For You. The article was written to accompany my presentation to the online Access DevCon conference on 28 April 2023

Finally, all the code is provided completely FREE as a public service. It may be used in any Access applications including commercial apps PROVIDING that all author and copyright information is retained unchanged for each of the code modules used.

If you do find AFR useful, I would appreciate links back to this site. Donations to support the costs in running this site would be very welcome

If you wish to contact me with any questions, please use the feedback form below.
I reserve the right to charge for my time if you need detailed support with your applications



9.   Downloads                                                                                                                                       Return To Top

Click the links to download various items related to this topic:

1.   The example application referenced in this article - ResizeFormExample_v3.94.zip     (ACCDB file zipped)

2.   The previous version of the example application if you don't need any of the new features - ResizeFormExample_v3.75.zip     (ACCDB file zipped)

3.   The original auto form resize utility by Jamie Czernik from 2003 - afr2003jc.zip     (MDB zipped)

4.   The module code you will need to import to use AFR in your own applications depending on Access version and features required:
      a)   Full version with all features used in v3.90 onwards - for Access 2010 or later - AFRModuleCode2010+_v393.zip     (ACCDB zipped)

      b)   Simplified version without the latest features in v3.90 - for Access 2010 or later - AFRModuleCode2010+_v375.zip     (ACCDB zipped)

      c)   ACCDB version for Access 2007 (code for navigation forms/controls also removed) - AFRModuleCode2007.zip     (ACCDB zipped)

      d)   MDB version for Access 2000-2003 (code for navigation pane also removed) - AFRModuleCode2000.zip     (MDB zipped)



10.   YouTube Videos                                                                                                                         Return To Top

1.   I ran a session on Automatic Form Resizing for the US Lunchtime Access User Group on 28 Sept 2021.
      The video is available on You Tube at: https://youtu.be/-mgIvCosYtU or you can click on the video below.

       

      Many thanks to Crystal Long (MVP) for the considerable time she spent expertly editing the video recording of this session.

2.   Since then, I have done updated versions of the same presentation for the UK Access User Group on 8 Feb 2022 and again for the Denver Area Access
      User Group on 17 Feb 2022. The updated presentation also included the use of AFR with datasheet forms and navigation forms

      The video of the Denver Area AUG session is also available on You Tube at: https://youtu.be/dzk9rM5A9zU or you can click on the video below.

       

3.   I was also invited to give a new presentation on 28 April 2023 to the Access DevCon online conference organised by Karl Donaubauer (MVP).
      Access DevCon is an annual two day event and is by far the largest English language Access conference.
      This year there were around 160 participants from 22 countries spanning 5 continents.

      My presentation was called Making Automatic Form Resizing Work For You
      For more details of the session, see my related article elsewhere on this website.


      The session was extremely popular with a lot of very positive feedback

      The video of my DevCon session was made available to all who attended the conference but is not available publicly.
      However, I have since created a new video with similar content for my own Isladogs on Access YouTube channel

      The video is available at: https://youtu.be/Mlon99a2f1I or you can click on the video below.

       



11.   Summary / Review                                                                                                                Return To Top

The first part of this article compared the use of automatic form resizing (AFR) with the built in layout guides and anchoring.

The second part discussed the many features available in the example app as well as potential issues you may experience and possible solutions for each.

The third part of the article will explain how the resizing code works and how to use it effectively in your own applications.

This article explained the latest features added in version 3.90 of the example application



12.   Feedback                                                                                                                                       Return To Top

I would be grateful for any feedback on this article including details of any errors or omissions

If you have any comments or questions, please contact me using the feedback form below or send me an email



Colin Riddington               Mendip Data Systems               Last Updated 9 Aug 2023



Return to Access Articles Page 4 of 4 1 2 3 4 Return To Top