Subform total on a main form

[JustJim]

I must have Friday-itis!

I have a main form which has a sub-form. The sub form is shown as a datasheet, but in design view I have a control on the Form Footer which totals a field on the Detail area. This works fine for the subform when displayed by itself as a single form (including applied filters to simulate being used on the main form) and naturally the total field doesn't appear when the sub-form is shown as a datasheet.

On the main form there is a control which refers to the sub-form and to the total control in the following way

[Code=txt]
=sfrmSubFormNam e!txtTotalContr olName
[/Code]

and it just sits there empty! No #ERROR, no #NAME?, no nuthin'!

The spelling of the sub-form name and control is correct, I had two other people look at it.

I've done this a million times (Hyperbole alert!), perhaps I shouldn't have had that last glass of red while watching the cricket last night!

Drat! I'm actually going to have to submit this... I was hoping that just typing it out clearly would make something go Click! in my (admittedly fuzzy) brain, but no, not yet.

Jim

[ADezii]

I must have Friday-itis!

I have a main form which has a sub-form. The sub form is shown as a datasheet, but in design view I have a control on the Form Footer which totals a field on the Detail area. This works fine for the subform when displayed by itself as a single form (including applied filters to simulate being used on the main form) and naturally the total field doesn't appear when the sub-form is shown as a datasheet.

On the main form there is a control which refers to the sub-form and to the total control in the following way

[Code=txt]
=sfrmSubFormNam e!txtTotalContr olName
[/Code]

and it just sits there empty! No #ERROR, no #NAME?, no nuthin'!

The spelling of the sub-form name and control is correct, I had two other people look at it.

I've done this a million times (Hyperbole alert!), perhaps I shouldn't have had that last glass of red while watching the cricket last night!

Drat! I'm actually going to have to submit this... I was hoping that just typing it out clearly would make something go Click! in my (admittedly fuzzy) brain, but no, not yet.

Jim

Try a change in syntax:
[CODE=vb]=Me!sfrmSubForm Name.Form!txtTo talControlName[/CODE]

[JustJim]

Try a change in syntax:
[CODE=vb]=Me!sfrmSubForm Name.Form!txtTo talControlName[/CODE]

Well Access is paying attention, that gets me a #Name? error. I even thought to really tell it what I wanted by adding
[CODE=vb]=Me!sfrmSubForm Name.Form!txtTo talControlName. Value[/CODE]
to no avail.

Interestingly, when I leave out the .Value in the Control Source box of the Properties window, Access leaves it exactly as I typed it but with the .Value it puts [ and ] around all the bits
[CODE=vb]=[Me]![sfrmInvoice].[Form]![txtInvoiceSubTo tal].[Value][/CODE]
Strange animal!

Jim

[ADezii]

Well Access is paying attention, that gets me a #Name? error. I even thought to really tell it what I wanted by adding
[CODE=vb]=Me!sfrmSubForm Name.Form!txtTo talControlName. Value[/CODE]
to no avail.

Interestingly, when I leave out the .Value in the Control Source box of the Properties window, Access leaves it exactly as I typed it but with the .Value it puts [ and ] around all the bits
[CODE=vb]=[Me]![sfrmInvoice].[Form]![txtInvoiceSubTo tal].[Value][/CODE]
Strange animal!

Jim

The problem is not Value, since it is the Default Property of a Control and does not ned to be explicitly stated. If I had to venture a guess, it would be that you are referencing the Sub-Form itself and NOT the Sub-Form Control, namely the syntax should be:
[CODE=vb]
=Me!<Sub-Form Control Name>.Form![txtInvoiceSubTo tal]
NOT
=Me!<Sub-Form Name>.Form![txtInvoiceSubTo tal][/CODE]

[JustJim]

The problem is not Value, since it is the Default Property of a Control and does not ned to be explicitly stated. If I had to venture a guess, it would be that you are referencing the Sub-Form itself and NOT the Sub-Form Control, namely the syntax should be:
[CODE=vb]
=Me!<Sub-Form Control Name>.Form![txtInvoiceSubTo tal]
NOT
=Me!<Sub-Form Name>.Form![txtInvoiceSubTo tal][/CODE]

No, I knew it wasn't the .Value, I just thought the formating that Access did was interesting.

OK I deleted the subform control on the form and put it back. The actual form I'm using is called sfrmInvoice and it has a control on it's footer called txtInvoiceSubTo tal and its Control Source is
[CODE=vb]
=Sum([InvoiceAmount])[/CODE]
where InvoiceAmount is a control on the Detail of sfrmInvoice. In Single Form view, this control adds up all the InvoiceAmounts and if I filter the form, it adds up just those InvoiceAmounts. So far, so good.

On the main form, the sub-form is called sfrmInvoiceCont rol and there is a text box on the main form called txtInvoiceTotal whose Control Source is now
[CODE=vb]
=[Me]![sfrmInvoiceCont rol].[Form]![txtInvoiceSubTo tal][/CODE]

This gives me a #NAME? error!

Cheers

Jim

[JustJim]

No, I knew it wasn't the .Value, I just thought the formating that Access did was interesting.

OK I deleted the subform control on the form and put it back. The actual form I'm using is called sfrmInvoice and it has a control on it's footer called txtInvoiceSubTo tal and its Control Source is
[CODE=vb]
=Sum([InvoiceAmount])[/CODE]
where InvoiceAmount is a control on the Detail of sfrmInvoice. In Single Form view, this control adds up all the InvoiceAmounts and if I filter the form, it adds up just those InvoiceAmounts. So far, so good.

On the main form, the sub-form is called sfrmInvoiceCont rol and there is a text box on the main form called txtInvoiceTotal whose Control Source is now
[CODE=vb]
=[Me]![sfrmInvoiceCont rol].[Form]![txtInvoiceSubTo tal][/CODE]

This gives me a #NAME? error!

Cheers

Jim

Sometimes it's not about Me!
[CODE=vb]=sfrmInvoiceCon trol.Form!txtIn voiceSubTotal[/CODE]

Jim