"Cy" <go********@computunity.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:
I've had good luck with posting in the past, so we'll see how
things go this time...:)
I have an Access 2000 form. On the form is about 40 objects. I
want to set everything to invisible, except a drop down box. Once
the user selects from the pull down menu, I have code that picks
the applicable fields to be visible.
Thus, if a user makes changes to the pull down menu, ie. changes
the value in the pull down, I need everything to go back to being
invisible and a new set of objects appear.
Any ideas on how to set all 40 objects to invisible without having
to call each individual object in code?
I'm obviously not getting the joke that the others are engaging in,
so I'll actually take you seriously and answer your question.
The best way to change a group of controls is to loop through the
form's controls collection and set properties of those controls.
You'll need some way of identifying which controls you want to act
upon, so I usually use the .Tag property. However, if you want all
controls except the one combo box, you could select on controltype
and name. That would look something like this:
Dim ctl As Control
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox _
Or ctl.ControlType = acTextBox) _
And ctl.Name <> "MyComboBox" Then
ctl.Visible = True
End If
Set ctl = Nothing
Next ctl
Now, you could wrap that in a subrouting that you pass a Boolean
argument that controls whether you're revealing or hiding:
Private Sub ShowHideControls(bolShow As Boolean)
Dim ctl As Control
For Each ctl In Me.Controls
If (ctl.ControlType = acComboBox _
Or ctl.ControlType = acTextBox) _
And ctl.Name <> "MyComboBox" Then
ctl.Visible = bolShow
End If
Next ctl
Set ctl = Nothing
End Sub
Then you could call that in the AfterUpdate event of your combo box
thus:
Call ShowHideControls(Not IsNull(Me!MyComboBox))
That would reveal everything as long as the combo box is not null,
and hide everything when it's Null.
Now, if you used the .Tag property instead, it would look like this:
Private Sub ShowHideControls(bolShow As Boolean)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "ShowHide" Then
ctl.Visible = bolShow
End If
Next ctl
Set ctl = Nothing
End Sub
Now, walking the entire controls collection every time you do this
is actually a pretty slow operation, so for something like this, I
always use a custom collection. You populate that collection in the
OnLoad event of the form, and then use the collection for
showing/hiding. To populate the collection:
Dim ctl As Control
If (mcolShowHide Is Nothing) Then
Set mcolShowHide = New Collection
For Each ctl In Me.Controls
If ctl.Tag = "ShowHide" Then mcolShowHide.Add ctl, ctl.Name
Next ctl
Set ctl = Nothing
End If
One point: mcolShowHide should be declared at the form's module
level:
Private mcolShowHide As Collection
Now, you also might wonder why I'm checking for Is Nothing in the
form's OnLoad event. It's obviously not necessary there, but I
included it because it's better to put this routine in its own
subroutine so you can call it before using the collection (so that
you're sure the collection has been initialized). So, that would
look something like this:
Private Sub InitializeShowHideCollection()
Dim ctl As Control
If (mcolShowHide Is Nothing) Then
Set mcolShowHide = New Collection
For Each ctl In Me.Controls
If ctl.Tag = "ShowHide" Then mcolShowHide.Add ctl, ctl.Name
Next ctl
Set ctl = Nothing
End If
End Sub
Now, you'd call that in the OnLoad event as:
Call InitializeShowHideCollection
Now, to use it, you'd define your ShowHideControls subroutine as
this:
Private Sub ShowHideControls(bolShow As Boolean)
Dim varCtl As Variant
Dim ctl As Control
Call InitializeShowHideCollection
For Each varCtl In mcolCollection
Set ctl = varCtl
ctl.Visible = bolShow
Next varCtl
Set ctl = Nothing
End Sub
You'll find that this is *much* faster noticeably so, than walking
the entire controls collection because there are a lot more controls
on the form than you think (remember that every label, every command
button, every line, every box is a control), and the time it takes
to test for the name or tag or ControlType accumulates when you loop
through that many items.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
