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


Section Links (this page):
          Introduction
          Download
          Using the Example App
          How Does the Example App Work?
          More Continuous Forms Examples
          Feedback


Introduction                                                                                                                  Return To Top

This is the second 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 approach used for continuous forms required a main form with 2 subforms. The left subform is fixed. The right subform can be scrolled horizontally, sorted and filtered.


Whilst that worked well, that approach also had certain issues / limitations including:

a)   The fixed columns in the left subform are locked and the subform cannot be sorted or filtered directly.
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 BUT not using the ribbon.

This article demonstrates a much improved approach with a continuous form and no subforms. All the above issues have been solved.
As far as I am aware, this type of solution has never been published before. I hope you find it useful!

I am very grateful to Xevi Battle for suggesting this approach using ideas from my earlier article: Hide & Restore Selected Columns in Continuous Forms
A significant part of the code used is also due to Xevi. Many thanks



Download                                                                                                                      Return To Top

Click to download:   FreezeColumns_v2.3     ACCDB file     Approx 1.4 MB (zipped)


Using the Example App                                                                                               Return To Top

The app opens to a startup form:


Click Continue to open the main (continuous) form. In this case the first three columns are fixed


This is the form in design view. It is just a standard continuous form with an ActiveX horizontal scrollbar.


The form allows you to specify the number of frozen columns (any value between 0 and 8). If set to zero, all columns are scrollable as in a standard continuous form.
You can also optionally choose to lock the frozen columns so the text in those columns cannot be edited. The text in the locked columns is shaded RED.
In this screenshot, SEVEN columns have been frozen and the text in those columns LOCKED.


The next screenshot shows the same setup after scrolling both horizontally and vertically


All columns can also be sorted and filtered whether or not the text is locked:


Sorts and filters can be removed using either the form buttons or the ribbon


This is the same form after removing both sorts and filters and then scrolling both horizontally & vertically.




How Does the Example App Work?                                                                           Return To Top

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. Alternatively, use late binding:

Early Binding

Late Binding

The type of binding used and related code is defined in the declarations section of modManageColumns

Option Compare Database
Option Explicit

'#Const EarlyBind = True       'Use Early Binding
#Const EarlyBind = False       'Use Late Binding

'Early Binding comments* * * *
'The ArrayList is part of the library 'System.Collections * * * * * * * * * * * * * * * * * * * * * *
'You'll find it in the file       \WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb (32 bit)
'                                                \WINDOWS\Microsoft.NET\Framework64\v4.0.30319\mscorlib.tlb (64 bit)
'------------------------------------------------------------------------
'The dictionary is an element in the Microsoft Scripting Runtime library.
'That library is the file       \Windows\system32\scrrun.DLL or in a similar directory
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

#If EarlyBind Then

      Public dict As New Scripting.Dictionary
      Public oArrayList As New ArrayList
      Public oArrayListOrdered As New ArrayList
#Else
      Public dict As Object
      Public oArrayList As Object
      Public oArrayListOrdered As Object
#End If

The Form_Open event creates both the dictionary and array list

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

'Set values if using late binding
#If EarlyBind Then
      'no code needed here
#Else
      Set oArrayList = CreateObject("System.Collections.ArrayList")
      Set oArrayListOrdered = CreateObject("System.Collections.ArrayList")
      Set dict = CreateObject("Scripting.Dictionary")
#End If

      'Create dictionary to store control names, sizes & postions
      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

      'Store labels in an ArrayList to order them by Left Position
      For Each ctl In Me.Controls
            If ctl.ControlType = acLabel And ctl.Tag = "DetachedLabel" Then
                  oArrayList.Add Format(ctl.Left, "000000") & ctl.Name       ' Assign this format to sort Left position as string
            End If
      Next

      oArrayList.Sort
      For I = 0 To oArrayList.Count - 1
            oArrayListOrdered.Add Mid(oArrayList.Item(I), 7)
      Next I

End Sub

The Form_load event sets up the initial formatting depending on conditions saved in tblSettings:

Private Sub Form_Load()

      'set combo values depending on values saved in tblSettings
      Me.cboFrozenColumns = GetCountFrozenColumns
      Me.cboLockFrozen = GetLockFrozenColumnsStatus

      'clear existing filters and sorts
          strWhere = ""
      strOrderBy = ""
      Me.FilterOn = False
      Me.OrderByOn = False

      AssignScrollBarValues Me     'set horizontal scrollbar position depending on number of frozen columns
      ShowAllColumns Me     'move horizontal scrollbar fully to left so all columns visible
      PaintFrozenColumns Me    'format frozen columns light grey (with red text if locked)

End Sub

There are several procedures referenced in Form_Load which are called from modManageColumns:

Function GetCountFrozenColumns()
'Colin Riddington
      GetCountFrozenColumns = Nz(DLookup("ItemValue", "tblSettings", "ItemName='CountFrozenColumns'"), "3")
End Function
'---------------------------------------------------

Function GetLockFrozenColumnsStatus()
'Colin Riddington
      GetLockFrozenColumnsStatus = Nz(DLookup("ItemValue", "tblSettings", "ItemName='LockFrozenColumns'"), "No")
End Function

'---------------------------------------------------

Function GetControlNameFromLabel(labelName As String)
'Colin Riddington
      GetControlNameFromLabel = Left(labelName, Len(labelName) - 6)
End Function
'---------------------------------------------------

Sub AssignScrollBarValues(frm As Form)
'Xevi Batlle

      With frm.MainScrollBar
            .Max = oArrayListOrdered.Count - 1
            .Min = frm.cboFrozenColumns

            .Value = frm.cboFrozenColumns
            .Left = dict(oArrayListOrdered(GetCountFrozenColumns) & "_Left")
            .Width = frm.InsideWidth - .Left - 500
      End With

End Sub
'---------------------------------------------------

Sub PaintFrozenColumns(frm As Form)
'Xevi Batlle / Colin Riddington

      For I = 0 To oArrayListOrdered.Count - 1
            labelName = oArrayListOrdered(I)
            controlName = Left(labelName, Len(labelName) - 6)
            frm(controlName).Locked = False
            If frm(controlName).BackColor <> colPaleGreen And frm(controlName).BackColor <> colPaleYellow Then
                  If I < GetCountFrozenColumns Then
                        frm(controlName).BackColor = colPaleGrey
                        frm(controlName).ForeColor = IIf(GetLockFrozenColumnsStatus = "Yes", colDarkRed, colDarkGrey)
                        If GetLockFrozenColumnsStatus = "Yes" Then frm(controlName).Locked = True
                  Else
                        frm(controlName).BackColor = vbWhite
                        frm(controlName).ForeColor = colDarkGrey
                  End If
            End If
      Next I

End Sub
'---------------------------------------------------

Sub ShowAllColumns(frm As Form)
'Xevi Batlle

      For I = 0 To oArrayListOrdered.Count - 1
            labelName = oArrayListOrdered(I)
            controlName = GetControlNameFromLabel(labelName)
            widthControl = dict(oArrayListOrdered(I) & "_Width")
            leftPosition = dict(oArrayListOrdered(I) & "_Left")
            frm(labelName).Left = leftPosition
            frm(labelName).Visible = True
            frm(controlName).Left = leftPosition
            frm(controlName).Visible = True
            leftPosition = leftPosition + widthControl
      Next I

End Sub

The Form_Current event checks if any sorts and filters have been applied and sets or clears the format conditions as appropriate for relevant columns

It then runs PaintFrozenColumns again to format frozen columns light grey (with red text if locked)

Private Sub Form_Current()

      If Me.FilterOn = True Then
            strWhere = Me.Filter
            CheckFilterFormat Me     'highlight filtered columns
            Me.cmdClearFilter.Enabled = True
      Else
            ClearFilterFormat Me
            Me.cmdClearFilter.Enabled = False
      End If

      If Me.OrderByOn = True Then
            strOrderBy = Me.OrderBy
            CheckSortFormat Me     'highlight sorted columns
            Me.cmdClearSort.Enabled = True
      Else
            ClearSortFormat Me
            Me.cmdClearSort.Enabled = False
      End If

      PaintFrozenColumns Me

End Sub

NOTE:
The CheckFilterFormat / ClearFilterFormat / CheckSortFormat / ClearSortFormat code is in modManageColumns
It is identical to that described in earlier articles such as: Hide & Restore Selected Columns in Continuous Forms

I hope you find this approach useful for your own applications

To use this approach in your own apps, you need to import the following items:
a)   Table tblSettings
b)   All three standard modules: modFunctions / modManageColumns / modNavPane
b)   Use late binding or add the two references listed above

The form code should be easily transferable to your own forms with little or no changes required.

As a test, I created a second form frmStudentAddresses (not included in the example app) with similar layout.


I then copied all the code from frmMain with no changes. It worked perfectly first time!

UPDATE 23 Feb 2024
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. This is clearly far from satisfactory.

That issue is addressed in the third part of this article. It wasn't difficult to get the code to work when tabbing but shift+tab proved more tricky.

The solution used makes use of class module code by A.P.R. (Ramachandran) Pillai to streamline the code and make it easily transferable to other 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

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



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