MajP
You've got your good things, and you've got mine.
- Local time
- Today, 10:28
- Joined
- May 21, 2018
- Messages
- 8,992
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.
All the procedures are in the module.
Here is the complete code for the demo form.
Code:
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.
Code:
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