Version 3.94 First Published 10 Mar 2019 Last Updated 26 Apr 2023 Difficulty level: Moderate
Section Links (this page):
Using the example app
Add Zoom feature
Datasheets
Split Forms
Navigation Forms
Report As Subform
Issues/Solutions
Downloads
YouTube Videos
Summary/Review
Feedback
In the first part of this article, I discussed the benefits of automatic form resizing as well as an alternative approach based on layout guides and anchoring.
In this section, I will demonstrate how the resizing code can be modified to include an adjustable zoom feature in your forms.
I will then show how to succesfully use form resizing with datasheets, split forms and navigation forms.
Finally, this will cover some potential issues together with possible solutions for each of these.
In the third part of the article, I will explain how the resizing code works and provide tips for using the code most effectively in your own applications.
The fourth part of the article will explain all the latest features added in version 3.90
1. Using the example application
Return To Top
The attached example app has been designed for use with BOTH overlapping windows AND tabbed documents.
The app opens to a main menu form giving access to almost all its features:
The first two menu items relate to the use of layout guides, grouping and anchoring as described in the first part of this article
The next four items are variations of the same example form shown below:
This is the same form showing its original size in design view.
The form includes a wide range of control types used in Access forms and also shows the current screen resolution and form factor (shape).
The differences between the four versions of this form are as follows
a) frmExample1 - opens in design view and automatically resized on form load
b) frmExample2 - not resized on form load BUT can be resized by clicking a button in the form header.
This version of the form also includes a button with 'scalable' images
c) frmExample3 - automatically resized on form load PLUS a ZOOM feature (see below)
d) frmExample4 - this form is designed to be stretched / shrunk 'on the fly' by dragging the form border with the mouse
This is a new feature and will be explained in the fourth part of this article
Each form also includes a subform which summarises the properties available for each control type
The example application also contains these forms:
e) A zoom form frmZoom. A popup version frmPopupZoom is also included.
f) A split form frmSplit. An emulated version frmSplitEmulated is also included.
g) A navigation form frmNavigation together with several subforms. An emulated version frmNavigationEmulated is also included.
h) A datasheet form frmExampleDatasheet
i) A form with a report as subform frmReportSubform
j) A helper form frmFormUnresizer - use to fix issues if any forms become 'over-enlarged'
k) five template forms created at base resolutions 800x600, 1024x768, 1366x768, 1680x1050 and 768x1366.
These can be used to assist with setting a default form size at different 'design resolutions'
l) A form frmAFRIssues outlining possible issues you may experience together with how best to deal with each of these.
Each of these forms is described in more detail below
NOTE: The buttons marked next to each menu item will take you directly to the code for that item.
2. Adding a Zoom feature
Return To Top
Unlike Word and Excel, there is no built in zoom control available in Access. For those with less than perfect eyesight this can be an issue.
I have now added two additional forms to the example application to show how Access forms can be zoomed in/out using a modified version of the resizing code.
The new code in Form3 allows further adjustment of the form from 75% to 125% of the default size for your screen & resolution
Use the combo or slider control to adjust the scale in 5% intervals according to personal preference.
The form dimensions and all control sizes/contents are adjusted accordingly
For example, with zoom = 110%
Enlarging still further, with zoom = 125%
Or the form can be shrunk e.g. with zoom reduced to 90%
The zoom level can be reset to normal by clicking the 100% button
Alternatively, the preferred zoom level can be saved for future use by ticking the checkbox. If so, the form will automatically open at that zoom level in future (this is an individual user setting))
NOTE:
1. Increasing the zoom may cause part of the form to move 'off screen' as in the 125% screenshot above. Scrollbars appear automatically when this happens
2. The slider is an ActiveX control. This will be hidden and 'inactive' if you have disabled ActiveX controls in Access Options Trust Center
At some point in the future, I will replace this with a non-ActiveX slider control
A very simple popup form is also available which includes a zoom feature with a range 50% to 150%
Once again, click the 100% button to reset the form to its default size and scale
3. Datasheet Resize & Zoom
Return To Top
Datasheets are NOT affected by standard AFR code. Until recently, it was necessary to use a resizable continuous form to emulate a datasheet.
However, it is possible to both resize and zoom datasheet forms/subforms by looping through each control in turn.
In each case, the control width and font height is then resized. This is almost instantaneous.
Datasheet form before resizing:
The same datasheet form after resizing:
This approach also means that the zoom code explained above can be utilised to allow further levels of adjustment as required.
The same datasheet form with no resizing and 130% zoom:
The same datasheet form after resizing and scaled down with 90% zoom:
It is also possible to resize a standalone datasheet form:
4. Split Forms
Return To Top
Split forms need a slightly modified version of the AFR code.
The parent form must be referenced in the resizing code. This is explained fully in part 3 of this article.
In addition, it is ESSENTIAL that the splitter bar is removed to prevent the resized form being saved on close.
To remove the splitter bar, open the form in design view and go the the Property Sheet. Click the Format tab and set the Split form Splitter Bar property to No
NOTE: The split form resizes and works perfectly with the splitter bar removed
Alternatively, you can use an emulated split form which has many advantages over a standard Access split form.
The emulated split form can be treated like any standard form when using AFR.
For further details on the emulated split form, see
Emulated Split Form
5. Navigation Forms
Return To Top
Navigation forms were a new feature introduced with Access 2010. These also need a slightly modified version of the AFR code
In this case, it is ESSENTIAL to remove layout guides from the navigation form BEFORE running AFR code to prevent the resized form being saved on close.
NOTE: The navigation form resizes and works perfectly with the layout guides removed. All subform types work well - single/continuous/datasheet
For example, here the subform shows a single record
Or, in this case, the subform is a datasheet
Alternatively, create your own emulated navigation form with similar functionality. See form frmNavigationEmulated in the example app supplied with this article.
6. Report As Subform
Return To Top
Code has also been included to handle resizing in situations where reports are used as a subform.
I added this feature at the request of a fellow Access developer, Chris Arnold, who sometimes uses a report as a subform e.g. to display a list of comments making use of the can grow/shrink report control property to display each comment in full.
Form with report subform in design view:
Form view with no AFR:
Form view with AFR:
Using AFR with a report required a modified procedure called ReportResize. Add this code to the Report_Load event:
Private Sub Report_Load()
ReportResize Me
End Sub
NOTE:
You may wish to disable AFR when it is used as a standard report. To do so, modify the above code as follows:
Private Sub Report_Load()
'resize when opened as subform
If IsSubform Then ReportResize Me
End Sub
The IsSubform code is discussed in the third part of this article
7. Possible Issues and Solutions Return To Top
The automatic form resizing code is most successful with maximised forms.
If using non-maximised forms, you should check the effect of resizing at different resolutions.
If necessary, adapt the form size/shape for best results.
NOTE: Built in Access forms such as message boxes, input boxes and error messages are NOT resized.
Popup forms appear on top of the application window and therefore may not fit fully on the screen if resized.
It may be necessary to exclude certain popup forms from being resized.
The original version of this code by Jamie Czernik from 2003 did not work well with the tabbed documents display option added with Access 2007.
For example, certain controls such as subforms and tab controls were shifted off screen causing the whole form to be shifted both horizontally and vertically
Tab control in the correct position
Tab control headers shifted off screen
NOTE:
The display issue shown above MAY not occur with popup forms containing tab controls.
It NEVER occurs when using overlapping windows
Using both scrollbars, the form can be correctly 'realigned'.
Similarly, if the navigation pane and / or ribbon is maximised then minimised again.
However even if the form was saved after doing so, it would again be shifted the next time it is opened
The original partly successful solution was to apply a tag value = 1 to the form.
Doing so triggered the implementation of an 'exemption code' in the ResizeForm procedure.
However, the current version of the resizing code supplied with this article works equally well with both overlapping windows and tabbed documents.
The code now detects the display option by checking the UseMDIMode property. This is is 1 for overlapping windows and 0 for tabbed documents
After resizing, the revised code maximizes then minimizes the navigation pane if tabbed documents are in use
NOTE:
You will also need to import module modNavPaneTaskbar to your own apps if using tabbed documents display.
'====================================================
'Modification by Colin Riddington 13/03/2019
'UseMDIMode property =1 (overlapping windows) or = 0 (tabbed documents)
'next section fixes display issue for users of tabbed documents (MDIMode=0)
If CurrentDb.Properties("UseMDIMode") = 0 Then
MaximizeNavigationPane
DoEvents
MinimizeNavigationPane
End If
'====================================================
This code is an integral part of the ResizeForm procedure. It is sufficient to reset the form positioning correctly.
It has been tested successfully with document tabs both visible and hidden. No fix is required for overlapping windows
Nevertheless, it is STRONGLY RECOMMENDED that forms are both designed and displayed using the same display option.
UPDATE 24/01/2020
Many thanks to Utter Access member ollyhutsy for informing me of an issue for users running runtime versions of Access.
Code to maximise/minimize the navigation pane fails in runtime versions. However the original code setting the form tag value = 1 still works
So there are two possible solutions for runtime users:
1. Using tabbed document display - set the form tag value = 1
2. Change to overlapping windows display(RECOMMENDED) - that works perfectly without needing any code tweak.
Button captions may not quite fit when forms are resized for small screens such as a tablet
To prevent this issue, ensure the button width is slightly greater than the length of the caption.
Option groups can occasionally become 'over enlarged' switching between form view and design view during the development process.
This can cause the entire form to expand far more than required
The cause of the problem can be seen in design view. The frame borders (ORANGE lines) have become over enlarged
This causes the entire form to scale up more than necessary
Luckily this is an extremely rare event if the form is well designed.
However, it can be an issue if the option group is near the bottom or right edge of the form.
To prevent this issue, place option groups as far left and close to the top of the form section as possible.
The following quote is taken from the help file supplied with the original code by Jamie Czernik:
Tab controls and option groups are difficult to resize as Access tries to keep the child controls within the frame while the child controls are being moved and resized. This can lead to distortion if the controls are too close to either the top/bottom or left/right of the form being resized.
If your forms tend to be scaled up to higher resolutions, then try to keep tab controls and option groups as far left and near to top of the form section as possible.
The reverse is true if forms tend to be scaled down to lower resolutions so in this situation try to keep them as far right and near to the bottom as possible.
If a form does become 'over enlarged' it can be fixed using one of these methods:
a) Open the FixFormSize procedure in modResizeForm and enter the form name where indicated
Run the procedure whilst the form is open in design view
Sub FixFormSize()
'---------------------------------------------------------------------------------------
' Procedure : FixFormSize
' DateTime : 23/09/2015
' Authors : Colin Riddington
' Purpose : Can be used to restore size of forms which have become 'over-sized'
' Or just use frmFormUnresizer (no need to open in design view first)
'---------------------------------------------------------------------------------------
On Error GoTo Err_Handler
'Make sure the form you need to un-resize is open in design view before running this process
Dim frm As Access.Form
Set frm = Forms!frmColoursCont 'modify form name as necessary
UnReSizeForm frm
'ReSizeForm frm
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " in FixFormSize procedure : " & Err.Description
Resume Exit_Handler
End Sub
This calls a separate procedure UnresizeForm:
Public Sub UnReSizeForm(frm As Access.Form)
'---------------------------------------------------------------------------------------
' Procedure : UnReSizeForm
' DateTime : 27/01/2003 with many updates
' Authors : Jamie Czernik / Colin Riddington
' Purpose : Used to unresize forms where needed e.g. before changing zoom
' Modifications: Jeff Blumson / Colin Riddington
'Significantly modified by Colin Riddington 2006-2022
'---------------------------------------------------------------------------------------
Dim rectWindow As tRect
Dim lngWidth As Long
Dim lngHeight As Long
Dim sngFactor As Single
On Error Resume Next
'NEW CODE v3.78
GetCurrentResolution
sngFactor = 1 / GetCurrentFactor 'scaling factor
'lngH = horizontal resolution ; DESIGN_HORZRES = base resolution set in declarations section
If lngH <> DESIGN_HORZRES Then 'resize necessary
'END OF NEW CODE
Resize sngFactor, frm 'local procedure
If IsZoomed(frm.hWnd) = 0 Then 'Don't change window settings for maximized form.
Access.DoCmd.RunCommand acCmdAppMaximize 'Max Access Window
Call GetWindowRect(frm.hWnd, rectWindow)
With rectWindow
lngWidth = .Right - .Left
lngHeight = .Bottom - .Top
End With
'NEW CODE v3.78
Call MoveWindow(frm.hWnd, ((lngH - _
(sngFactor * lngWidth)) / 2) - GetLeftOffset, _
((lngV - (sngFactor * lngHeight)) / 2) - GetTopOffset, _
lngWidth * sngFactor, lngHeight * sngFactor, 1)
End If
End If
End Sub
b) Use the form frmFormUnresizer supplied with the example app. Select the over enlarged form from the list then click Shrink Form
Whichever method you use, the form will be scaled down and controls shifted back into the correct positions.
However, you will still need to restore the original size of the option group controls that were over enlarged.
Once you have done so, you then need to reduce the height and width of the form yourself.
NOTE:
It can be useful to create a hidden box control with a transparent fill in each section which is the correct height and width for that section
This makes it very easy to restore the form to the original size
Finally save and close the form.
NOTE:
If you make a mistake, the process can if necessary be reversed by clicking the Enlarge Form button.
You will need to import the module modResizeForm and (optionally) form frmFormUnresizer.
If you are using tabbed document display, you should also import the module modNavPaneTaskbar.
Then follow the instructions as above adding ResizeForm Me to the Form_Load event of any forms to be resized.
Click the Possible AFR Issues button on the main form for more information about possible issues and solutions.
8. 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. An earlier 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)
9. 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.
10. 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.
This article discussed the many features available in the example app including:
a) an optional zoom feature which can be very useful for those with less than perfect eyesight.
b) the use of AFR with 'special' types of form: (datasheet, split and navigation) together with the use of a report subform
c) some potential issues you may experience and possible solutions for each of these.
The third part of the article will explain how the resizing code works and how to use it in your own applications.
The fourth part of the article will explain all the latest features added in version 3.90
11. 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 26 Apr 2023
Return to Access Articles
Page 2 of 4
1
2
3
4
Return To Top
|
|
|