Looping through controls on form and subforms to return control la

[Lesli]

I am helping a co-worker with a database that is using a main form with a tab
control with a subform on each tab. She needs to be able to loop through the
controls on the form and each subform to check for missing data and return a
list to the user. I have the basic code to loop through the main form and
return the ctl.Name, but what we really need is to return the label because
the name isn't going to be very helpful to the user. We tried putting text
in the Status Bar Text field to see if we could return that, but didn't have
any luck. The plan is to get the list and then display it in an unbound
control that users can reference (rather than a message box). I'm using the
debug.print line to test the concatenation.

Here's the basic code that I'm starting with. It works. (I know the actually
validating of the field values isn't included in the code yet. RIght now,
I'm just trying to return a list that will be useful to the user).

Dim myList, ctlName As String

For Each ctl In Me.Controls
ctlName = ctl.Name
If ctl.Tag = "Validate" Then
myList = myList & ctlName & " : "
End If
Next

Debug.print myList

So, I have two questions:
1. How can I return the label or the Status Bar Text or something else
useful instead of the control name?
2. How do I loop through all the subforms and concatenate those lists to the
main form list?

Thanks in advance for your help!
Lesli

 

[Klatuu]

A couple of ideas come to mind. The most straight forward way would be to
change the Tag property from Validate to the value you want to display and
only include those controls where Tag has a value. An uninitialized Tag
property is a null string "". So your code could be:

Dim myList As String
Dim ctlName As String
Dim ctl As Control

For Each ctl In Me.Controls
ctlName = ctl.Name
If ctl.Tag <> vbNullString Then
myList = myList & ctl.Tag & " : "
End If
Next ctl

Also note the change I made in your Dim statemtents. As written:
Dim myList, ctlName As String

myList is dimmed as a variant. You have to define the type of each
variable. Dim does not type all variables the same for a line. If a
specific type is not defined, it defaults to Variant.

To include your subforms, you will need to identify the subform control on
the main form and include the Form property of the subform control:

For Each ctl In Me.subSub1.Form.Controls

The other way, if you need to use the Tag for another purpose, would be to
use a naming convention for your controls so you you can reference the label
for a control based on the control's name. For example, I use prefixes such
as txt for Text Boxes, cbo for Combo Boxes, chk for Check Boxes, etc. But,
it doesn't matter what the prefix is, because all Labels are prefixed with
lbl.

Using that method, you can modify the name in your code to get the Caption
value of the associated label:

Dim myList As String
Dim ctlName As String
Dim strCaption As String
Dim ctl As Control

For Each ctl In Me.Controls
ctlName = ctl.Name
strCaption = "lbl" & Mid(ctl.Name, 4)
If ctl.Tag <> vbNullString Then
strCaption = "lbl" & Mid(ctl.Name, 4)
myList = myList & strCaption & " : "
End If
Next ctl

Now, I don't know how you plan to display the list, but a good visual
presentation can be achieved by using a List Box, and populating it usint a
variation on the code above. Make the Row Source Type property of the List
Box a Value List. Then in your code, you can use the AddItem method of the
list box to add the items.:

Dim ctl As Control

'Remove the existing list
Me.lstMissing.RowSource = vbNullString

For Each ctl In Me.Controls
If ctl.Tag <> vbNullString Then
Me.lstMissing.AddItem "lbl" & ctl.Tag
End If
Next ctl
Set ctl = Nothing

 

[Lesli]

Great info Dave! Thanks for a very detailed response. I think we'll try the
first scenario and see if that works. I'll let you know.

Thanks again,
Lesli

 

[Lesli]

It worked! That is so cool. We ended up using the text box and the whole
think worked without a hitch. Thank you VERY much for your help and quick
response. You've saved us lots of work. And we learned some neat tricks too.

Thanks again,
Lesli