Example Apps for Businesses, Schools & Developers

Page 1 Page 2 Page 3

Version 1.3           Approx 1.3 MB (zipped)                 First Published 14 Feb 2024                 Last Updated 16 Feb 2024

Section Links (this page):
          Freeze Columns in Continuous Forms
          Designing the form - Part 1
          Designing the form - Part 2
          Example App
          More Continuous Forms Examples

This is the ninth article in my series showing how functionality can be added to continuous forms. The article is in two parts.

The first part discusses the built-in freeze fields feature in datasheets and then builds similar functionality for continuous forms.

Despite extensive searches, the only other continuous form examples I found online had very limited functionality with no sorts or filters.
There are two such examples in this very old thread at Access World Forums: Freeze Panes in Form view

In the first part of this article, I will start with that approach and then add sorting and filtering to the continuous form.
The second article uses a different and completely new solution with additional functionality.

Introduction                                                                                                                  Return To Top

Datasheets contain a very useful Freeze Fields feature which allows users to keep selected columns on the screen whilst scrolling a wide datasheet horizontally.

To do this in a datasheet, select the columns you want to freeze, right click and click Freeze Fields.

This shows the same datasheet after horizontal scrolling. The first 3 columns are fixed with the next 5 columns now hidden.

Each of the above solutions needs little or no code. However, you are limited in terms of formatting options in datasheet forms/subforms.

Furthermore, datasheets cannot have form headers/footers or other controls such as command buttons.

If you need those features, there are 2 well known workarounds:
a)   use an unbound main form with a datasheet subform
b)   use a split form with the single form section hidden and the splitter bar disabled. Split forms cannot display a footer section.


Freeze Columns in Continuous Forms                                                                        Return To Top

Needless to say, the freeze columns feature isn't 'natively' available in continuous forms.
This article demonstrates how you can replicate the freeze columns feature in your continuous forms.

Before Horizontal Scrolling

After Horizontal Scrolling

The first 3 columns remain fixed and the next 5 columns have been hidden


Designing the form - Part 1                                                                                         Return To Top

To do this, I used an unbound main form and two subforms with the same record source placed side by side.
Both forms need a similar appearance with the header and detail sections set to the same height in each form.

In this first (simple) version, neither subform can be sorted or filtered. This limitation keeps the code VERY simple

Other REQUIRED design features include:

1.   The record source for each form MUST include a number or autonumber field (here called SA_ID) with consecutive numbering in the same order as the form data.
      The field should normally be hidden
2.   An ActiveX scrollbar control is used at the right side of the main form to control vertical scrolling in both subforms. No additional library references are needed.
3.   The left (fixed) subform has no scrollbars. The right subform has a horizontal scrollbar only
4.   To prevent the data in each subform being modified, set the properties as follows:

Left subform - Data tab Right subform - Data tab Both subforms - Other tab
All Records locked.
No additions/deletions/edits/filters
Edited Records locked.
No additions/deletions/edits/filters
No shortcut menu (context menu disabled)
Properties1 Properties2 Properties3

      Doing this prevents sorting & filtering from the form or using the ribbon.

      Other OPTIONAL design features include:

5.   The detail section in the left (fixed) subform is shaded slightly to make it obvious that these columns are different
6.   Both subforms have a solid border style so there is a defined boundary between them
7.   The left subform has a footer with a record counter instead of navigation buttons

The only code used is in the main form. This controls the vertical scrolling in both subforms (fsubLeft & fsubRight):

Private Sub MainScrollBar_GotFocus()
      Me.MainScrollBar.Enabled = True
End Sub


Private Sub MainScrollBar_Updated(Code As Integer)

On Error GoTo Err_Handler

      Me.fsubLeft.Form.RecordsetClone.FindFirst "SA_ID = " & Me.MainScrollBar.Value
      Me.fsubLeft.Form.Bookmark = Me.fsubLeft.Form.RecordsetClone.Bookmark

      Me.fsubRight.Form.RecordsetClone.FindFirst "SA_ID = " & MainScrollBar.Value
      Me.fsubRight.Form.Bookmark = Me.fsubRight.Form.RecordsetClone.Bookmark

      Exit Sub

      If Err = 3001 Then Exit Sub       'invalid argument
      MsgBox "Error " & Err & " in MainScrollBar_Updated procedure: " & Err.Description
      Resume Exit_Handler

End Sub

This screenshot shows the same form after scrolling both vertically and horizontally


Designing the form - Part 2                                                                                         Return To Top

Whilst the above example works, it is very limited as it is locked down so that the columns cannot be sorted or filtered.

Fixing these restrictions with this form layout is certainly possible but requires a lot more code.

This version includes yellow/green coloured shading for sorted and filtered fields.
The code for the shading is almost identical to that used in my example app in my recent article: Hide & Restore Selected Columns in Continuous Forms

The main form also includes an additional event to disable the vertical scrollbar when it gets focus if the subforms are sorted or filtered.

Private Sub MainScrollBar_GotFocus()

      Me.MainScrollBar.Enabled = True

      'disable scrollbar if subform filtered or sorted to prevent errors
      If strWhere <> "" Or strOrderBy <> "" Then
            MsgBox "The scrollbar is disabled when the subforms are sorted or filtered", vbInformation, "Scrolling currently not available"
            Me.MainScrollBar.Enabled = False
      End If

End Sub

The right subform code to handle filters and sorts is as follows:

Private Sub Form_Load()
      'clear existing sorts/filters
      strWhere = ""
      strOrderBy = ""
End Sub


Private Sub Form_Current()

      If Me.FilterOn = True Then
            strWhere = Me.Filter
            CheckFilterFormat       'highlight filtered columns
            Parent.fsubLeft2.Form.lblFilter.Visible = True
            Parent.cmdClearFilter.Enabled = True
            Parent.fsubLeft2.Form.lblFilter.Visible = False
            Parent.cmdClearFilter.Enabled = False
      End If

      If Me.OrderByOn = True Then
            strOrderBy = Me.OrderBy
            Parent.fsubLeft2.Form.lblSort.Visible = True
            Parent.cmdClearSort.Enabled = True
            Parent.fsubLeft2.Form.lblSort.Visible = False
            Parent.cmdClearSort.Enabled = False
      End If

      'the next line updates the left form but causes the vertical scroll to fail - this is handled in the MainScrollBar_GotFocus event in the main form
      If Me.OrderByOn = True Or Me.FilterOn = True Then Parent.fsubLeft2.Form.Requery

End Sub

The left subform includes more limited code for handling sorts & filters based on changes in the right subform

Private Sub Form_Load()
      'clear existing sorts/filters
      strWhere = ""
      strOrderBy = ""
End Sub


Private Sub Form_Current()

On Error Resume Next

      If Nz(strWhere, "") <> "" Then
            Me.FilterOn = True
            Me.Filter = strWhere
      End If

      If Nz(strOrderBy, "") <> "" Then
            Me.OrderByOn = True
            Me.OrderBy = strOrderBy
      End If


End Sub

The code works well for the intended purpose. However, it also has several important limitations including:

a)   The fixed columns in the left subform are locked and the subform cannot be sorted or filtered directly.
      My attempts to allow direct sorting and filtering in the left subform caused major errors and application crashes.

b)   The ActiveX vertical scrollbar is disabled to prevent errors when the right subform is filtered or sorted

c)   Clearing the sorts & filters works correctly using the form buttons.
      However, if this is attempted using the ribbon, the coloured shading isn't removed and the filters are not cleared.

If you can live with these limitations, it works fine. However, there is a far better solution without any of these limitations and with additional features.
I will discuss the improved version in detail in the second part of this article.

Example App                                                                                                                 Return To Top

This has a startup form with links to all 4 example forms described above:
a)   datasheet form
b)   datasheet split form with no single form section and no splitter bar
c)   continuous form with 2 subforms - no sorts or filters
d)   continuous form with 2 subforms - with sorts and filters


Download                                                                                                                      Return To Top

Click to download:   FreezeColumns_v1.3     ACCDB file     Approx 1.2 MB (zipped)

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 16 Feb 2024

Return to Example Databases Page Page 1 of 3 1 2 3 Return To Top