Object loop help

[pib311 via AccessMonster.com]

Hello all,

Thanks in advance.

I am using the following code to run through all forms in a field and hide
values based on a combo box.

For Each ctl In Me.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Or ctl.ControlType = acLabel Or ctl.ControlType = acListBox) And _
(ctl.Tag Like "*attribute*") = True Then

ctl.Visible = False

ElseIf (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Or ctl.ControlType = acLabel Or ctl.ControlType = acListBox) And _
(ctl.Tag Like "*project*") = True Then

ctl.Visible = True

End If

Next ctl

I want to make all fields null that will be forced to be invisible, basically
clearing any values they may have entered. I have tried ctl.value = false
with no luck, and set ctl = Null. What is the easiest way to accomplish
clearing out any existing values?

Thanks!

 

[Arvin Meyer [MVP]]

The value property is the default property, so you don't usually need to
use:

ctl.Value

simply use:

ctl

Assuming that the datatype is a string, you set them empty by:

ctl = ""

or if a variant or number, use:

ctl = Null

 

[Dirk Goldgar]

The value property is the default property, so you don't usually need to
use:

ctl.Value

simply use:

ctl

Assuming that the datatype is a string, you set them empty by:

ctl = ""

or if a variant or number, use:

ctl = Null

Note, though, that label controls don't have a value, so setting them to any
value will fail. The OP should either test to exclude label controls from
the value-setting, or else use On Error Resume Next to ignore the error.

 

[Arvin Meyer [MVP]]

Note, though, that label controls don't have a value, so setting them to
any value will fail. The OP should either test to exclude label controls
from the value-setting, or else use On Error Resume Next to ignore the
error.

The default property of a label is the Caption property, which can also be
set to an empty string ( "" ). So if one uses the default property instead
of .Value (or .Caption) the code will work just fine.

 

[Dirk Goldgar]

The default property of a label is the Caption property, which can also be
set to an empty string ( "" ). So if one uses the default property instead
of .Value (or .Caption) the code will work just fine.

You're right, Arvin; I didn't think of that! The code won't fail ... but it
won't give the desired result, since when the label control is made visible
again its caption will have been cleared.

 

[Arvin Meyer [MVP]]

You're right, Arvin; I didn't think of that! The code won't fail ... but
it won't give the desired result, since when the label control is made
visible again its caption will have been cleared.

I did wonder why the OP was even using the labels for data. I did that on
one application that was used on a kiosk to keep users from interacting with
textboxes, but that was a very unusual situation.

 

[David W. Fenton]

Assuming that the datatype is a string, you set them empty by:

ctl = ""

or if a variant or number, use:

ctl = Null

If it's bound to a text field with ZLS OFF (as should be the case
99% of the time), wouldn't it be proper to set it to Null?

And controls don't have datatypes themselves -- they are only
"typed" when bound to a field in a table.

I would never use "" to empty a control, to be honest.

And, of course, it's more efficient to use vbNullString because
using "" requires allocating memory for the ZLS, whereas the memory
for the constant is already allocated. It makes little difference
for a single line of code, but if you're looping, it could be
significant. I think it's a good habit to use vbNullString in all
cases so that you never have a problem in the cases where it really
*does* make a difference.

 

[David W. Fenton]

I want to make all fields null that will be forced to be
invisible, basically clearing any values they may have entered. I
have tried ctl.value = false with no luck, and set ctl = Null.
What is the easiest way to accomplish clearing out any existing
values?

For Each ctl in Me.Controls
ctl.Visible = (ctl.Tag Like "*attribute*")
If ctl.Visible Then
Select case ctl.ControlType
Case acTextBox, acComboBox, acListBox
ctl = Null
Case acCheckBox
ctl = False
Case acOptionGroup
ctl = ctl.DefaultValue
End Select
End If
Next ctl
Set ctl = Nothing

It also does occur to me that if you set the default value of your
Nullable controls to "Null" and of your checkboxes to "False" then
you wouldn't have to test for ControlType at all, and you'd have:

For Each ctl in Me.Controls
ctl.Visible = (ctl.Tag Like "*attribute*")
If ctl.Visible Then
ctl = ctl.DefaultValue
End If
Next ctl
Set ctl = Nothing

(I actually didn't know you could use Like with wildcards for string
comparisons -- I've always use InStr() which is much more
complicated, and gets you no extra functionality except when you
need to know exactly *where* the string is located)