Conditionally hide columns in Access 2003 Code

[Kay]

I have code which unhides the columns on a subform based on a selection made
in the main form. It works fine, but now I need to do the same thing but
include two columns. I need to hide both TargetAudience and ProductMarketed.
Can I use "and" or do I have to reference the other control independently.

I am not including all of the code but just what I need to change I think.
Dim frm As Form
Dim ctl As Control
Dim ctlLbl As Control
Dim ctlHld As Control

Set ctl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!ProductMarketed
Set ctlLbl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!ProductMarketed_Label

 

[Marshall Barton]

I have code which unhides the columns on a subform based on a selection made
in the main form. It works fine, but now I need to do the same thing but
include two columns. I need to hide both TargetAudience and ProductMarketed.
Can I use "and" or do I have to reference the other control independently.

I am not including all of the code but just what I need to change I think.
Dim frm As Form
Dim ctl As Control
Dim ctlLbl As Control
Dim ctlHld As Control

Set ctl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!ProductMarketed
Set ctlLbl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!ProductMarketed_Label

You need to do each one separately. The With statement may
be used to make the lines shorter/faster/easier to read.
Without seeing the rest of your code, I can't be sure, but I
think this may be all you need:

With Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form
!ProductMarketed.Visible = (t/f expression)
!ProductMarketed_Label = (t/f expression)
!TargetAudience.Visible = (t/f expression)
!TargetAudience_Label = (t/f expression)
End With

Note that if the label is attached to the other control, its
visibility will automatically mirror the other control so
you would not need the ...label lines.

 

[Kay]

Marshall,
Thanks and you are right, I should have posted the entire code. I did try
to set it up separately using my approach, but it finds the second line and
not the first. ODD. So here is the entire code.
Option Compare Database

Public Sub HideProductColOnPmntFrm(blnHide As Boolean)
Dim frm As Form
Dim ctl As Control
Dim ctlLbl As Control
Dim ctlHld As Control

Set ctl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!ProductMarketed
Set ctlLbl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!ProductMarketed_Label
Set ctl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!TargetAudience
Set ctlLbl = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!TargetAudience_Label

'This sets the focus
Set ctlHld = Forms!frmPaymentEntryforDC![sbfEventsDC
subform].Form![sbfDCEventDatails subform].Form!TradeSecret
'Set ctl = frm!Form!PaymentDetailType
ctlHld.SetFocus
ctl.Visible = Not blnHide
ctlLbl.Visible = Not blnHide
End Sub