Show / Hide Columns Easily in Datasheet

[MajP]

Recently I had to have a lot of visible fields on the form because each column was part of a detailed calculation. In order to validate the data I had to do a lot of comparison between different columns. So the datasheet needed to present a lot of columns, but that is difficult to process all that information and work with the different columns. I had a few forms like this. With a datasheet this is real easy. I load a list box with all the controls for a given form. To make this work well give every control a readable name (like Last Name not text123). Then you can show or hide as many columns as needed. The nice thing about this is you can do this on any form with very little code. I found this functionality very useful in viewing and working with the data. I have been incorporating this in many dbs.

Here is the complete code for the demo form.

Private Sub Form_Load()
  LoadListBox lstChoose, Me.subFrmEmployees.Form
End Sub

Private Sub frameShowHide_AfterUpdate()
  Select Case frameShowHide
   Case 1
     ShowHideAll Me.lstChoose, False
   Case 2
     ShowHideAll Me.lstChoose, True
  End Select
  ShowSelections lstChoose, Me.subFrmEmployees.Form
End Sub

Private Sub lstChoose_AfterUpdate()
  ShowSelections Me.lstChoose, Me.subFrmEmployees.Form
End Sub

All the procedures are in the module.

Public Sub listSelectUnselect(lst As Access.ListBox, Clear As Boolean)
  Dim i As Integer
  For i = 0 To lst.ListCount - 1
    lst.Selected(i) = Clear
  Next i
End Sub

Public Sub LoadListBox(ByVal lst As Access.ListBox, ByVal frm As Access.Form)
   Dim ctrl As Access.Control
   Dim ct As Long
   For Each ctrl In frm.Controls
     ct = ctrl.ControlType
     If ct = acTextBox Or ct = acComboBox Or ct = acCheckBox Then
       If ctrl.Visible Then
         ctrl.ColumnHidden = False
         lst.AddItem ctrl.Name
       Else
         ctrl.ColumnHidden = True
       End If
     End If
   Next ctrl
 End Sub

Public Sub ShowSelections(ByVal lst As Access.ListBox, ByVal frm As Access.Form)
  On Error Resume Next
  Dim itm As Variant
  Dim i As Integer
   'show all first
   showColumns lst, frm
  ' Hide selected
   For Each itm In lst.ItemsSelected
    frm.Controls(lst.ItemData(itm)).ColumnHidden = True
  Next itm
End Sub

Public Sub showColumns(ByVal lst As Access.ListBox, ByVal frm As Access.Form)
   On Error Resume Next
  Dim itm As Variant
  Dim i As Integer
  For i = 0 To lst.ListCount - 1
    If frm.Controls(lst.ItemData(i)).Visible Then
      frm.Controls(lst.ItemData(i)).ColumnHidden = False
    Else
      frm.Controls(lst.ItemData(i)).ColumnHidden = True
    End If
  Next i
End Sub

Public Sub HideColumns(ByVal lst As Access.ListBox, ByVal frm As Access.Form)
  ' On Error Resume Next
   Dim itm As Variant
  Dim i As Integer
  Dim ctl As Access.Control
  For i = 0 To lst.ListCount - 1
    Set ctl = frm.Controls(lst.ItemData(i))
    ctl.ColumnHidden = True
  Next i
End Sub

Public Sub ShowHideAll(lst As Access.ListBox, Show As Boolean)

  Dim i As Integer
  For i = 0 To lst.ListCount - 1
    lst.Selected(i) = Not Show
  Next i
End Sub

 

[arnelgp]

you can also do the same without any coding by right-clicking on the Column header of the datasheet to hide/unhide the column (field).

 

[isladogs]

You can indeed show/hide columns using the datasheet context menu. However, this provides an alternative method that may be more convenient if you have a lots of columns.
I'm also wondering whether the approach may be of use to someone at Utter Access forum who wants to select a datasheet column programmatically in order to spell check that column only. although its easy to select a column by clicking its header, there appears to be no code that does the same thing - surprisingly acCmdSelectEntireColumn fails

For info, I also have an example app which adds functionality to hide/show columns in a continuous form by double clicking the header label

Hide & Restore Selected Columns

This article and example app demonstrates how to hide and restore selected columns in a continuous form. The form can also be sorted and filtered with all filtered fields highlighted in green and sorted columns in yellow.

www.isladogs.co.uk

Video here:

 

[skymou]

For a datasheet, there is also the built-in command to hide and unhide columns:
RunCommand acCmdUnhideColumns

Steve