An interesting little problem! As Steve suggested, you can do this with a Combobox, but being a Datasheet View Form, you can't use a Command Button, so we'll just use the AfterUpdate event of the Combobox.
Assuming that all of the Fields are present, on the Form:
- Add a Combobox to your Form
- Name it FieldListCombo
- Set the Row Source to the Form’s underlying Table/Query
- Set the Row Source Type to Field List
Now place this code in the AfterUpdate event of the Combobox:
Code:
Private Sub FieldListCombo_AfterUpdate()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Name = Me.FieldListCombo Then
If ctl.ColumnHidden Then
ctl.ColumnHidden = False
Else
ctl.ColumnHidden = True
End If
End If
Next
Me.FieldListCombo = ""
End Sub
When you select a Field from the Combobox, it will change/toggle its Hidden state…if it’s currently Hidden, it will Unhide it, and vice versa, so you can show a column then hide it.
Because the Combobox was added last, it will always be the right-most Column. And Access will ask the user, when closing the Form, if they want to save the changes to the design, so they can either leave the Columns as they were changed, or return to the original state.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007