Enumerate through subform fields

[Guest]

Hi all. I am trying to run through fields on several subforms on a tabbed
control. The object is to try and find and count the tagged fields.

I am able to enumerate the controls on the main form and find out if a
control is a subform however, this is where i get stuck trying then to run
through the controls on the subform I have just identified. This is what I
have so far.

Dim i As Integer, j As Integer
Dim frm As Form

Set frm = Forms!frmInspection (This form has several subforms on a
tabbed control)
For i = 0 To frm.Count - 1
If frm(i).ControlType = acSubform Then
HELP!! I'M STUCK AS TO WHAT TO DO HERE!!
I want to find the field that have a .tag value of "CountMe" and update the
field to hold the count.
End If
Next i
Any and all help is greatly appreciated.
Thanks,
Barry

 

[Marshall Barton]

Hi all. I am trying to run through fields on several subforms on a tabbed
control. The object is to try and find and count the tagged fields.

I am able to enumerate the controls on the main form and find out if a
control is a subform however, this is where i get stuck trying then to run
through the controls on the subform I have just identified. This is what I
have so far.

Dim i As Integer, j As Integer
Dim frm As Form

Set frm = Forms!frmInspection (This form has several subforms on a
tabbed control)
For i = 0 To frm.Count - 1
If frm(i).ControlType = acSubform Then
HELP!! I'M STUCK AS TO WHAT TO DO HERE!!
I want to find the field that have a .tag value of "CountMe" and update the
field to hold the count.
End If
Next i

Here's one way:

Dim ctl As Control, sfm As Form
Set sfm = frm(i).Form
For Each ctl In sfm.Controls
If ctl.Tag = "???" Then
. . .
End If
Next ctl

 

[Guest]

Dear Marshall:
Thanks for the response. If I cut and paste your code directly into where I
have HELP!! I receive and error object does not support this property or
method on Set sfm = frm(i).Form. My code currently looks like this:

Dim i As Integer, j As Integer
Dim frm As Form

Set frm = Forms!frmInspection
For i = 0 To frm.Controls.Count - 1
If frm(i).ControlType = acPage Then
Dim ctl As Control, sfm As Form
Set sfm = frm(i).Form
For Each ctl In sfm(i).Controls
If ctl.Tag = "1" Then
j = j + 1 'Increment counter
sfm(i)!CountUsed = j 'Update field on form that holds
the count
End If
Next ctl
End If
Next i

Thanks for your help.

Barry

 

[RoyVidar]

Hi all. I am trying to run through fields on several subforms on a
tabbed control. The object is to try and find and count the tagged
fields.

I am able to enumerate the controls on the main form and find out if
a control is a subform however, this is where i get stuck trying
then to run through the controls on the subform I have just
identified. This is what I have so far.

Dim i As Integer, j As Integer
Dim frm As Form

Set frm = Forms!frmInspection (This form has several subforms on
a tabbed control)
For i = 0 To frm.Count - 1
If frm(i).ControlType = acSubform Then
HELP!! I'M STUCK AS TO WHAT TO DO HERE!!
I want to find the field that have a .tag value of "CountMe" and
update the field to hold the count.
End If
Next i
Any and all help is greatly appreciated.
Thanks,
Barry

Do you have to use index? I think the For Each Next construct is more
efficient. Could you use some of this?

Dim frm As Form
Dim sfrm As Form
Dim ctl As Control
Dim sctl As Control

Set frm = Forms("frmInspection")

For Each ctl In frm.Controls
If ctl.ControlType = acSubform Then
Set sfrm = ctl.Form

' putting info immediate pane
Debug.Print
Debug.Print sfrm.Name
Debug.Print

For Each sctl In sfrm.Controls
' not looping all controltypes
Select Case sctl.ControlType
Case acTextBox, acComboBox

' putting info immediate pane
Debug.Print sctl.Name
If sctl.Tag = "CountMe" Then
' do your counting
End If
End Select
Next sctl
End If
Next ctl

Set sctl = Nothing
Set sfrm = Nothing
Set ctl = Nothing
Set frm = Nothing

 

[Guest]

Roy:
This is most excellent and I have learned much from your example.
Thank you very much.
Barry

 

[Marshall Barton]

Thanks for the response. If I cut and paste your code directly into where I
have HELP!! I receive and error object does not support this property or
method on Set sfm = frm(i).Form. My code currently looks like this:

Dim i As Integer, j As Integer
Dim frm As Form

Set frm = Forms!frmInspection
For i = 0 To frm.Controls.Count - 1
If frm(i).ControlType = acPage Then
Dim ctl As Control, sfm As Form
Set sfm = frm(i).Form
For Each ctl In sfm(i).Controls
If ctl.Tag = "1" Then
j = j + 1 'Increment counter
sfm(i)!CountUsed = j 'Update field on form that holds
the count
End If
Next ctl
End If
Next i

You changed the ControlType code from what you had before.
Change it back. If you were concerned about the Tab
control, don't be. It has no effect on the form's Controls
collection.

You are itterating through some unknown control in the sfm
object, remove the index.

I don't think you are setting the wrong control to the
count.

Unless you feel more comfortable using the index into the
Controls collection, I agree with Roy that the For Each
construct is more intuitive.

While not necessary, it is customary to place all Dim
statements at the top of the procedure.

Incorporating all that with some cleanup:

Dim ctl As Control, sctl As Control
Dim j As Integer

For Each ctl In Forms!frmInspection
If ctl.ControlType = acSubform Then
For Each sctl In ctl.Form.Controls
If sctl.Tag = "1" Then
j = j + 1 'Increment counter
End If
Next sctl
frm!CountUsed = j 'Update count field on main form
End If
Next ctl

I think I might have lost track of the CountUsed control. I
thought it was on the main form, but maybe you have separate
ones on each subform??

 

[Guest]

Dear Marshall:
The advice from both you and Roy has been most informative and helpful.
Just so you know, I have a control on each subform to hold a count. The
application is used to create an inspection report for automobiles. Each
subform has one part of the inspection ie, under hood, interior, exterior,
road check etc. and each subform is weighted so that "trunk" is not as
important as "road test". I did add some code to find out if any of the
tagged controls had a value of zero or N/A as the user sees it in the combo.
This was to insure that the average was not affected if the user had some
fields that were N/A. All in all it seems to be working well. There are also
some unbound controls on each subform to add up all the selections and then
divide by the CountUsed found in the code. Then the average score is divided
by the weight in percent. All the "WeightedScores" are summed to give the
overall score of the inspection.

Again, thanks so much for all the help. I learned alot, especially about
using for next.

Barry