Referencing subform controls

[J Welsby]

Trying to 'see' the values in controls in a subform. Have created some code
like the following:
Dim ctlC As Control
For Each ctlC In frm.Controls
If TypeOf ctlC Is SubForm Then
MsgBox ctlC.Name
End If
Next ctlC

This code is attached to the close event of a form and will return (via the
msgbox) the names of each of the subforms on the form.

But how do I get it to return the values in text boxes, combo boxes etc. on
each subform? Am having difficulty in creating this code

Pls be gentle - newbie to VBA! Thanks

 

[Allen Browne]

That's a fairly serious question if you are not really comfortable with VBA
yet.

You have code that loops through all the controls on the form. Some controls
(text boxes, combos, ...) have a Value; others do not (labels, command
buttons, ...) Some controls sometimes do, but not others (e.g. option
buttons do if they are not part of an option group.) The code therefore
needs to handle different controls differently, test if they have a Value,
and if so report on it.

When you do find a subform, are you wanting the code to loop through all the
controls on the subform as well? And if the subform has a subform of its
own, you want to loop through those and continue as far as the possible 7
levels of nesting? If so, you are talking about recursive code (code that
calls itself to handle further instances.) It's quite feasible, but it's not
really beginner stuff.

If you want an example of a function that loops through all the controls on
the form, determines whether they have a Control Source, and handles
subforms recursively, see:
Locking bound controls
at:
http://allenbrowne.com/ser-56.html
The purpose is different (setting the Locked property), and it checks
controls for a Control Source (which should give identical results to
checking their value.) So, in the middle of the procedure, you will want to
replace:
ctl.Locked = bLock
with:
Debug.Print ctl.Name, ctl.Value