Help with hiding column(s)

A

alex

Using Access ’03…

With the help of an article from Michael Kaplan, I have the following
code that will hide/unhide columns on a subform:

Private Sub Form_Load()
Dim ctl As Control
Dim stCtl As String

Set sfrm = Me.sfrmHideShowColumns
For Each ctl In Me.Controls
If TypeOf ctl Is Access.CheckBox Then
stCtl = "tb" & Mid$(ctl.Name, 3)
ctl.Value = Not sfrm.Form(stCtl).ColumnHidden
End If
Next ctl
End Sub
---------------------------------------------------------------------------------
Private Function ShowHideColumn()
Dim sfrm As SubForm
Dim ctl As Control
Dim stCtl As String

Set sfrm = Me.sfrmHideShowColumns
For Each ctl In Me.Controls
If TypeOf ctl Is Access.CheckBox Then
stCtl = "tb" & Mid$(ctl.Name, 3)
sfrm.Form(stCtl).ColumnHidden = _
Not ctl.Value
End If
Next ctl
End Function

This code, however, relates a checkbox to a specific textbox/column
(both named the same). I need code such that when a checkbox is
checked, >1 column is hidden/unhidden. In addition, I’d like a
checkbox to hide/unhide all columns. I cannot seem to modify the code
in such a way to do this. I basically need to relate a checkbox to
multiple textboxes.

Any help would be appreciated.

alex
 
D

Douglas J. Steele

In the AfterUpdate event of the first checkbox (the one for only a few
columns), use something like:

Private Sub Checkbox1_AfterUpdate
Dim sfrm As SubForm

Set sfrm = Me.sfrmHideShowColumns
sfrm.Form.Controls("NameOfColumn1").ColumnHidden = _
Not Me.Checkbox1.Value
sfrm.Form.Controls("NameOfColumn2").ColumnHidden = _
Not Me.Checkbox1.Value

End Sub

In the AfterUpdate event of the second checkbox (for all columns), use
something like:

Private Sub Checkbox2_AfterUpdate
Dim sfrm As SubForm
Dim ctl As Control

Set sfrm = Me.sfrmHideShowColumns
For Each ctl In sfrm.Form.Controls
ctl.ColumnHidden = _
Not Me.Checkbox2.Value
Next ctl

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Using Access ’03…

With the help of an article from Michael Kaplan, I have the following
code that will hide/unhide columns on a subform:

Private Sub Form_Load()
Dim ctl As Control
Dim stCtl As String

Set sfrm = Me.sfrmHideShowColumns
For Each ctl In Me.Controls
If TypeOf ctl Is Access.CheckBox Then
stCtl = "tb" & Mid$(ctl.Name, 3)
ctl.Value = Not sfrm.Form(stCtl).ColumnHidden
End If
Next ctl
End Sub
---------------------------------------------------------------------------------
Private Function ShowHideColumn()
Dim sfrm As SubForm
Dim ctl As Control
Dim stCtl As String

Set sfrm = Me.sfrmHideShowColumns
For Each ctl In Me.Controls
If TypeOf ctl Is Access.CheckBox Then
stCtl = "tb" & Mid$(ctl.Name, 3)
sfrm.Form(stCtl).ColumnHidden = _
Not ctl.Value
End If
Next ctl
End Function

This code, however, relates a checkbox to a specific textbox/column
(both named the same). I need code such that when a checkbox is
checked, >1 column is hidden/unhidden. In addition, I’d like a
checkbox to hide/unhide all columns. I cannot seem to modify the code
in such a way to do this. I basically need to relate a checkbox to
multiple textboxes.

Any help would be appreciated.

alex
 
A

alex

In the AfterUpdate event of the first checkbox (the one for only a few
columns), use something like:

Private Sub Checkbox1_AfterUpdate
Dim sfrm As SubForm

    Set sfrm = Me.sfrmHideShowColumns
    sfrm.Form.Controls("NameOfColumn1").ColumnHidden = _
                       Not Me.Checkbox1.Value
    sfrm.Form.Controls("NameOfColumn2").ColumnHidden = _
                       Not Me.Checkbox1.Value

End Sub

In the AfterUpdate event of the second checkbox (for all columns), use
something like:

Private Sub Checkbox2_AfterUpdate
Dim sfrm As SubForm
Dim ctl As Control

    Set sfrm = Me.sfrmHideShowColumns
    For Each ctl In sfrm.Form.Controls
      ctl.ColumnHidden = _
                       Not Me.Checkbox2.Value
    Next ctl

End Sub

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Using Access ’03…

With the help of an article from Michael Kaplan, I have the following
code that will hide/unhide columns on a subform:

Private Sub Form_Load()
    Dim ctl As Control
    Dim stCtl As String

    Set sfrm = Me.sfrmHideShowColumns
    For Each ctl In Me.Controls
        If TypeOf ctl Is Access.CheckBox Then
            stCtl = "tb" & Mid$(ctl.Name, 3)
            ctl.Value = Not sfrm.Form(stCtl).ColumnHidden
        End If
    Next ctl
End Sub
---------------------------------------------------------------------------­------
Private Function ShowHideColumn()
    Dim sfrm As SubForm
    Dim ctl As Control
    Dim stCtl As String

    Set sfrm = Me.sfrmHideShowColumns
    For Each ctl In Me.Controls
        If TypeOf ctl Is Access.CheckBox Then
            stCtl = "tb" & Mid$(ctl.Name, 3)
            sfrm.Form(stCtl).ColumnHidden = _
                       Not ctl.Value
        End If
    Next ctl
End Function

This code, however, relates a checkbox to a specific textbox/column
(both named the same).  I need code such that when a checkbox is
checked, >1 column is hidden/unhidden.  In addition, I’d like a
checkbox to hide/unhide all columns.  I cannot seem to modify the code
in such a way to do this.  I basically need to relate a checkbox to
multiple textboxes.

Any help would be appreciated.

alex
Click to expand...

Doug,

Your first sub worked like a charm!

The second sub gives me a run-time error of 438...Object doesn't
support this property or method.
it doesn't seem to like: ctl.ColumnHidden = _
Not Me.Checkbox2.Value

I'll keep trying to debug it!

alex
 
D

Douglas J. Steele

Sorry, my fault. There will be labels on the subform that can't be hidden,
plus I referred to the subform incorrectly.

Try:

Private Sub Checkbox2_AfterUpdate
Dim sfrm As SubForm
Dim ctl As Control

For Each ctl In Me.sfrmHideShowColumns.Form.Controls
If TypeOf ctl Is Label Then
' Do nothing
Else
ctl.ColumnHidden = Not Me.Checkbox2.Value
End If
Next ctl

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the AfterUpdate event of the first checkbox (the one for only a few
columns), use something like:

Private Sub Checkbox1_AfterUpdate
Dim sfrm As SubForm

Set sfrm = Me.sfrmHideShowColumns
sfrm.Form.Controls("NameOfColumn1").ColumnHidden = _
Not Me.Checkbox1.Value
sfrm.Form.Controls("NameOfColumn2").ColumnHidden = _
Not Me.Checkbox1.Value

End Sub

In the AfterUpdate event of the second checkbox (for all columns), use
something like:

Private Sub Checkbox2_AfterUpdate
Dim sfrm As SubForm
Dim ctl As Control

Set sfrm = Me.sfrmHideShowColumns
For Each ctl In sfrm.Form.Controls
ctl.ColumnHidden = _
Not Me.Checkbox2.Value
Next ctl

End Sub
Click to expand...

Doug,

Your first sub worked like a charm!

The second sub gives me a run-time error of 438...Object doesn't
support this property or method.
it doesn't seem to like: ctl.ColumnHidden = _
Not Me.Checkbox2.Value

I'll keep trying to debug it!

alex
 
A

alex

Sorry, my fault. There will be labels on the subform that can't be hidden,
plus I referred to the subform incorrectly.

Try:

Private Sub Checkbox2_AfterUpdate
Dim sfrm As SubForm
Dim ctl As Control

  For Each ctl In Me.sfrmHideShowColumns.Form.Controls
    If TypeOf ctl Is Label Then
' Do nothing
    Else
      ctl.ColumnHidden = Not Me.Checkbox2.Value
    End If
  Next ctl

End Sub

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)











Doug,

Your first sub worked like a charm!

The second sub gives me a run-time error of 438...Object doesn't
support this property or method.
it doesn't seem to like:  ctl.ColumnHidden = _
                       Not Me.Checkbox2.Value

I'll keep trying to debug it!

alex- Hide quoted text -

- Show quoted text -
Click to expand...

Nice work Doug!
Thanks for your help.
alex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide Controls on Subform 2
problem with ColumnHidden 5
Assign string to a Control 13
Control on Form 3
Reuse varialbe in sucessive Case statements 1
Value From ComboBox On UserForm 3
For...Next statement woes 3
block Form 3

Top