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.
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.
Private Sub cmdUPN_Click()
If Me.cmdUPN.Caption = "Hide UPN" Then
cmdUPN.Caption = "Show UPN"
Me.UPN.Visible = False
Me.UPN_Label.Visible = False
Me.cmdUPN.Caption = "Hide UPN"
Me.UPN.Visible = True
Me.UPN_Label.Visible = True
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:
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
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
Option Compare Database
'Uses accessibility code suggested by Karl Donaubauer and adapted from https://nolongerset.com/acchittest/
Private Type POINTAPI
x As Long
y As Long
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
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
If Not accObject Is Nothing Then
frm(strName).Visible = False
frm(strText).Visible = False
frm.cmdShowAll.Enabled = True
'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"
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
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)
• Freeze columns (2 pages)
• Move columns
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