Multiple Subform Syntax HELP!

[splaisance]

I am stuck on a problem and can't quite find the answer.

I have a database that consists of a main form and multiple subforms.

ClientViewer = mainform
sfViwer = subform
sfPlView = subform
sfHeaders = subform
sfRevenue = subform
sfCost = subform

The database opens the mainform which consists of the sfviewer. Sfviewer's sourceobject is set to sfplview which is another subform that contains subforms sfheaders, sfrevenue, sfcost, etc.

What I need to do is determine how many columns (unbound text boxes) are displayed on the subforms sfheaders, sfrevenue, sfcost, etc based on information queried from tables based on a value choosen on the ClientViewer mainform.

I've tried numerous variations of syntax but always get error messages.

Can anyone please help???


Thanks!

Shannon

 

[SBendBuckeye]

Use the collections to help you get what you need. Try this:
Forms("ClientViewer&quot("sfHeaders&quot.Form.Controls.Count

Make sure that ClientViewer is the actual name of your form and that sfHeaders, etc. are the names of the SubForm container controls on the main form, NOT the name of the subform itself. The main form subform container will hold an actual form, that is why you can reference its controls as you do above.

If you need to actually count specific controls do something like the following:

Dim ctl As Control
Dim intCounter As Integer

With Forms("ClientViewer&quot("sfHeaders&quot.Form
For Each ctl in .Controls
Debug.Print ctl.Name, etc so you can see it work
If .ControlType = vbTextBox ' or check for 109
intCounter = intCounter + 1
End If
Next ctl
End With

The with just allows you to avoid a lot of ugly qualifications and as a side benefit makes your code run faster. You can just as easily say

For Each ctl In _
Forms("ClientViewer&quot("sfHeaders&quot.Form.Controls

Hope this helps and Good Luck!

PS. You can find the above type things if you view the locals window me object while in single step mode. Just substitute me. for Forms("ClientViewer&quot. If you've never done that before, it can be a little bit overwhelming. Just look at a couple things and then later look at a few more things. I would recommend you start with the Form.Controls area, that is where you can see all of the controls you have places on your form. Enjoy!

 

[splaisance]

Thanks for responding.

I tried the suggestion using
For Each ctl In Forms("ClientViewer&quot("sfRevenue&quot.Form.Controls.Count
' the above being all on one line
Debug.Print ctl.Name
Next ctl

I get an error message that it can't find "sfRevenue".

If I add in the subform control of ("sfViewer&quot (which is found on the clientviewer form), then I get an error message that the command is in error because it refers to an object that doesn't exist or is closed.

I mostly code in VB 6.0 so I've used 'me' and 'with' syntax before (and it's a great tip for ppl who haven't used it!).
I am doing this code in a general module and not behind the main form so I can't use the 'me'.

I've checked the names and they are correct. I just can't seem to get it to work right

The database was originally in 97. However, accessing these subforms didn't work with the original syntax when I converted it to 2000. Here is the original Access 97 syntax:
Set sf = Forms!ClientViewer!sfViewer.Form(rs1!sfName).Form

Where sf is dimmed as a form and rs1!sfName contains the names of the different subform names to be viewed simultanouesly (ie: sfheaders, sfrevenue, etc).
Perhaps this will help someone point me in the right direction? My assumption is that something in DAO changed between the 97 and 2000 forms of it causing the syntax to no longer be acceptable.


Thanks!

Shannon

 

[SBendBuckeye]

Remove the .Count from the end of your For Each expression. That is one of the properties of the collection.

For Each ctl In
Forms("ClientViewer&quot("sfRevenue&quot.Form.Controls

The first statement in my reply was to get it directly without spinning the controls collection. Sorry if it confused the issue!

Dim intCounter As Interger

intCounter =
Forms("ClientViewer&quot("sfHeaders&quot.Form.Controls.Count

Good Luck!

 

[splaisance]

Silly me! I should have caught that.


Unfortunately, it still gives the same error.


Shannon

 

[SBendBuckeye]

Make sure you are using the name of the subform container on the main form, not the name of the subform itself. If you click the properties of the container on the main form it is the name under the other tab.

I am using Access2000 and have tested it a couple times as pertains to this thread. Also, sometimes when you drag a form on to another form to create a subform, even though it works just fine, the name field we are talking about here is a null string. You might want to check that out also to make sure that the name exists.

If that still doesn't work, view the locals window in single step mode and create the pieces one aat a time. How many levels are your subforms nested? Your original code looks like it might be 2 levels deep. The example I gave you is only one level deep.

Good Luck!

 

[splaisance]

I've double checked the name of the container and I am referencing the correct name. I think part of the problem is that the code is 4 levels deep. Mainform, subform container, subform, multiple subforms.

The programmer who created the database is known to have done convoluted stuff that Access97 would allow but Access2000 won't. And of course he is not here anymore. I am attempting to use his formatt on this database because the users like the way it looks/uses but want it to show correct data

I'll play some more with debuging and such - If you have any other ideas/suggestions please post em!


Thanks!

 

[SBendBuckeye]

Can you post the hierarchy of form1, subform container1, subform1, subform1 subform container2, etc? I know you can only nest subforms 2 levels deep.

Also, you might try breaking the process down a little bit to make it simpler. Can you get the syntax to work for referencing a control on the first level subform as we have been discussing? Sometimes a series of smaller steps will get you there just as well.

Good Luck!