Version 1.2           Approx 1.1 MB (zipped)                 First Published 4 Feb 2024                 Last Updated 7 Feb 2024


This is the eighth article in my series showing how functionality can be added to continuous forms.

Datasheet forms provide some very useful features that are missing in continuous forms.
For example, clicking on the column headers displays a shortcut menu allowing users to sort / resize / hide and freeze fields


By contrast, none of those features are natively available when clicking on column headers in continuous forms.
However, sorting and filtering are available by right clicking on the field controls.


The example app supplied with this article demonstrates three simple ways of allowing users to hide columns by clicking on the column headers.



Download

Click to download:   HideSelectedColumns_v1.2     ACCDB file     Approx 1.1 MB     (zipped)

After downloading, unzip the file then unblock the ACCDB file to remove the 'mark of the web'. Next save to a trusted location or enable content after opening it.



Using the Example App

The app opens to a startup form:


Click Continue to open the main (continuous) form


The form contains 22 fields and you will need to scroll horizontally to view all of them.

Notice the Unique Pupil Number (UPN) field directly below the Hide UPN button. Click the button to hide both the UPN field and its column header.


CODE:

Private Sub cmdUPN_Click()

      If Me.cmdUPN.Caption = "Hide UPN" Then
            cmdUPN.Caption = "Show UPN"
            Me.UPN.Visible = False
            Me.UPN_Label.Visible = False
      Else
            Me.cmdUPN.Caption = "Hide UPN"
            Me.UPN.Visible = True
            Me.UPN_Label.Visible = True
      End If

End Sub

The button caption changes to Show UPN. Click again to restore the field and its header label.

In theory, you could add similar buttons for each of the 22 fields but the form would be very 'busy'.
That would also require a lot of similar code and be very inefficient.

The second method improves on that approach by adding an event procedure to the UPN header label double click event:

CODE:

Private Sub UPN_Label_DblClick(Cancel As Integer)

      Me.UPN.Visible = Not Me.UPN.Visible
      Me.UPN_Label.Visible = Me.UPN.Visible
     Me.cmdUPN.Caption = "Show UPN"
      Me.cmdShowAll.Enabled = True

End Sub

The code hides the UPN column and its header label. The Show All Columns button becomes enabled. Click the button to restore the column

The third method provides a generic solution that works for all columns in can be used in any continuous form. This is by far the preferred approach.
The module modAccessibility contains the following code

CODE:

Option Compare Database
Option Explicit

'Uses accessibility code suggested by Karl Donaubauer and adapted from https://nolongerset.com/acchittest/

Private Type POINTAPI
     x As Long
      y As Long
End Type

Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" _
      ByRef lpPoint As POINTAPI) As Long

'==================================================

Function HideSelectedColumn(frm As Form)

'Colin Riddington - 3 Jan 2024

'Uses name of object (header label) from accHitTest. Could instead use caption or position
'This only works if the related field control name can be derived from the label name
'Currently fails with err 5 on secondary monitor with negative co-ordinates (to left of primary monitor)

On Error GoTo Err_Handler

      Dim pt As POINTAPI
      Dim accObject As Object
      Dim strText As String, strName As String

      GetCursorPos pt

      Set accObject = frm.AccHitTest(pt.x, pt.y)

      strName = accObject.Name       'label name


      'The following assumes labels named using default Access method e.g. Surname_Label, Gender_Label
      strText = Left(strName, InStr(strName, "_") - 1)       'corresponding control name

      'Modify as necessary
      'e.g. if using e.g. lblSurname, lblGender etc ...
      'strText = Mid(accObject.Name, 4)

      ' If control being hidden was sorted, it has the focus. Move focus to prevent error
     frm.cmdClose.SetFocus

      If Not accObject Is Nothing Then
            frm(strName).Visible = False
            frm(strText).Visible = False
            End If

      frm.cmdShowAll.Enabled = True

Exit_Handler:

      Exit Function

Err_Handler:
      'Fails with err 5 on secondary monitor with negative co-ordinates (to left of primary monitor)
      If Err = 5 Then MsgBox "This code cannot be used on a secondary monitor to the left of a primary monitor", vbCritical, "Code failed"
      Resume Exit_Handler

End Function

The above code is a slightly modified version of that used in several recent articles and is fully explained in the article:
      Streamlining the Multiselect Filter code in a Continuous Form

The AccHitTest function uses accessibility code to determine the name of the header label clicked and, from that, the name of the corresponding textbox field control.
Both items are then hidden and the Show All Records button again becomes visible.

The function is run from the On Dbl Click event of the property sheet for all column labels.


As a result, you can hide as many columns as you wish with no additional code. For example, in the next screenshot, all six % attendance fields have been hidden.


The built-in form sort and filter features can be used successfully in conjunction with hiding selected fields.
Filtered columns are shaded GREEN. For more details, see my article: Highlight Filtered Columns


NOTE:
a)   Hiding columns leaves blank space on the form as the remaining columns are not automatically moved across to fill the gaps.
      In addition, although all columns can be restored, individual columns can not be made visible.
      Both these issues will be addressed in the second part of this article: Hide and restore selected columns

b)   Scrolling the form horizontally also highlights a further issue in that the data in the first three columns on the left of the form disappear off screen.


      This issue will also be addressed in a forthcoming article: Freeze columns



More Continuous Form Examples

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



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