Version 3.0           Approx 1.1 MB (zipped)                 First Published 23 Feb 2024                 Last Updated 8 Nov 2026

UPDATED 2026-11-08:     Version 3.0 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?
          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 Batlle . 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_v3.0     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 collection of the column names, widths and positions
      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

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
Dim iCombo As clsControlComboBox 'not used in this example
Dim iCheck As clsControlCheckBox 'not used in this example

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

d)   Referencing the class module 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()

    Application.Echo False
    
    Set FormObject.t_Form = Me
    
    'other existing Form_Load code goes here ...
    
End Sub
    
Private Sub Form_Close()
    Set FormObject = Nothing
End Sub

      The Form_Open event is used to set up the dictionary used to store column names, positions & widths. A collection stores the detached labels ordered by left position.

'Uses Dictionary to store column names, positions & widths
'Xevi Batlle
Private Sub Form_Open(Cancel As Integer)

Dim currentControlName As String, currentLabelName As String, PreviouslabelName As String
Dim J As Long

#If EarlyBind Then
'    these are defined in modManageColumns
'    Dim dict As New Scripting.Dictionary
'    Dim colOrdered As New Collection
#Else
'    these are also defined in modManageColumns
'    Dim dict As Object
'    Dim colOrdered As Collection
    Set dict = CreateObject("Scripting.Dictionary")
    Set colOrdered = New Collection
#End If

    For Each ctl In Me.Controls
        If ctl.ControlType = acLabel Or ctl.ControlType = acTextBox Then
            dict.Add ctl.Name & "_Left", ctl.Left
            dict.Add ctl.Name & "_Width", ctl.Width
        End If
    Next
    
    ' * * * Stores detached labels in a Collection ordered by Left Position
    For Each ctl In Me.Controls
        If ctl.ControlType = acLabel And ctl.Tag = "DetachedLabel" Then
            For J = 1 To colOrdered.Count
                If ctl.Left < dict(colOrdered(J) & "_Left") Then
                    colOrdered.Add ctl.Name, Before:=J
                    Exit For
                End If
            Next J
            If J > colOrdered.Count Then
                colOrdered.Add ctl.Name
            End If
        End If
    Next ctl
    
    For I = 1 To colOrdered.Count
        currentLabelName = colOrdered(I)
        ' Sets the distance from previous control
        If I = 1 Then
            dict.Add currentLabelName & "_Offset", 0
        Else
            PreviouslabelName = colOrdered(I - 1)
            currentControlName = GetControlNameFromLabel(currentLabelName)
            dict.Add currentControlName & "_Offset", dict(currentControlName & "_Left") - (dict(PreviouslabelName & "_Left") _
                + dict(PreviouslabelName & "_Width"))
        End If
    Next I
 
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 and 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)
'Move to next record using Down
'Move to previous record using Up or Shift+Tab or Left (when at start of field)
'Disable action on first or last record
'v2.8 Colin Riddington

Dim intShiftDown As Integer
Dim intCtrlDown As Integer
Dim position As Integer
Const controlName As String = "PupilID"
    'Debug.Print acAltMask, acCtrlMask, acShiftMask
    
    intShiftDown = (Shift And acShiftMask)
    intCtrlDown = (Shift And acCtrlMask)
    
    '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. Also disable End & Ctrl+End keys
    If (intShiftDown And KeyCode = vbKeyTab) Or (KeyCode = vbKeyLeft And position = 0) Or KeyCode = vbKeyUp _
        Or KeyCode = vbKeyEnd Or (intCtrlDown And KeyCode = vbKeyEnd) 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
            'above line makes it go down two records! Workround - fix by moving back up one record
            DoCmd.GoToRecord , , acPrevious
        End If
    End If
    
End Sub

e)   There are a few code changes in modManageColumns.

      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 = colOrdered.Count
        .Min = frm.cboFrozenColumns + 1
        .Value = frm.cboFrozenColumns + 1
        .Width = 1  'Added to prevent from raising an error when changing the frozen columns number
        .Left = dict(colOrdered(GetCountFrozenColumns + 1) & "_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
      ' Debug.Print frm.InsideWidth, .Left, newBarSize
        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

'http://allenbrowne.com/AppPrintMgtCode.html#HasProperty
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    '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
'2.8 Colin Riddington / Xevi Batlle

Dim intCtrlDown As Integer
Dim 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)
    
    'Disable End & Ctl+End keys
    If (intCtrlDown And KeyCode = vbKeyEnd) Or KeyCode = vbKeyEnd Then
        KeyCode = 0
        Exit Function
    End If
    
    'Trap Shift + TAB & Left / Up / Down arrows to change default behaviour
    If (intShiftDown And KeyCode = vbKeyTab) Or (KeyCode = vbKeyLeft And position = 0) Then
        FirstLabelNameNotFrozen = colOrdered(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 colOrdered.Count
        labelName = colOrdered(I)
        controlName = GetControlNameFromLabel(labelName)
        totalSize = totalSize + frm(labelName).Width
        If totalSize > ctl.Width Then
            nextOrigin = I
            Exit For
        End If
    Next I
    
    If nextOrigin < colOrdered.Count 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 Microsoft Scripting Runtime reference library.

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

NOTE: Versions 2.3 to 2.9 all require .NET version 3.5 to be installed as an optional Windows feature. If it is missing, automation and other errors will occur.

v2.3     2024-02-16       (Page 2 of this series of articles)
- new approach suggested by Xevi Batlle using a dictionary & 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     2024-02-23
- 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     2024-02-25
- 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

v3.0     2026-11-08
- updated code to remove dependency on .NET version 3.5 which was needed for the array list
- Many thanks to Xevi Batlle for detecting the issue and updating the code



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 E-Mail 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 3 1 2 3 Return To Top