Version 1.6           Approx 1.2 MB (zipped)                 First Published 7 Feb 2024                 Last Updated 11 Mar 2024


The first part of this article demonstrated how you can hide selected columns in a continuous form by clicking on the header label.
The example app also allowed you to restore all columns on a button click.

However there were a couple of issues in the previous example:
1.   hiding columns left blank space on the form as the remaining columns were not automatically moved across to fill the gaps.
2.   individual columns could only be made visible if all columns were restored.

Both issues are addressed in this second article.



Download

Click to download:   HideRestoreSelectedColumns_v1.6     ACCDB file     Approx 1.2 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


As before, the form contains 22 fields and you will need to scroll horizontally to view all of them.

You can select any column to hide it by double clicking the header label. In the screenshot below the Unique Pupil Number (UPN) column has been hidden
All the columns to the right now move across automatically to fill the gap.


A combo box now appears with the name of the hidden column(s).
Click the column name in the combo box to restore the column. Once again, the other columns move automatically

The same process can be used with multiple columns. The next screenshot shows the result after hiding the six % attendance columns.


Next, three of those columns have been restored leaving the other three columns listed as hidden in the combo box..


The process of hiding and restoring columns can also be used in conjunction with sorting and filtering columns.
In this screenshot, nine columns have been hidden (including the PupilID column on the left), 3 columns filtered and the Surname column is reverse sorted.
Filtered columns are highlighted in GREEN and sorted columns in YELLOW.


Click the ShowAllColumns button to restore all columns. The sort and filters can be cleared separately by clicking the Remove Sorts and Clear Filters buttons.




How Does the Example App Work?

As before a single function is used to 'hide' the columns.
A new function ShrinkSelectedColumn (in modAccessibility) is used which is also based on the same accHitTest accessibility code.

However, in this case, the columns remain 'visible' but the width is reduced to just 1 twip (where 1440 twips = 1 inch.) so in reality they cannot be seen.

This is done so that the columns still occupy some space and therefore remain in the correct order when not visible.

The ShrinkSelectedColumn function is run from the double click event on the property sheet


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

'=============================================
'Colin Riddington 03/01/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)

'Updated v1.5 to shift remaining columns located to right of selected hidden column
'Shrink columns to width of 1 twip instead of hiding them
'This means they can be restored in their original position for reverting to original width
'=============================================

Function ShrinkSelectedColumn(frm As Form)

On Error GoTo Err_Handler

Dim pt As POINTAPI
Dim accObject As Object
Dim strText As String, strName As String
Dim ctl As Control
Dim lngOldWidth As Long

      GetCursorPos pt

      Set accObject = frm.AccHitTest(pt.X, pt.Y)

      strName = accObject.Name 'header label

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

      '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 cmdShowAll to prevent error
      frm.cmdShowAll.Enabled = True
      frm.cmdShowAll.SetFocus

     If Not accObject Is Nothing Then
           lngOldWidth = frm(strName).Width
            frm(strName).Width = 1 'label
            frm(strText).Width = 1 'corresponding control
      End If

      'update form appearance
      frm.cboHiddenColumns.Visible = True

      frm.lblFormInfo.Caption = "Double click any column header to hide that column." & _
           " Restore any hidden column to its original position by selecting it from the combobox." & _
            " In each case, the positions of all other columns are automatically shifted leaving no gaps."

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

      'CR - v1.4 05/01/2024 - based on a suggestion by Xevi Batlle
      'shift all columns located to right of selected column to leave no gaps
      'exclude controls with tag = "A"
      For Each ctl In frm.Controls
            If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox) And ctl.Tag <> "A" Then
                  If ctl.Left > frm(strName).Left Then
                        ctl.Left = ctl.Left - lngOldWidth + 1
                  End If
            End If
      Next
      '-------------------------------------------------------------

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

NOTE:
As in all cases with the accHitTest function, it currently fails when used on a secondary monitor placed on the left of the primary monitor i.e. with negative co-ordinates.
Microsoft are aware of this issue and it is expected to be fixed soon.

As a work-around, use only on the primary monitor or make the left monitor the primary display


My original plan for the new functionality was to determine the left and width properties for each column in the Form_Load event and then store that data in a table.
I could then use this data both for repositioning the remaining columns when a column is hidden and for restoring the columns as required.

I am very grateful to one of my regular readers, Xavier Battle, who suggested a much simpler approach based on using a dictionary to hold the data.
This is done in the Form_Open event:

'v1.4 - Code based on suggestion by Xevi Batlle
'Uses Dictionary to store column names, positions & widths

Private Sub Form_Open(Cancel As Integer)

      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

End Sub

After a column is hidden, the following code runs whenever the mouse is clicked in the cboHiddenColumns combo box.
On each occasion, this repopulates the combo box with a list of the names of all currently 'hidden' columns i.e. control width = 1

'v1.4 - Code based on suggestion by Xevi Batlle
'v1.5 - CR modified to use shrunken control width

Private Sub cboHiddenColumns_Enter()

      'empty the combo row source then repopulate it
      Me.cboHiddenColumns.RowSource = ""

      For Each ctl In Me.Controls
            If ctl.ControlType = acLabel And ctl.Width = 1 Then
                  Me.cboHiddenColumns.AddItem ctl.Name & ";" & Replace(ctl.Caption, vbCr, " ")
            End If
      Next

End Sub

Selecting any item in the combobox list restores that column width using the dictionary data.
It then repositions all columns to the right of the restored column maintaining the column order
It also removes the selected item from the combobox list as it is no longer 'hidden'.

'v1.4 - Code based on suggestion by Xevi Batlle
'v1.5 - CR - changed in line with new code ShrinkSelectedColumn

Private Sub cboHiddenColumns_AfterUpdate()

Dim controlName As String
Dim labelName As String

      If Not IsNull(Me.cboHiddenColumns) Then

            labelName = Me.cboHiddenColumns
            controlName = Left(labelName, Len(labelName) - 6)
      End If

      'CR v1.5 - empty & repopulate combo row source removing item just selected
      Me.cboHiddenColumns.RowSource = ""
      For Each ctl In Me.Controls
            If ctl.ControlType = acLabel And ctl.Name <> Me.cboHiddenColumns And ctl.Width = 1 Then
                  Me.cboHiddenColumns.AddItem ctl.Name & ";" & Replace(ctl.Caption, vbCr, " ")
            End If
      Next

      'restore label & control column widths
      Me(labelName).Width = dict(labelName & "_Width")
      Me(controlName).Width = dict(controlName & "_Width")

      'now restore column positions
      For Each ctl In Me.Controls
            If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox) And ctl.Tag <> "A" Then
                  If ctl.Left > Me(labelName).Left Then
                        ctl.Left = ctl.Left + Me(labelName).Width - 1
                  End If
            End If
      Next

      'modify form if all columns are now visible
      If Me.cboHiddenColumns.ListCount = 0 Then
            Me.cmdShowAll.SetFocus
            Me.cboHiddenColumns.Visible = False

            Me.lblFormInfo.Caption = "Double click any column header to hide that column." & _
                 " The positions of all other columns are automatically shifted leaving no gaps." & _
                  " Click the Show All Columns button to restore all hidden columns to their original positions"
      End If

End Sub

Finally the cmdShowAll button code also uses the dictionary data to restore the left and width properties of all columns

Private Sub cmdShowAll_Click()

      Me.cmdShowAll.Enabled = False

      'CR v1.4 - restore position code based on dictionary data
      For Each ctl In Me.Controls
            If (ctl.ControlType = acLabel Or ctl.ControlType = acTextBox) And ctl.Tag <> "A" Then
                  ctl.Left = dict(ctl.Name & "_Left")
                  ctl.Width = dict(ctl.Name & "_Width")
            End If
      Next

      'update form appearance
      Me.cboHiddenColumns.Visible = False
      Me.lblFormInfo.Caption = "Double click any column header to hide that column." & _
            " The positions of all other columns are automatically shifted leaving no gaps."

End Sub

That's all there is to it. The code used to create the dictionary data provides everything needed to move and restore each column either individually or all at once.

Very easy when you know how! Once again, thanks are due to Xevi Batlle for suggesting this approach

I already have ideas for how similar methods can be used for streamlining the code in several other applications
e.g. to store monitor size/position data in my Automatic Form Resizing example app instead of using a table.



UPDATE: 11 Mar 2024 - Video

I have created a video (9:29) for YouTube to demonstrate how selected columns can be hidden/restored in a continuous form.

You can watch the Hide / Restore 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

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 11 Mar 2024



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