473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Subform total on a main form

JustJim
407 Expert 256MB
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
5 2688
ADezii
8,834 Expert 8TB
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
407 Expert 256MB
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
8,834 Expert 8TB
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
407 Expert 256MB
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
407 Expert 256MB
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

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

Similar topics

1
by: Mary Ann | last post by:
I have a main form with 4 subforms. One of the subforms reflects other costs/miscellaneous charges that an account might be assessed. The main form includes a field with a total pulled from that...
0
by: Ellen Manning | last post by:
I've got an A2K form with a subform based on a query. This query has a checkbox and an amount field and returns records if checkbox is checked. I Dsum the amount field and display on the main...
1
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have...
5
by: Mark | last post by:
Hi all, I'm completely new to access and am trying to create a simple form/subform. The main form will include: employee_number, forename, surname, total_leave, leave_remaining (to be...
16
by: ken | last post by:
I have a formA and subformB subformB is a continous form with a txtTotal in form footer =Sum() This works fine as long as there are records in form but if form is null I get Error I would like...
1
by: lawton | last post by:
Source: this is an access 2003 question My knowledge level: reading books, internet, and trial & error; no formal training I'm trying to get a running sum of what's filtered in a subform which is...
2
by: post | last post by:
Sorry that this is rather basic. But I have tried the various suggestions given in this group and cannot get them to work. I have a main form, for simplicity called "Main". On it are two...
16
by: mlcampeau | last post by:
I'm fairly new to this so I'll try to be as clear as possible. I have a database that stores employee information and all job titles available. I have a main form that is based from a query and...
1
by: kickergirl | last post by:
I'm not sure my title actually describes my problem, but here it goes. I am creating a form to track account information for participants. Basically, a single participant can be offered up to...
7
AccessIdiot
by: AccessIdiot | last post by:
This is driving me nuts. I have an unbound textbox (txt_SumSize) in my subform that holds the total of a field (Debitage_Size_Quantity)seen in datasheet view (control source =Sum(). I have an...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.