Example Apps for Businesses, Schools & Developers

Page 1 Page 2

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:

StartForm
Click a button to open either the datasheet or the main (continuous) form.

MainForm1
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:

SettingsForm
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.

SaveLayoutMessage
      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.

ControlDataTable
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.

ControlDataTableUpdated
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.

MainFormModified
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.

DesignView
If the user chooses not to save the modified layout on close, table tblControlData is emptied

ControlDataEmpty
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 Sub Form_Open(Cancel As 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 Riddington

On Error Resume Next

Set Db = CurrentDb
Set frm = Me

CommandBars("Form View Popup").Enabled = True
CommandBars("Form View Control").Enabled = True

'count records in tblControlData
I = DCount("*", "tblControlData", "FormName = '" & Me.Name & "'")

If Nz(Me.OpenArgs, "") = "" Then
Application.Echo False

'set up dictionary based on default control layout or modified layout from tblControlData where it exists
Set dict = CreateObject("Scripting.Dictionary")
For Each ctl In Me.Controls
If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox) And ctl.Tag <> "A" Then
If GetSaveNewLayoutStatus = "Yes" Then
ctl.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 If

dict.Add ctl.Name & "_Left", ctl.Properties("Left").Value
dict.Add ctl.Name & "_Width", ctl.Properties("Width").Value

'v2.0 - if tblControlData is empty, add control names to table for future use
If I = 0 Then
strSQL = "INSERT INTO tblControlData(FormName, ControlName, ControlType)" & _
" VALUES('" & Me.Name & "', '" & ctl.Name & "', " & ctl.ControlType & ");"
' Debug.Print strSQL
Db.Execute strSQL, dbFailOnError

'update control position info
strSQL = "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, dbFailOnError
End If
End If
Next

'Store labels in a Collection to order them by Left Position (eliminating .NET 3.5 dependency)
Set colColumns = New Collection

Dim curLeft As Long
For Each ctl In Me.Controls
If ctl.ControlType = acLabel And ctl.Tag = "DetachedLabel" Then
curLeft = ctl.Properties("Left")
AddSortedColumn colColumns, Format$(curLeft, "000000") & ctl.Name
End If
Next

Set oCollection = colColumns

Application.Echo True
End If
End 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 Sub Form_Load()
'Colin Riddington

Application.Echo False
DoCmd.Maximize

MinimizeNavigationPane
If GetHideStartFormStatus = "Yes" Then cmdClose.Caption = "Quit"

Me.cmdClearFilter.Enabled = False
Me.cmdClearSort.Enabled = False
Me.cmdReset.Enabled = False
Me.cboHiddenColumns.Visible = False
Me.txtCurrentRecord.Visible = False

Me.cmdClose.SetFocus

Me.OrderByOn = False
Me.FilterOn = False

SetListboxColumnOrder Me

DoEvents

'v2.0
If CountHiddenColumns > 0 Then Me.cboHiddenColumns.Visible = True
If CountMovedColumns > 0 Or CountChangedColumnWidths > 0 Or Me.cboHiddenColumns.Visible = True Then
cmdReset.Enabled = True
End If

Me.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."

GetFormFooterCaptions Me

Application.Echo True

End 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 Sub Form_Load()
'Colin Riddington

Application.Echo False
DoCmd.Maximize

MinimizeNavigationPane
If GetHideStartFormStatus = "Yes" Then cmdClose.Caption = "Quit"

Me.Properties("Zoom") = 97

Me.cmdClearFilter.Enabled = False
Me.cmdClearSort.Enabled = False
Me.cmdReset.Enabled = False
Me.cboHiddenColumns.Visible = False
Me.txtCurrentRecord.Visible = False

Me.cmdClose.SetFocus

Me.OrderByOn = False
Me.FilterOn = False

SetListboxColumnOrder Me

DoEvents

'v2.0
If CountHiddenColumns > 0 Then Me.cboHiddenColumns.Visible = True
If CountMovedColumns > 0 Or CountChangedColumnWidths > 0 Or Me.cboHiddenColumns.Visible = True Then
cmdReset.Enabled = True
End If

Me.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."

GetFormFooterCaptions Me

Application.Echo True

End 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 Sub cmdReset_Click()
'Colin Riddington

Application.Echo False

'count records in tblControlData
I = DCount("*", "tblControlData", "FormName = '" & Me.Name & "'")

'restore all controls using dictionary values
For Each ctl In Me.Controls
If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox) And ctl.Tag <> "A" Then
If I > 0 Then
'v2.2 - use original values from tblControlData before any modifications made
ctl.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 dictionary
ctl.Properties("Left") = dict(ctl.Name & "_Left")
ctl.Properties("Width") = dict(ctl.Name & "_Width")
End If
End If
Next

' clear table to remove saved layout
Db.Execute "DELETE * FROM tblControlData WHERE FormName = '" & Me.Name & "';", dbFailOnError

'v2.1 - restart form to restore default layout
DoCmd.Close acForm, Me.Name

DoCmd.OpenForm "frmStudentAttendanceCont"
Application.Echo True

End Sub


5.   cmdClose_Click

      This event controls what happens when the form is closed depending on the application settings and user choice

Private Sub cmdClose_Click()
'Colin Riddington

On Error GoTo Err_Handler

If Me.cmdReset.Enabled = True Then
If GetShowLayoutWarningsStatus = "Yes" Then
'ask whether to save layout
If FormattedMsgBox("The form layout has been altered" & _
"@Do you want to save this new layout for future use? @", _
vbQuestion + vbYesNo + vbDefaultButton2, "Save Layout?") = vbYes Then

CurrentDb.Execute "UPDATE tblSettings SET ItemValue = 'Yes'" & _
" WHERE ItemName='SaveNewLayout';", dbFailOnError

For Each ctl In Me.Controls
If ctl.ControlType = acLabel Or ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Then
'update control position info
strSQL = "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, dbFailOnError
End If
Next
Else
'clear table to remove saved layout
Db.Execute "DELETE * FROM tblControlData WHERE FormName = '" & Me.Name & "';", dbFailOnError
End If
ElseIf GetSaveNewLayoutStatus = "Yes" Then
'save layout without asking
CurrentDb.Execute "UPDATE tblSettings SET ItemValue = 'Yes'" & _
" WHERE ItemName='SaveNewLayout';", dbFailOnError

For Each ctl In Me.Controls
If ctl.ControlType = acLabel Or ctl.ControlType = acTextBox _
Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Then
'update control position info
strSQL = "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, dbFailOnError
End If
Next
Else
'clear table to remove saved layout
Db.Execute "DELETE * FROM tblControlData WHERE FormName = '" & Me.Name & "';", dbFailOnError
End If
End If

DoCmd.Close acForm, Me.Name

If GetHideStartFormStatus = "Yes" Then
Application.Quit
Else
DoCmd.OpenForm "frmStart"
End If

Exit_Handler:
Exit Sub

Err_Handler:
If Err = 91 Then Resume Next
MsgBox "Error " & Err & " in cmdClose_Click procedure: " & Err.Description
Resume Exit_Handler

End 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