Version 1.3 Approx 1.3 MB (zipped) First Published 14 Feb 2024 Last Updated 16 Feb 2024
Section Links (this page):
Introduction
Freeze Columns in Continuous Forms
Designing the form - Part 1
Designing the form - Part 2
Example App
Download
More Continuous Forms Examples
Feedback
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) |
|
 |
 |
 |
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_Handler:
Exit Sub
Err_Handler:
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
Else
ClearFilterFormat
Parent.fsubLeft2.Form.lblFilter.Visible = False
Parent.cmdClearFilter.Enabled = False
End If
If Me.OrderByOn = True Then
strOrderBy = Me.OrderBy
CheckSortFormat
Parent.fsubLeft2.Form.lblSort.Visible = True
Parent.cmdClearSort.Enabled = True
Else
ClearSortFormat
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
Me.txtCount.Requery
Me.Refresh
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
|
|
|