Show Subform Totals On Main Form

[razorking]

This should be an easy one - just not for me I guess. I have Googled it but not finding the answer, or else it is not working for me for some reason. This is similar to a recent post of mine but slightly different:

I have a form (frmNewClaim) with a subform (frmClaimItems). The subform is for entering items and prices for each. I have a textbox on the subform that is used to enter a material price, it is called Value$. On the subform on the footer I have a calculated textbox (valsum) - =Sum([Value$]). On the main form I have a text box that I would like to display the value from the calculated control previously mentioned on the footer of the subform.

I tried this:
=[frmClaimItems].[Form]![valsum] But I just get the dreaded #NAME?

Tried a few other thing and had no success.

What do I need to do?

 

[Galaxiom]

Check you are using the name of the subform contol on the main form not the subform object it holds.

Otherwise try the full name:
[Forms]![frmNewClaim]![frmClaimItems].[Form]![valsum]

 

[razorking]

Otherwise try the full name:
[Forms]![frmNewClaim]![frmClaimItems].[Form]![valsum]

I tried this...still not working. Any other ideas?

 

[boblarson]

I tried this...still not working. Any other ideas?

Make sure you are using the right name. See the screenshot which shows which name to use:

 

[razorking]

Make sure you are using the right name. See the screenshot which shows which name to use:

I see - that does change things. But I am still unsuccessful. Possibly a personal problem but, I still need to figure this out.

Here is my form and subform (see attached image) the red is my syntax for the control on the main form where I would like to disply the subtotats from the text box on the subform footer

 

[boblarson]

Change the recordsource of your text box to:

=[SubfrmItems]![Form]![valsum]

 

[razorking]

Change the recordsource of your text box to:

=[SubfrmItems]![Form]![valsum]

Ugh, still not working. I think I am going to have to upload a stripped down version of the db and if anyone would be so kind as to take look that would be great.

 

[boblarson]

Ugh, still not working. I think I am going to have to upload a stripped down version of the db and if anyone would be so kind as to take look that would be great.

Sounds good.

 

[razorking]

OK, here is a stripped down version of the db. If you open the form - frmNewClaim you will see the form and subform. When I enter items on the bottom/subform I want a cumulative dollar value total to display on the main form in the field I have highlighted on the attached image file.

Please see attached mdb and image.

And Thanks again.

 

[boblarson]

Okay, amazing as it sounds - change this:

=[SubfrmItems]![Form]![valsum]

to this:

=[SubfrmItems].[Form]![valsum]

where it is a period in front of the [Form] part instead of the bang (!).

 

[razorking]

Yep, that's all it needed. My problem is I am way better at the overall concept of how to build a database to accomplish a task than I am at the fundamentals of sql, correct syntax and the like.

Anyway, I will have to figure out when to use a . versus a !

thanks!.

 

[boblarson]

Yep, that's all it needed. My problem is I am way better at the overall concept of how to build a database to accomplish a task than I am at the fundamentals of sql, correct syntax and the like.

Anyway, I will have to figure out when to use a . versus a !

thanks!.

Well, not to worry - it was my own syntax that was failing. You used what I initially gave you and I hadn't made the distinction either. But using the "builder" I gathered it correctly. See here for an example of what I did.

 

[Galaxiom]

Anyway, I will have to figure out when to use a . versus a !

The "dot operator" (.) precedes properties of an object.
The "bang operator" (!) precedes collections and members of a collection.

For example:
Forms!formname.Form!controlname
But never:
Forms!formname!Form!controlname

But where a property is the default (as in .Form) it can be left out:
Forms!formname!controlname

Indeed the full reference is really:
Forms!formname.Form!Controls!controlname
but everyone leaves out Controls because it is the default collection of the Form. Usage seems inconsistent. Some include .Form others don't but .Controls is rarely seen.

Occasionally you will also see:
Forms!formname.Form!Controls("controlname")
This is just another syntax.

Much of the confusing usage is because Access converts the dot to a bang as required.
Forms.formname.controlname
is interpreted as:
Forms!formname.Form!Controls!controlname

Since the autocomplete in the VBA designer only works with the dot operator some developers use the dot everywhere. Many don't really know what is going on and use the operators somewhat randomly.

To add to the confusion Access will move to alternative properties and collections when the named member cannot be found within the defaults.

Forms!formname.somename
will first try for the control:
Forms!formname.Form!Controls!somename
but will return the field from the RecordSet:
Forms!formname.RecordSet!Fields!somename
if it can't find a control by that name on the form.

Fields is the default collection of the RecordSet so this is the same:
Forms!formname.Recordset!somename

Note that is RecordSet not RecordSource which a property being the name of the object or an SQL statement forming the RecordSet of the form.

The form wizard names the bound controls the same as the fields the record source and in this case it usually doesn't really matter that they go by the same name.

However adding an unbound control with the same name as a field in the record source can cause real confusion. Some developers shun the wizard entirely to ensure that controls never have the same name as a recordset field even when bound.

Clear as mud?

 

[boblarson]

One clarification -

When using subforms, you need to use the .Form part to refer to some method or property of the subform because the syntax is to use the subform control that is on the main form and therefore you need to tell Access that you want something on the form itself instead of a method or property of the subform control itself.