Version 2.9 Approx 1.1 MB (zipped) First Published 23 Feb 2024 Last Updated 15 Mar 2024
Section Links (this page):
Introduction
Download
Using the Example App
How Does the Example App Work?
Version History
Video
More Continuous Forms Examples
Feedback
Introduction
Return To Top
This is the third part of the ninth article in my series showing how functionality can be added to continuous forms.
In the
first part of this article, I discussed the built-in freeze fields feature in datasheets and then built similar functionality in continuous forms.
The original approach used for continuous forms involved a main form with 2 subforms. The left subform was fixed. The right subform could be scrolled horizontally, sorted and filtered.
The
second article demonstrated a much improved approach with a continuous form and no subforms. Form navigation was managed using an ActiveX horizontal scrollbar.
This took into account the specified number of frozen columns which remained fixed in position as the form was scrolled horizontally.
This was a completely new approach which I was very pleased with. It did everything it was designed to do perfectly.
However, I completely forgot about navigation using the tab (or shift+tab) keys as I rarely use this method myself.
The result was that tabbing completely ignored the frozen columns and the whole form was scrolled horizontally. Clearly far from satisfactory.
That issue is addressed in this article. It wasn't difficult to get the code to work when tabbing but shift+tab proved more tricky.
The solution discussed here was a collaboration between myself and Xevi Battle . It uses no APIs and requires no additional references (if using late binding)
It makes use of class module code by A.P.R. (Ramachandran) Pillai to streamline the code and make it easily transferable to other forms.
For more details on Ramachandran's approach to streamlining code, see his excellent series of articles starting with
Streamlining Form Module Event Procedures
You can also watch a video of his presentation from Jan 2024 to the Access Europe user group on YouTube:
AEU23: Streamline Form Module Code in the standalone Class Module
I am very grateful to both Xevi and Ramachandran for the significant contributions each made to the development of this application. Many thanks
Download Return To Top
Click to download: FreezeColumns_v2.9 ACCDB file Approx 1.1 MB (zipped)
Using the Example App Return To Top
As before, the app opens to a startup form:
Click Continue to open the main (continuous) form. In this case, the first three columns are fixed but this can be changed between 0 and 8 frozen columns
This is the form in design view. As before, it is just a standard continuous form with an ActiveX horizontal scrollbar.
All the functionality from the previous version still applies here but you can now also scroll the form horizontally to navigate using the TAB / SHIFT+TAB keys. The frozen columns now remain fixed in position.
Navigation using the TAB key behaves exactly as it does in a datasheet form. The columns are only scrolled horizontally once the cursor moves to the right side of the screen.
With further tabbing, the columns are scrolled horizontally and the scrollbar moves further to the right each time.
Similarly when using SHIFT+TAB, the columns are only scrolled horizontally when the cursor is adjacent to the frozen columns with hidden columns needing to be made visible
This continues until all hidden columns are made visible again after which further use of SHIFT+TAB moves the cursor onto the frozen columns
You can also use a mixture of navigation by tabbing or using the scroll bar.
As before, the number of frozen columns can be changed and the form can be sorted and filtered
How Does the Example App Work? Return To Top
As already stated, the app requires no APIs and works in both 32-bit & 64-bit Access. The only non standard features are:
a) an ActiveX scrollbar for horizontal scrolling of the columns that are not frozen (fixed)
b) VBA code to create a dictionary and array list of the column names, widths and positions
To do both of these, you need two additional references (Microsoft Scripting Runtime / MSCore library) if using early binding.
However, I recommend using late binding. The type of binding used and related code is defined in the declarations section of modManageColumns
c) class module code to control the behaviour of the continuous form controls.
In this example all controls are textboxes, but similar code is provided for use with checkboxes or combo box controls on your own forms.
The main class module is clsForm which has the following code:
Option Compare Database
Option Explicit
'=======================================================
'Based on code by APR Pillai
'https://www.msaccesstips.com/2023/04/reusing-form-module-vba-code-for-new.html
'Class module to manage events for each control type used in continuous form
'=======================================================
Private Coll As New Collection
Private iFrm As Form
'------------------------------------------------------------------------
Public Property Get t_Form() As Form
Set t_Form = iFrm
End Property
'------------------------------------------------------------------------
Public Property Set t_Form(ByRef vFrm As Form)
Set iFrm = vFrm
Call Class_Init
End Property
'------------------------------------------------------------------------
Private Sub Class_Init()
Dim labelName As String, controlName As String
Dim iTxt As clsControlTextBox
'The next two lines are not used in this example
Dim iCombo As clsControlComboBox
Dim iCheck As clsControlCheckBox
Dim ctl As Control
Dim ctlToProcess As Control
Const EP = "[Event Procedure]"
For Each ctl In iFrm.Controls
If ctl.ControlType = acLabel And ctl.Tag = "DetachedLabel" Then
labelName = ctl.Name
controlName = GetControlNameFromLabel(labelName)
Set ctlToProcess = iFrm(controlName)
Select Case ctlToProcess.ControlType
Case acTextBox
Set iTxt = New clsControlTextBox
Set iTxt.t_Control = ctlToProcess
iTxt.t_Control.OnEnter = EP
iTxt.t_Control.OnKeyDown = EP
Coll.Add iTxt
Set iTxt = Nothing
Case acComboBox
Set iCombo = New clsControlComboBox
Set iCombo.t_Control = ctlToProcess
iCombo.t_Control.OnEnter = EP
iCombo.t_Control.OnKeyDown = EP
Coll.Add iCombo
Set iCombo = Nothing
Case acCheckBox
Set iCheck = New clsControlCheckBox
Set iCheck.t_Control = ctlToProcess
iCheck.t_Control.OnEnter = EP
iCheck.t_Control.OnKeyDown = EP
Coll.Add iCheck
Set iCheck = Nothing
Case Else
'no code here
End Select
End If
Next
End Sub
'------------------------------------------------------------------------
Private Sub Class_Terminate()
'Delete Collection Object contents
Do While Coll.Count > 0
Coll.Remove 1
Loop
Set iFrm = Nothing
End Sub
The related code in clsControlTextBox is:
Option Compare Database
Option Explicit
'=======================================================
'Xevi Batlle - Based on code by APR Pillai
'https://www.msaccesstips.com/2023/04/reusing-form-module-vba-code-for-new.html
'Used with textbox controls in continuous form
'=======================================================
Private WithEvents Txt As TextBox
'------------------------------------------------------------------------
Public Property Get t_Control() As TextBox
Set t_Control = Txt
End Property
'------------------------------------------------------------------------
Public Property Set t_Control(ByRef pcbo As TextBox)
Set Txt = pcbo
End Property
'------------------------------------------------------------------------
Private Sub txt_KeyDown(KeyCode As Integer, Shift As Integer)
SetPreviousControl Txt.Parent, KeyCode, Shift, Screen.ActiveControl.Name
End Sub
'------------------------------------------------------------------------
Private Sub txt_Enter()
SetNewFirstUnFrozenColumn Txt.Parent, Txt
End Sub
The code in clsControlCheckBox and clsControlComboBox is almost identical to that in clsControlTextBox. Neither of those are used in this example app.
Referencing the class moduule code in the form frmMain needs minimal code.
One new line is added to the declarations section and the Form_Load event to load the class. The Form_Close event is used to reset it to nothing.
Option Compare Database
Option Explicit
Private FormObject As New clsForm
'------------------------------------------------------------------------
Private Sub Form_Load()
Set FormObject.t_Form = Me
'other existing Form_Load code here . . . .
End Sub
'------------------------------------------------------------------------
Private Sub Form_Close()
Set FormObject = Nothing
End Sub
As all the control event code is controlled from the class modules, there is almost no event procedure code for the form controls.
Nor is there any code in the property sheets for any controls.
The only exception is code to handle the SHIFT+TAB key combination and the LEFT, UP & DOWN arrow keys in the KeyDown event of the first control (PupilID).
This was needed to prevent unwanted side effects & to ensure consistent behaviour both in current Access versions e.g. 365 and older versions of Access such as 2010.
Private Sub PupilID_KeyDown(KeyCode As Integer, Shift As Integer)
'Updated v2.8 Colin Riddington
'Move to previous record using Up or Shift+Tab or Left (when at start of field)
'Move to next record using Down arrow
'Disable actions on first or last record
Dim intCtrlDown As Integer, intShiftDown As Integer, position As Integer
Const controlName As String = "PupilID"
'Debug.Print acAltMask, acCtrlMask, acShiftMask
intCtrlDown = (Shift And acCtrlMask)
intShiftDown = (Shift And acShiftMask)
'check cursor position (affected by client settings)
position = 0
If HasProperty(Me(controlName), "SelStart") Then
position = Me(controlName).SelStart
End If
'handle Shift+Tab & Left / Up / Down arrows
If (intShiftDown And KeyCode = vbKeyTab) Or _
(KeyCode = vbKeyLeft And position = 0) Or KeyCode = vbKeyUp Then
KeyCode = 0
If Me.CurrentRecord > 1 Then DoCmd.GoToRecord , , acPrevious
ElseIf KeyCode = vbKeyDown Then
If Me.CurrentRecord < Me.Form.Recordset.RecordCount Then DoCmd.GoToRecord , , acNext
End If
End Sub
There are a few code changes in modHandleColumns.
AssignScrollBarValues has been updated with error handling code to handle extreme cases where the form is made very narrow or very wide:
Sub AssignScrollBarValues(frm As Form)
'Xevi Batlle / Colin Riddington
Dim newBarSize As Long
On Error GoTo Err_Handler
With frm.MainScrollBar
.Max = oArrayListOrdered.Count - 1
.Min = frm.cboFrozenColumns
.Value = frm.cboFrozenColumns
.Width = 1 'Added to prevent from raising an error when changing the frozen columns number
.Left = dict(oArrayListOrdered(GetCountFrozenColumns) & "_Left")
'next section prevents error 2100 if form width reduced so scroll bar doesn't fit
'error handling prevents same error for users with large monitors
newBarSize = frm.InsideWidth - .Left - 500
If newBarSize > 0 Then
.Width = newBarSize
End If
End With
Exit_Handler:
Exit Sub
Err_Handler:
'err 6 = Overflow (exceeds integer limit)
'err 2100 = The control or subform control is too large for this location.
'The number you entered for the Left, Top, Height, or Width property is too large or is a negative number.
'Reduce the size of the control or subform control, or enter a positive number.
'err 91 = Object variable or With block variable not set - triggered after err 2100
If Err = 2100 Or Err = 6 Then
'don't allow scrollbar width to exceed integer limit. Set max to 32000 for safety
frm.MainScrollBar.Width = 32000 - frm.MainScrollBar.Left - 500
ElseIf Err = 91 Then
Resume Next
Else
MsgBox "Error " & Err.Number & " in AssignScrollBarValues procedure : " & Err.Description
End If
Resume Exit_Handler
End Sub
Four new procedures have been added for use when tabbing through the form
Public Function FullShownInWindow(frm As Form, ctl As Control) As Boolean
'Xevi Batlle v2.4
FullShownInWindow = True
If ctl.Left + ctl.Width > frm.InsideWidth - 500 Then
FullShownInWindow = False
End If
End Function
'------------------------------------------------------------------------
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'http://allenbrowne.com/AppPrintMgtCode.html#HasProperty
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
'------------------------------------------------------------------------
Public Function SetPreviousControl(frm As Form, KeyCode As Integer, Shift As Integer, controlName As String) As Boolean
'v2.4 Xevi Batlle
'Updated v2.8 Colin Riddington
Dim intCtrlDown As Integer, intShiftDown As Integer
Dim FirstLabelNameNotFrozen As String, FirstControlNameNotFrozen As String
Dim ctl As Control
Dim position As Integer
Set ctl = frm(controlName)
position = 0
If HasProperty(frm(controlName), "SelStart") Then
position = ctl.SelStart
End If
'Debug.Print acAltMask, acCtrlMask, acShiftMask
intShiftDown = (Shift And acShiftMask)
intCtrlDown = (Shift And acCtrlMask)
' Trap Shift + TAB & Left / Up / Down arrows to change default behaviour
If (intShiftDown And KeyCode = vbKeyTab) Or (KeyCode = vbKeyLeft And position = 0) Then
FirstLabelNameNotFrozen = oArrayListOrdered(frm.MainScrollBar)
FirstControlNameNotFrozen = GetControlNameFromLabel(FirstLabelNameNotFrozen)
If controlName = FirstControlNameNotFrozen Then
' If true, the control is the first non frozen column
If frm.MainScrollBar > frm.MainScrollBar.Min Then
frm.MainScrollBar = frm.MainScrollBar - 1
KeyCode = 0
End If
End If
ElseIf KeyCode = vbKeyUp Then
'move to previous record
If frm.CurrentRecord > 1 Then
DoCmd.GoToRecord , , acPrevious
ctl.SetFocus
End If
ElseIf KeyCode = vbKeyDown Then
'move to next record
If frm.CurrentRecord < frm.Form.Recordset.RecordCount Then
DoCmd.GoToRecord , , acNext
ctl.SetFocus
End If
End If
End Function
'------------------------------------------------------------------------
Public Function SetNewFirstUnFrozenColumn(frm As Form, ctl As Control) As Integer
'Xevi Batlle v2.5
Dim totalSize As Long
Dim labelNameToShow As String
Dim labelName As String, controlName As String
Dim nextOrigin As Long
Dim I As Integer
If FullShownInWindow(frm, ctl) Then Exit Function
nextOrigin = frm.MainScrollBar
labelNameToShow = getLabelNameFromControl(ctl.Name)
totalSize = 0
For I = frm!MainScrollBar To oArrayListOrdered.Count - 1
labelName = oArrayListOrdered(I)
controlName = GetControlNameFromLabel(labelName)
totalSize = totalSize + frm(labelName).Width
If totalSize > ctl.Width Then
nextOrigin = I
Exit For
End If
Next I
If nextOrigin < oArrayListOrdered.Count - 1 Then
nextOrigin = nextOrigin + 1
End If
SetNewFirstUnFrozenColumn = nextOrigin
frm.MainScrollBar = nextOrigin
End Function
The app now appears to work correctly both when navigating using the scrollbar and with the tab key.
It also handles the behaviour when using arrow keys to navigate the form.
The app should also be resilient against different client settings and show consistent behaviour in all versions of Access from 2010 through to 365
I hope you find this feature useful for your own applications. Feedback welcomed!
To use this approach in your own apps, you need to import the following items:
a) Table tblSettings
b) Standard modules: modFunctions / modManageColumns / modNavPane
c) Class modules: clsForm, clsControlTextBox and (if required) clsControlCheckBox, clsControlComboBox
d) Use late binding or add the two references listed above
The form code should then be easily transferable to your own forms with little or no changes required.
e) Import code from PupilID_KeyDown event to the same event in the first control on your form.
f) Make sure the form property sheet has KeyPreview = Yes
Version History Return To Top
v1.3 14/02/2024 (Page 1 of this series of articles)
- initial release using a main form with 2 subforms. Some limitations in functionality
v2.3 16/02/2024
- new approach suggested by Xevi Batlle using adictionary & array list of column names, widths and positions
- completely revised to work on a continuous form with no subforms using an ActiveX scrollbar
- added code to specify the number of frozen columns & optionally lock those columns
- horizontal & vertical scrolling now works correctly together with sorting & filtering
- BUG: use of tab key to navigate didn't take into account frozen colunns
v2.6 23/02/2024
- updated code to also work correctly when navigating the form using Tab or Shift+Tab keys
- added class module code based on an approach by A.P.R. Pillai to manage continuous form controls and streamline form code
v2.9 25/02/2024
- fixed issues with use of Shift+Tab key in the first column. This had been a particular problem in older versions of Access e.g. 2010
- added code to also handle Left/Up & Down arrow keys together disabling use of End / Ctrl+End keys
- other minor bug fixes to ensure consistent behaviour in different versions of Access
Video
Return To Top
UPDATE 15 Mar 2024
I have now created a video (16:22) for YouTube to demonstrate freezing (and optionally locking) selected columns in a continuous form.
You can watch the Freeze 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.
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
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 15 Mar 2024
Return to Example Databases Page
Page 2 of 3
1
2
3
Return To Top
|
|
|