By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Subform total on a main form

JustJim
Expert 100+
P: 407
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

Expand|Select|Wrap|Line Numbers
  1. =sfrmSubFormName!txtTotalControlName
  2.  
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
Sep 21 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
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

Expand|Select|Wrap|Line Numbers
  1. =sfrmSubFormName!txtTotalControlName
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. =Me!sfrmSubFormName.Form!txtTotalControlName
Sep 21 '07 #2

JustJim
Expert 100+
P: 407
Try a change in syntax:
Expand|Select|Wrap|Line Numbers
  1. =Me!sfrmSubFormName.Form!txtTotalControlName
Well Access is paying attention, that gets me a #Name? error. I even thought to really tell it what I wanted by adding
Expand|Select|Wrap|Line Numbers
  1. =Me!sfrmSubFormName.Form!txtTotalControlName.Value
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
Expand|Select|Wrap|Line Numbers
  1. =[Me]![sfrmInvoice].[Form]![txtInvoiceSubTotal].[Value]
Strange animal!

Jim
Sep 21 '07 #3

ADezii
Expert 5K+
P: 8,597
Well Access is paying attention, that gets me a #Name? error. I even thought to really tell it what I wanted by adding
Expand|Select|Wrap|Line Numbers
  1. =Me!sfrmSubFormName.Form!txtTotalControlName.Value
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
Expand|Select|Wrap|Line Numbers
  1. =[Me]![sfrmInvoice].[Form]![txtInvoiceSubTotal].[Value]
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:
Expand|Select|Wrap|Line Numbers
  1. =Me!<Sub-Form Control Name>.Form![txtInvoiceSubTotal]
  2.                      NOT
  3. =Me!<Sub-Form Name>.Form![txtInvoiceSubTotal]
Sep 21 '07 #4

JustJim
Expert 100+
P: 407
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:
Expand|Select|Wrap|Line Numbers
  1. =Me!<Sub-Form Control Name>.Form![txtInvoiceSubTotal]
  2.                      NOT
  3. =Me!<Sub-Form Name>.Form![txtInvoiceSubTotal]
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 txtInvoiceSubTotal and its Control Source is
Expand|Select|Wrap|Line Numbers
  1. =Sum([InvoiceAmount])
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 sfrmInvoiceControl and there is a text box on the main form called txtInvoiceTotal whose Control Source is now
Expand|Select|Wrap|Line Numbers
  1. =[Me]![sfrmInvoiceControl].[Form]![txtInvoiceSubTotal]
This gives me a #NAME? error!

Cheers

Jim
Sep 24 '07 #5

JustJim
Expert 100+
P: 407
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 txtInvoiceSubTotal and its Control Source is
Expand|Select|Wrap|Line Numbers
  1. =Sum([InvoiceAmount])
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 sfrmInvoiceControl and there is a text box on the main form called txtInvoiceTotal whose Control Source is now
Expand|Select|Wrap|Line Numbers
  1. =[Me]![sfrmInvoiceControl].[Form]![txtInvoiceSubTotal]
This gives me a #NAME? error!

Cheers

Jim
Sometimes it's not about Me!
Expand|Select|Wrap|Line Numbers
  1. =sfrmInvoiceControl.Form!txtInvoiceSubTotal
Jim
Sep 25 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.