Hide a column programmatically in MS-Access

Question

I want to hide or show a column based on variable data from a users selection. How do you set a column to hidden in MS-Access 2003?

For Example,

After user change event...

For Each ctl In Me.FormNameHere.Form.Controls
    If (TypeName(ctl) = "Textbox") Then
        If InStr(GetTextList(), ctl.Name) > 0 Then
            ctl.hidden = True
        Else
            ctl.hidden = False
        End If
    End If
Next ctl

• What is the best approach to this type of challenge?
• Is there a more obvious solution?

Answer 1

Hi, I answered a similar question to this not long ago to do with hiding columns on a datasheet. However you seem to want to hide textboxes arranged in a column on a form, is that correct?

Iterating over all the controls in the form could be slow if you have many controls. If you really need to use textboxes like that, then you could try group the 'columns' together then hide the groups. Another approach would be to use a listbox or datasheet to represent the data, where you can alter the layout of the columns directly.

Answer 2

Controls do not have a "hidden" property (no objects in Access have a hidden property). They do have a .Visible property.

For future reference, I suggest you familiarize yourself with the Object Browser in the VBE -- open the VBE and hit F2. You can then restrict your search to the individual libraries used in your project. It does take a while to get to the point where you understand the object model, though.

Also, you can rely on Intellisense to learn the properties/methods of an object, so in the code of the form you're working with, you can type "Me.MyTextBox." and the Intellisense dropdown will show you all the properties and methods of that particular control. It doesn't work for a generic control variable (as in your code) because different control types have different properties.

And, of course, the properties sheet gives the names of the properties, even though in code they don't always use the same orthography (usually they are the same with spaces removed).

Also, there are differences in how you might want to do this depending on whether it's a regular form or a datasheet form. In a datasheet, your controls also have .ColumnHidden and .ColumnWidth properties (setting those in any view other than datasheet view has no effect, and neither of those properties are available in the standard property sheet, but changes to them are retained when you save the form).

Answer 3

I found the ColumnHidden property does the trick.

For Each ctl In Me.FormNameHere.Form.Controls
    If (TypeName(ctl) = "Textbox") Then
        If InStr(GetTextList(), ctl.Name) > 0 Then
            ctl.Columnhidden = True
        Else
            ctl.Columnhidden = False
        End If
    End If
Next ctl

I got a hint from this related question.