Example Apps for Businesses, Schools & Developers

Page 1 Page 2 Page 3

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:

StartForm
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

MainForm1
This is the form in design view. As before, it is just a standard continuous form with an ActiveX horizontal scrollbar.

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

TabScroll
With further tabbing, the columns are scrolled horizontally and the scrollbar moves further to the right each time.

TabScroll2
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

ShiftTabScroll
This continues until all hidden columns are made visible again after which further use of SHIFT+TAB moves the cursor onto the frozen columns

ShiftTabScroll2
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

8 Columns Frozen, Filtered, Sorted & Scrolled


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