Hiding columns in split form.

[cementblocks]

I'm trying to create a split form. I have created a checkbox in the header of the form that will hide/unhide a set of columns in the datasheet portion of the form. The code I am using to do so look like this:

Private Sub chkAddress_AfterUpdate()
Dim ctrl As Control
For Each ctrl In Me
If ctrl.Tag = "Address" Then ctrl.ColumnHidden = Not chkAddress
Next ctrl
End Sub

The code works executes properly but the columns don't get hidden. They remain visible, but I can not select a cell in them.
I'm hoping that there is a method that I need to call to make the datasheet part of the from refresh/update to actually remove the columns.

[llkhoutx]

You say

If ctrl.Tag = "Address" Then ctrl.ColumnHidden = Not chkAddress 

is a meaningless statement.

ctrl.ColumnHidden = Not chkAddress 

makes no sense whatsoever.

Maybe this is what you want:

If ctrl.Tag = "Address" Then
       ctl.visible=true
else
       ctl.visible = false
end if 

[cementblocks]

Actually my code works fine. It seems that there is a bug in Access.
chkAddress is the name of a checkbox on my form. The statement

ctrl.ColumnHidden = Not chkAddress

sets the value of the columnhidden property to the inverse value of the checkbox.

I've given up on the split form and did this with a subform instead.

[cksm4]

See https://www.accessforums.net/program...form-8499.html

[alfaista]

hello!

i am working on this same problem. it is very difficult to find any help on this one! :-)

i went thru seeting the ActiveDatasheet to have focus, setting the column width to 0, requering the control. all with setting the column hidden property. no luck. and yes, i repainted, refreshed, etc... controls, forms.

it appears this only gets updated from Form events.
i played and played with my code, and found that if i put all hide, reset, unhide code in Form Load, Form Current, and Form Unload, then it works great. calling the code from my dropdown boxes, when they changed, ran the code, but the form would never update. no matter how many repaints and refreshes i did. a bit of a bug i would say, but workable.

for those interested, here is my very simple code...

Private Sub Form_Current()

ReSetFields

End Sub

Private Sub Form_Load()

ReSetFields

End Sub

Private Sub Form_Unload(Cancel As Integer)

UnHideControls

End Sub

Sub ReSetFields()

Dim rst As Recordset
Dim fld As Field
Dim i As Integer
Dim strName As Variant

HideControls
Set rst = Me.RecordsetClone
i = 1
rst.MoveFirst
For Each fld In rst.Fields
If fld.Name Like "????" Then
strName = "lblYr" & i
Me.Controls(strName).Caption = fld.Name
Me.Controls(strName).Visible = True
strName = "yr" & i
Me.Controls(strName).ControlSource = fld.Name
Me.Controls(strName).Visible = True
Me.Controls(strName).ColumnHidden = False
Me.Controls(strName).Requery
i = i + 1
End If
Next fld
Me.Repaint


End Sub

Sub HideControls()
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Name Like "lblYr*" Then
ctl.Visible = False
ElseIf ctl.Name Like "yr*" Then
ctl.Visible = False
ctl.ColumnHidden = True
ctl.Requery
End If
Next ctl
End Sub

Sub UnHideControls()
Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Name Like "lblYr*" Then
ctl.Visible = True
ElseIf ctl.Name Like "yr*" Then
ctl.Visible = True
ctl.ColumnHidden = True
ctl.Requery
End If
Next ctl
End Sub

[ojw]

Hi everyone,

I have an existing splitform in which I wanted to hide a column on the datasheet. On_Load I call a small function to hide the column (ctrl.ColumnHidden) and then set the focus (ctrl.SetFocus). I set the focus otherwise it wasn't displaying a label in my header for some reason. If I right-click on any field name and unhide the column it remains unhidden even when it loads again (even though a msgbox says that ColumnHidden = true). Is there any way to control what gets listed in the Unhide menu and thus preventing users from unhiding the field?