Version 2.5 Approx 1.9 MB (zipped) First Published 28 Feb 2024 Last Updated 8 Nov 2026
UPDATED 2026-11-08: Version 2.5 removes dependency on .NET 3.5 being installed. No changes to functionality.
Section Links (this page):
Introduction
Download
Using the Example App
How Does the Example App Work?
Summary / Conclusions
Version History
Video
More Continuous Forms Examples
Feedback
Introduction
Return To Top
This is the second part of the tenth article in my series showing how functionality can be added to continuous forms.
In the first part of this article, I demonstrated how users can move, resize, hide and restore columns in a continuous form at runtime.
However, any changes made were not saved when the form is closed.
Whilst this can often be an advantage, end users may wish to keep the modified layout for future use
This article demonstrates how this can be done. Once again, no APIs are used for the basic functionality of this application.
The app does use APIs as part of the optional dim background effect used with subsidiary forms frmAppTips and frmSettings.
No additional references are required if late binding is used.
Download Return To Top
Click to download: MoveResizeColumns_v2.5 ACCDB file Approx 1.8 MB (zipped)
Using the Example App Return To Top
Once again, the app opens to the startup form:
Click a button to open either the datasheet or the main (continuous) form.
The design of the main form is identical to the earlier version in the first part of this article
In this version, the Settings form has an additional item, Save New Layout:
If the form layout has been changed by moving or resizing columns, it is now possible to save that layout for future use.
When the Close button is clicked, different things wil happen depending on your settings:
a) Show Layout Warnings
If Show Layout Warnings is set to Yes, you will be asked whether or not to save the modified layout.
If you click Yes, the form will reopen with the modified layout.
If you click No, the form will reopen with the original default layout.
If Show Layout Warnings is set to No, it will automatically do whatever the Save New Layout option setting is.
b) Save New Layout
The default action when no warning messages are shown.
How Does the Example App Work? Return To Top
Allowing users to save or restore the original layout without making any changes in design view required a different approach.
When the main form is first opened, it uses a VBA dictionary to store column names, positions and widths.
A collection stores any changes made to the positions and visiblity of the columns.
If using early binding, you need an additional reference, Microsoft Scripting Runtime. However, I recommend using late binding in which case no additional reference is required.
The type of binding used and related code is defined in the declarations section of modManageColumns
A listbox shows the visible columns in order by left position. A combo box shows a list of all hidden columns so these can be retrieved.
This allows the columns to be moved and resized but also the original layout can be restored at any time during a session.
It also means the original layout can be retrieved if required when the form is next opened.
The code is fast and very efficient in achieving these aims.
In order to save a modified layout, the layout data is stored in a table tblControlData. This is populated when the form is first opened.
If layout changes are made and the end user chooses to save those changes on form close, then the final 2 columns are populated with the NewLeft and NewWidth values for each control.
In this case, 7 columns have been hidden, PupilID moved two places to the right and Forename made narrower.
As a result, the position of all controls has changed. Controls / labels with NewWidth = 1 (twip) are those hidden by clicking on the column header.
When the form is reopened, it checks the status of that table and uses the NewLeft and NewWidth values to accurately position all controls & labels according to the 'saved layout'.
NOTE: The actual form design has NOT been changed. In design view, all controls and labels still have their original size and position.
If the user chooses not to save the modified layout on close, table tblControlData is emptied
The table will then be re-populated when the form is re-opened, reverting to the default layout.
Compared to the previous versions described in the first article of this series, code changes are only required in the following five form events for this to happen.
1. Form_Open
Previously this event was used to set up the VBA dictionary based on the default control layout
In this version, the dictionary code first looks for existing modified data from the NewLeft and NewWidth fields in tblControlData where these have been populated.
Where no data exists it uses the default layout.
It also checks if tblControlData is empty and, if so, populates it with the names, postions & sizes of the controls & header labels (as in the dictionary)
Finally it uses a collection to store the label captions in order of position for use as the listbox row source.
Private SubForm_Open(CancelAs Integer)'Uses Dictionary to store column names, positions & widths'Uses Collection to store label order (native VBA, eliminating .NET 3.5 dependency)'Xevi Batlle'Also populates table tblControlData for future use if modified layout saved'Table holds control name, control type, left, width & tag properties'Colin RiddingtonOn Error Resume NextSetDb=CurrentDbSetfrm=MeCommandBars("Form View Popup").Enabled=TrueCommandBars("Form View Control").Enabled=True'count records in tblControlDataI=DCount("*", "tblControlData", "FormName = '"&Me.Name& "'")IfNz(Me.OpenArgs, "") = ""ThenApplication.EchoFalse'set up dictionary based on default control layout or modified layout from tblControlData where it existsSetdict=CreateObject("Scripting.Dictionary")For EachctlIn Me.ControlsIf(ctl.ControlType=acLabelOrctl.ControlType=acTextBox_Orctl.ControlType=acCheckBoxOrctl.ControlType=acComboBox)Andctl.Tag<> "A"ThenIfGetSaveNewLayoutStatus= "Yes"Thenctl.Properties("Left") =DLookup("NewLeft", "tblControlData", _"FormName = '"&Me.Name& "' And ControlName = '"&ctl.Name& "'")ctl.Properties("Width") =DLookup("NewWidth", "tblControlData", _"FormName = '"&Me.Name& "' And ControlName = '"&ctl.Name& "'")End Ifdict.Add ctl.Name& "_Left",ctl.Properties("Left").Valuedict.Add ctl.Name& "_Width",ctl.Properties("Width").Value'v2.0 - if tblControlData is empty, add control names to table for future useIfI= 0ThenstrSQL= "INSERT INTO tblControlData(FormName, ControlName, ControlType)"& _" VALUES('"&Me.Name& "', '"&ctl.Name& "', "&ctl.ControlType& ");"' Debug.Print strSQLDb.Execute strSQL,dbFailOnError'update control position infostrSQL= "UPDATE tblControlData"& _" SET ControlTypeName = Switch(ControlType = 100, 'acLabel', ControlType=109, 'acTextbox',"& _" ControlType = 106, 'acCheckBox', ControlType=111, 'acComboBox'),"& _" ControlLeft = "&ctl.Properties("Left") & ", "& _" ControlWidth= "&ctl.Properties("Width") & ", _"& _" Tag = '"&ctl.Tag& "'"& _" WHERE FormName = '"&Me.Name& "'"& _" AND ControlName = '"&ctl.Name& "' AND ControlType = "&ctl.ControlType& ";"Db.Execute strSQL,dbFailOnErrorEnd IfEnd IfNext'Store labels in a Collection to order them by Left Position (eliminating .NET 3.5 dependency)SetcolColumns=NewCollectionDimcurLeftAs LongFor EachctlIn Me.ControlsIfctl.ControlType=acLabelAndctl.Tag= "DetachedLabel"ThencurLeft=ctl.Properties("Left")AddSortedColumn colColumns,Format$(curLeft, "000000") &ctl.NameEnd IfNextSetoCollection=colColumnsApplication.EchoTrueEnd IfEnd Sub
2. Form_Load
This event counts the moved/resized columns and if either are greater than zero, it enables the Reset button.
Clicking the Reset button should reset the form to the default layout.
Private SubForm_Load()'Colin RiddingtonApplication.EchoFalseDoCmd.MaximizeMinimizeNavigationPaneIfGetHideStartFormStatus= "Yes"ThencmdClose.Caption= "Quit"Me.cmdClearFilter.Enabled=FalseMe.cmdClearSort.Enabled=FalseMe.cmdReset.Enabled=FalseMe.cboHiddenColumns.Visible=FalseMe.txtCurrentRecord.Visible=FalseMe.cmdClose.SetFocusMe.OrderByOn=FalseMe.FilterOn=FalseSetListboxColumnOrderMeDoEvents'v2.0IfCountHiddenColumns> 0Then Me.cboHiddenColumns.Visible=TrueIfCountMovedColumns> 0OrCountChangedColumnWidths> 0Or Me.cboHiddenColumns.Visible=True ThencmdReset.Enabled=TrueEnd IfMe.lblFormInfo.Caption= "Select a column by double clicking the control or using the listbox. "& _"Use the left/right buttons to reposition the selected column on the form. "& _"Resize columns (wider/narrower) using the +/- buttons. You can also sort && filter columns."&vbCrLf& _"Double click any column header to hide that column. "& _"All other columns to the right are automatically shifted leaving no gaps. "& _"Click the BLUE ? button to view application tips. Click the Tools button to edit settings."GetFormFooterCaptionsMeApplication.EchoTrueEnd Sub
3. cboHiddenColumns_AfterUpdate
This restores the control sizes and positions when hidden columns are selected in the combo box
In this version, the data used to manage the restore process is taken from table tblControlData
The combo box row source is also updated removing the restored column
Private SubForm_Load()'Colin RiddingtonApplication.EchoFalseDoCmd.MaximizeMinimizeNavigationPaneIfGetHideStartFormStatus= "Yes"ThencmdClose.Caption= "Quit"Me.Properties("Zoom") = 97Me.cmdClearFilter.Enabled=FalseMe.cmdClearSort.Enabled=FalseMe.cmdReset.Enabled=FalseMe.cboHiddenColumns.Visible=FalseMe.txtCurrentRecord.Visible=FalseMe.cmdClose.SetFocusMe.OrderByOn=FalseMe.FilterOn=FalseSetListboxColumnOrderMeDoEvents'v2.0IfCountHiddenColumns> 0Then Me.cboHiddenColumns.Visible=TrueIfCountMovedColumns> 0OrCountChangedColumnWidths> 0Or Me.cboHiddenColumns.Visible=True ThencmdReset.Enabled=TrueEnd IfMe.lblFormInfo.Caption= "Select a column by double clicking the control or using the listbox. "& _"Use the left/right buttons to reposition the selected column on the form. "& _"Resize columns (wider/narrower) using the +/- buttons. You can also sort && filter columns."&vbCrLf& _"Double click any column header to hide that column. "& _"All other columns to the right are automatically shifted leaving no gaps. "& _"Click the BLUE ? button to view application tips. Click the Tools button to edit settings."GetFormFooterCaptionsMeApplication.EchoTrueEnd Sub
4. cmdReset_Click
This event restores all position and size values to the original default values before modification
It then clears table tblControlData to ensure it is repopulated with default data when the form is next opened.
Private SubcmdReset_Click()'Colin RiddingtonApplication.EchoFalse'count records in tblControlDataI=DCount("*", "tblControlData", "FormName = '"&Me.Name& "'")'restore all controls using dictionary valuesFor EachctlIn Me.ControlsIf(ctl.ControlType=acLabelOrctl.ControlType=acTextBox_Orctl.ControlType=acCheckBoxOrctl.ControlType=acComboBox)Andctl.Tag<> "A"ThenIfI> 0Then'v2.2 - use original values from tblControlData before any modifications madectl.Properties("Left") =DLookup("ControlLeft", "tblControlData", _"FormName = '"&Me.Name& "' And ControlName = '"&ctl.Name& "'")ctl.Properties("Width") =DLookup("ControlWidth", "tblControlData", _"FormName = '"&Me.Name& "' And ControlName = '"&ctl.Name& "'")Else'v1.9 - use dictionaryctl.Properties("Left") =dict(ctl.Name& "_Left")ctl.Properties("Width") =dict(ctl.Name& "_Width")End IfEnd IfNext' clear table to remove saved layoutDb.Execute"DELETE * FROM tblControlData WHERE FormName = '"&Me.Name& "';",dbFailOnError'v2.1 - restart form to restore default layoutDoCmd.Close acForm,Me.NameDoCmd.OpenForm"frmStudentAttendanceCont"Application.EchoTrueEnd Sub
5. cmdClose_Click
This event controls what happens when the form is closed depending on the application settings and user choice
Private SubcmdClose_Click()'Colin RiddingtonOn Error GoToErr_HandlerIf Me.cmdReset.Enabled=True ThenIfGetShowLayoutWarningsStatus= "Yes"Then'ask whether to save layoutIfFormattedMsgBox("The form layout has been altered"& _"@Do you want to save this new layout for future use? @", _vbQuestion+vbYesNo+vbDefaultButton2, "Save Layout?") =vbYesThenCurrentDb.Execute"UPDATE tblSettings SET ItemValue = 'Yes'"& _" WHERE ItemName='SaveNewLayout';",dbFailOnErrorFor EachctlIn Me.ControlsIfctl.ControlType=acLabelOrctl.ControlType=acTextBox_Orctl.ControlType=acCheckBoxOrctl.ControlType=acComboBoxThen'update control position infostrSQL= "UPDATE tblControlData"& _" SET NewLeft = "&ctl.Properties("Left") & ", NewWidth= "&ctl.Properties("Width") & ""& _" WHERE FormName = '"&Me.Name& "'"& _" AND ControlName = '"&ctl.Name& "' AND ControlType = "&ctl.ControlType& ";"Db.Execute strSQL,dbFailOnErrorEnd IfNextElse'clear table to remove saved layoutDb.Execute"DELETE * FROM tblControlData WHERE FormName = '"&Me.Name& "';",dbFailOnErrorEnd IfElseIfGetSaveNewLayoutStatus= "Yes"Then'save layout without askingCurrentDb.Execute"UPDATE tblSettings SET ItemValue = 'Yes'"& _" WHERE ItemName='SaveNewLayout';",dbFailOnErrorFor EachctlIn Me.ControlsIfctl.ControlType=acLabelOrctl.ControlType=acTextBox_Orctl.ControlType=acCheckBoxOrctl.ControlType=acComboBoxThen'update control position infostrSQL= "UPDATE tblControlData"& _" SET NewLeft = "&ctl.Properties("Left") & ", NewWidth= "&ctl.Properties("Width") & ""& _" WHERE FormName = '"&Me.Name& "'"& _" AND ControlName = '"&ctl.Name& "' AND ControlType = "&ctl.ControlType& ";"Db.Execute strSQL,dbFailOnErrorEnd IfNextElse'clear table to remove saved layoutDb.Execute"DELETE * FROM tblControlData WHERE FormName = '"&Me.Name& "';",dbFailOnErrorEnd IfEnd IfDoCmd.Close acForm,Me.NameIfGetHideStartFormStatus= "Yes"ThenApplication.QuitElseDoCmd.OpenForm"frmStart"End IfExit_Handler:Exit SubErr_Handler:IfErr= 91Then Resume NextMsgBox"Error "&Err& " in cmdClose_Click procedure: "&Err.DescriptionResumeExit_HandlerEnd Sub
Summary / Conclusions Return To Top
That's all there is to it. All very simple!!!
At some point, I intend to add a further article demonstrating a different approach where controls & labels can be moved and resized using drag and drop.
However, for now, this is likely to be the final article in my lengthy series about extending the functionality of continuous forms.
I intend to add a series of videos to my YouTube channel covering the various features covered in this series.
I also led a presentation to the Access DevCon online conference on 18/19 April 2024. The session title is: You can REALLY do all that with Forms?
The session included many of the items from this series together with some other form related items.
For further details about Access DevCon 2024, see the agenda on the Access DevCon website.
Typically, there are over 110 attendees from several continents for this two-day annual online event.
Video
Return To Top
I have created a short video (1:25) with no sound to demonstrate some features of this app.
You can watch the Move / Resize Columns in a Continuous Form video on my Isladogs YouTube channel or you can click below:
If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.
NOTE: A longer video with an audio commentary explaining all the features and code used in this app will follow later.
Version History Return To Top
NOTE: Versions 2.1 through to 2.3 required .NET version 3.5 to be installed as an optional Windows feature. If it is missing, automation and other errors will occur.
v1.9 2024-02-27 (Page 1 of this series of articles)
Initial release. Layout not saved when the form is closed
v2.1 2024-02-28 (Page 2 of this series of articles)
Added functionality to optionally save the modified layout for future use
v2.2 2024-03-01
Minor update to add support for checkbox and combobox controls and multiple forms
v2.3 2024-03-21
Restored datasheet form to frmStart for comparison. No other changes made
v2.5 2026-11-08
Removed dependency on .NET 3.5 being installed. No external libraries needed if using late binding. Thanks to Xevi Batlle for making these changes.
More Continuous Form Examples
Return To Top
The following articles provide further examples of functionality added to continuous forms:
• Highlight Current Record
• Highlight Selected Control or Column
• Highlight Filtered Columns
• Add Multiselect Filter (2 pages)
• Paint Me Transparent (Selectively Hide Controls)
• Sort columns
• Hide duplicate values in a column (as for reports)
• Hide & Restore Selected Columns (2 pages)
• Freeze Columns (3 pages)
• Move and Resize Columns (2 pages)
Feedback
Return To Top
Please use the Email button in the contact form below to let me know whether you found this article useful or if you have any questions.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 8 Nov 2026
Return to Example Databases Page
Page 2 of 2
1
2
Return To Top
|
|
|