Version 2.2 Approx 1.7 MB (zipped) First Published 28 Feb 2024 Last Updated 1 Mar 2024
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.2 ACCDB file Approx 1.8 MB (zipped)
Using the Example App Return To Top
Once again, the app opens to the startup form:
Click Continue to open 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.
As before, when the main form is first opened, it uses a VBA dictionary and array list as follows:
a) A dictionary is created to store column names, positions and widths
b) The array list populates the listbox placing the columns in order by left position
Doing this allows the columns to be moved and resized but also for the original layout to be restored at any time during a session.
It also means the original layout is retrieved when the form is next opened.
The code is fast and very efficient in achieving these aims. However, it does not allow users to save a modified layout.
In order to save a modified layout, the layout data needs to be 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 version, 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 populates the array list based on the label captions in order of position so it can be used as the listbox row source.
Private Sub Form_Open(Cancel As Integer)
'Uses Dictionary to store column names, positions & widths
'Uses array list to store label order
'Xevi Batlle
'v2.0 - Colin Riddington
'also populate table tblControlData for use when modified layout saved
'Table holds control name, control type, left, width & tag properties
'v2.2 - Colin Riddington
'added form name to tblControlData for use with multiple forms
'mnir bug fixes & added support for checkbox and combobox controls
On Error Resume Next
Set Db = CurrentDb
'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.Left = DLookup("NewLeft", "tblControlData", "ControlName = '" & ctl.Name & "'")
ctl.Width = DLookup("NewWidth", "tblControlData", "ControlName = '" & ctl.Name & "'")
End If
dict.Add ctl.Name & "_Left", ctl.Left
dict.Add ctl.Name & "_Width", ctl.Width
'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 & ");"
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.Left & ", ControlWidth= " & ctl.Width & ", Tag = '" & ctl.Tag & "'" & _
" WHERE ControlName = '" & ctl.Name & "' AND ControlType = " & ctl.ControlType & ";"
Db.Execute strSQL, dbFailOnError
End If
End If
Next
'Store labels in an ArrayList to order them by Left Position in listbox
Set oArrayList = CreateObject("System.Collections.ArrayList")
For Each ctl In Me.Controls
If ctl.ControlType = acLabel And ctl.Tag = "DetachedLabel" Then
' Assign this format to sort Left position as string
oArrayList.Add Format(ctl.Left, "000000") & ctl.Name
End If
Next
oArrayList.Sort
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
'v2.0 - check for hidden / moved / resized columns
If CountHiddenColumns > 0 Then Me.cboHiddenColumns.Visible = True
If CountMovedColumns > 0 Or CountChangedColumnWidths > 0 Or Me.cboHiddenColumns.Visible = True Then cmdReset.Enabled = True
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 cboHiddenColumns_AfterUpdate()
'Colin Riddington/Xevi Batlle
If Not IsNull(Me.cboHiddenColumns) Then
labelName = Me.cboHiddenColumns
controlName = Left(labelName, Len(labelName) - 6)
End If
Me.cboHiddenColumns.RowSource = ""
For Each ctl In Me.Controls
If ctl.ControlType = acLabel And ctl.Name <> Me.cboHiddenColumns And ctl.Width = 1 Then
Me.cboHiddenColumns.AddItem ctl.Name & ";" & Replace(ctl.Caption, vbCr, " ")
End If
Next
'v2.2 - Colin Riddington
Me(labelName).Width = DLookup("ControlWidth", "tblControlData", "FormName = '" & Me.Name & "' And ControlName = '" & labelName & "'")
Me(controlName).Width = Me(labelName).Width
'restore column positions
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 ctl.Left > Me(labelName).Left Then
ctl.Left = ctl.Left + Me(labelName).Width - 1
End If
End If
Next
SetListboxColumnOrder Me
If Me.cboHiddenColumns.ListCount > 0 Then
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. " & _
"Restore any hidden column to its original position by selecting it from the Show Hidden Column combobox " & _
"OR click the Reset All Columns button to restore all hidden columns."
Else
Me.cmdReset.SetFocus
Me.cboHiddenColumns.Visible = False
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 and the Tools button to edit settings"
DisableColumnButtons Me
End If
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")
'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.Left = DLookup("ControlLeft", "tblControlData", "FormName = '" & Me.Name & "' And ControlName = '" & ctl.Name & "'")
ctl.Width = DLookup("ControlWidth", "tblControlData", "FormName = '" & Me.Name & "' And ControlName = '" & ctl.Name & "'")
Else
'use dictionary to retrieve values
ctl.Left = dict(ctl.Name & "_Left")
ctl.Width = dict(ctl.Name & "_Width")
End If
End If
Next
'v2.2 - clear table to remove saved layout
Db.Execute "DELETE * FROM tblControlData WHERE FormName = '" & Me.Name & "';", dbFailOnError
'v2.2 - restart form to restore default layout
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmMain"
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.Left & ", NewWidth= " & ctl.Width & "" & _
" WHERE FormName = '" & Me.Name & "'" & _
" AND ControlName = '" & ctl.Name & "' AND ControlType = " & ctl.ControlType & ";"
Db.Execute strSQL, dbFailOnError
End If
Next
Else
'v2.2 - 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.Left & ", NewWidth= " & ctl.Width & "" & _
" WHERE FormName = '" & Me.Name & "'" & _
" AND ControlName = '" & ctl.Name & "' AND ControlType = " & ctl.ControlType & ";"
Db.Execute strSQL, dbFailOnError
End If
Next
Else
'v2.2 - 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 am also leading 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 will include many of the items from this series together with some other form related items
For further details about both my session and all the other excellent sessions announced so far, see the agenda on the Access DevCon website.
Last year there were over 150 attendees from 5 continents and several people were too late to reserve a place.
Don't forget to register your place for this year's conference before it is full.
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
v1.9 27/02/2024 (Page 1 of this series of articles)
Initial release. Layout not saved when the form is closed
v2.1 28/02/2024 (Page 2 of this series of articles)
Added functionality to optionally save the modified layout for future use
v2.2 01/03/2024
Minor update to add support for checkbox and combobox controls and multiple forms
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 contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 1 Mar 2024
Return to Example Databases Page
Page 2 of 2
1
2
Return To Top
|
|
|