Assign string to a Control

[alex]

Assign string to control

Hello,
Using Access ‘03

I’m attempting to convert a string name to a control name…Here’s the
code:

Private Sub Form_Current()

Dim ctl As Control
For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then 'omit labels
If IsNull(ctl) Then 'if no value
Dim strLabel As String
strLabel = "Me.lbl" & (ctl.Name)
strLabel.Visible = True ‘does not work!
'Debug.Print (strLabel)
Else
End If
End If

Next ctl
End Sub

All of my labels have an “lbl” prefix. If the ctl is null, I’d like
to concatenate the “lbl” in front of (ctl.name) and hide the label.
It seems to work (at least the name) in the immediate window.

I can’t get Access to recognize the control, however.

Keep in mind that my labels are NOT linked to my textboxes et al. I
did this because I place the label on top of the textbox to save
space. If the textbox is empty, the label is displayed. If not, the
textbox value appears. This only seems to work when the label and
texbox are not linked.

Thanks,
alex

 

[Dirk Goldgar]

Try this:

'------ start of revised code ------
Private Sub Form_Current()

Dim ctl As Control

For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then 'omit labels
Me.Controls("lbl" & ctl.Name).Visible = Not IsNull(ctl)
End If

Next ctl

End Sub
'------ end of revised code ------

That should hide the label if the control is null, and hide the label if it
isn't.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


Assign string to control

Hello,
Using Access ‘03

I’m attempting to convert a string name to a control name…Here’s the
code:

Private Sub Form_Current()

Dim ctl As Control
For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then 'omit labels
If IsNull(ctl) Then 'if no value
Dim strLabel As String
strLabel = "Me.lbl" & (ctl.Name)
strLabel.Visible = True ‘does not work!
'Debug.Print (strLabel)
Else
End If
End If

Next ctl
End Sub

All of my labels have an “lbl” prefix. If the ctl is null, I’d like
to concatenate the “lbl” in front of (ctl.name) and hide the label.
It seems to work (at least the name) in the immediate window.

I can’t get Access to recognize the control, however.

Keep in mind that my labels are NOT linked to my textboxes et al. I
did this because I place the label on top of the textbox to save
space. If the textbox is empty, the label is displayed. If not, the
textbox value appears. This only seems to work when the label and
texbox are not linked.

Thanks,
alex

 

[John Spencer]

Try
strLabel = "lbl" & ctl.Name
Me.Controls(strLabel).Visible = True

You might run into errors since
== the control might be a line , box , button, etc and not have a value
property (Line: If IsNull(ctl) Then)
== the control might not have an associated label (Line, Box, button)
== If the control is a combobox or a list the value might be null, but you
might not want to hide the label.

Also, as you move from record to record you might want to show/hide the label
so you should have something that hides or shows the labels instead of just
showing the label.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

'------ start of revised code ------
Private Sub Form_Current()

Dim ctl As Control
'To ignore any errors that this code can generate
'you can add the following line to Dirk's code
On Error Resume Next

For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then 'omit labels
Me.Controls("lbl" & ctl.Name).Visible = Not IsNull(ctl)
End If

Next ctl

End Sub
'------ end of revised code ------


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

'------ start of revised code ------
Private Sub Form_Current()

Dim ctl As Control
'To ignore any errors that this code can generate
'you can add the following line to Dirk's code
On Error Resume Next

For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then 'omit labels
Me.Controls("lbl" & ctl.Name).Visible = Not IsNull(ctl)
End If

Next ctl

End Sub
'------ end of revised code ------

Good idea, John.

Interestingly enough, I was concerned about what would happen in the case of
controls with no Value property, but I found that IsNull(ctl) returns False
for them and doesn't raise an error. I was surprised at that; my guess is
that they have a different default property. However, the code should still
guard against the possibility that there is no corresponding label, so the
"On Error Resume Next" statement is still recommended.

 

[Duane Hookom]

I would select all of the controls that have a label and set their tag to
"lbl".
Then use code like:

Private Sub Form_Current()

Dim ctl As Control
Dim strLabel As String

For Each ctl In Me.Controls
If Instr(ctl.Tag,"lbl") > 0 Then
strLabel = "lbl" & (ctl.Name)
Me(strLabel).Visible = IsNull(ctl)
End If
Next ctl
End Sub

 

[alex]

'------ start of revised code ------
Private Sub Form_Current()

    Dim ctl As Control
'To ignore any errors that this code can generate
'you can add the following line to Dirk's code
    On Error Resume Next

    For Each ctl In Me.Controls
        If Not TypeOf ctl Is Label Then 'omit labels
            Me.Controls("lbl" & ctl.Name).Visible = Not IsNull(ctl)
        End If

    Next ctl

End Sub
'------ end of revised code ------

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County











- Show quoted text -

Gentlemen,
The code seems to work well...
John,
You're right; I had to add the error handling because not all the
controls have the associated labels (yet!). The code was looking for
them. I should probably also change the code to look for only text or
combo boxes.
Thanks again,
alex

 

[David W. Fenton]

Try this:

'------ start of revised code ------
Private Sub Form_Current()

Dim ctl As Control

For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then 'omit labels
Me.Controls("lbl" & ctl.Name).Visible = Not
IsNull(ctl)
End If

Next ctl

End Sub
'------ end of revised code ------

That should hide the label if the control is null, and hide the
label if it isn't.

If you're trying to hide the label of a Null control, why not use
ctl.Controls(0).Visible, instead of depending on a naming
convention? I never name my labels anything other than the gibberish
default name, and access their properties via the control they are
attached to.

 

[David W. Fenton]

However, the code should still
guard against the possibility that there is no corresponding
label, so the "On Error Resume Next" statement is still
recommended.

For what it's worth, I would dissent on that. I just don't use On
Error Resume Next -- if I know an error can occur, I trap for the
specific errors I expect and ignore them. This allows an unexpected
error to be properly handled by my error handler.

 

[David W. Fenton]

Also, as you move from record to record you might want to
show/hide the label so you should have something that hides or
shows the labels instead of just showing the label

If you're doing this in the form's OnCurrent vent, I'd recommend a
custom collection for this, rather than walking the entire controls
collection of the form. In that case, you'd populate the collection
in the form's OnLoad, and then walk the collection in the OnCurrent
to set whatever you wanted to set. This would mean that you can use
the tag property, or whatever code conditions you want to pick out
the controls that you want in your collection that you're operating
on in the OnCurrent. Also, it makes it easy to set up multiple
collections for different purposes so that your conditional logic
can be much simpler.

It's also noticeably faster, even on forms with <100 controls. I was
surprised at this, but apparently, walking the controls collection
is not as efficient as looping a custom collection of smaller size.

If anyone's interested, I can repost sample code on how to
accomplish this.

 

[Dirk Goldgar]

If you're trying to hide the label of a Null control, why not use
ctl.Controls(0).Visible, instead of depending on a naming
convention?

Because, as the OP explained, the labels are not "attached" to their text
boxes, and hence are not members of the text boxes' Controls collections.
Maybe they ought to be, but the OP had problems with that approach that I
haven't attempted to investigate.

 

[John Spencer]

Actually, I got to thinking about why the poster is doing this. AND if the
reason is just to show a value if the control is null, the poster could use
the format property of the control to show a value for nulls.

For example:
If the control is bound to a text field the format property would be something
like:
@;"Missing Data"

If the control is bound to a number field.
#,###;-#,###;0;"Not Available"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[alex]

Actually, I got to thinking about why the poster is doing this.  AND ifthe
reason is just to show a value if the control is null, the poster could use
the format property of the control to show a value for nulls.

For example:
If the control is bound to a text field the format property would be something
like:
@;"Missing Data"

If the control is bound to a number field.
#,###;-#,###;0;"Not Available"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County









- Show quoted text -

That's a good idea John...it seems to work.
What I was simply trying to do was place a label on top of the textbox
to save space. I wanted to show the label when the textbox value was
null and hide the label when the textbox had a value.
alex

 

[David W. Fenton]

Because, as the OP explained, the labels are not "attached" to
their text boxes, and hence are not members of the text boxes'
Controls collections. Maybe they ought to be, but the OP had
problems with that approach that I haven't attempted to
investigate.

Ah. I missed that in the original post. That would be why, I guess,
he has to hide the labels explicitly, rather than hiding the
controls themselves.

I'd figure out why this isn't working and fix it before I spent time
on trying to hide the controls independently.