Getting the sum from a subform

[zeppeki]

I am trying to get a sum from a subform to show within a calculated control. Currently, I am using this expression:
=[RentalUnit subform].[Form]![NumOfRes]
But when I view my form, it shows only 2 occupants as opposed to the 12 that is expected. How do I get the sum of the NumOfRes to display?

Thanks!

[ADezii]

I am trying to get a sum from a subform to show within a calculated control. Currently, I am using this expression:
=[RentalUnit subform].[Form]![NumOfRes]
But when I view my form, it shows only 2 occupants as opposed to the 12 that is expected. How do I get the sum of the NumOfRes to display?

Thanks!

'Here is code that I use to calculate values in a Sub-Form for a specific Item_ID
'in Inventory. This is a Calculated Control that exists in the Parent Form, For the
'Control Source Property of the Calculated Control.

=DSum("[Quantity]","tblLocSubLocQty","[MasterItemID]=" & Forms!frmHMAUMasterInventory!MasterItemID)

[AccessHelp32]

'Here is code that I use to calculate values in a Sub-Form for a specific Item_ID
'in Inventory. This is a Calculated Control that exists in the Parent Form, For the
'Control Source Property of the Calculated Control.

=DSum("[Quantity]","tblLocSubLocQty","[MasterItemID]=" & Forms!frmHMAUMasterInventory!MasterItemID)

ADezii,

I had the same problem. I don't understand your code above. What is "tblLocSubLocQt y"?

[ADezii]

ADezii,

I had the same problem. I don't understand your code above. What is "tblLocSubLocQt y"?

A Table that stores various Inventory values

[AccessHelp32]

A Table that stores various Inventory values

So [Quantity] and [MasterItemID] are fields in Table that stores various Inventory Values?

My ultimate question is: I have a subform attached to a main form in which both forms are linked through a Project ID field. When I change the Project ID field on the main form, only the records associated with the Project ID are shown on the subform.

I would like to sum a field in the subform and display that sum on the main form (perhaps in the footer). However, I want the sum of the subform fields to be only for the Project ID field shown.

Thoughts?

[alpnz]

So [Quantity] and snip ...
I would like to sum a field in the subform and display that sum on the main form (perhaps in the footer). However, I want the sum of the subform fields to be only for the Project ID field shown.

Thoughts?

Have you considered using a listbox instead of a subform. And use the DSum aggregate function to find your value. It is quite a bit neater. This assumes you do not wish to edit anything in the subform.
What you can do is have another form called Edit "Sub date" E.g., which opens on a Double Click event for each line in the listbox. I realise this is going of topic, however the listbox does look neater on the form, and indeed is easy to name and refer to, if you wish to manipulate the list data.

DSum is the function you need to read up on to achieve the result you seek.

[AccessHelp32]

Have you considered using a listbox instead of a subform. And use the DSum aggregate function to find your value. It is quite a bit neater. This assumes you do not wish to edit anything in the subform.
What you can do is have another form called Edit "Sub date" E.g., which opens on a Double Click event for each line in the listbox. I realise this is going of topic, however the listbox does look neater on the form, and indeed is easy to name and refer to, if you wish to manipulate the list data.

DSum is the function you need to read up on to achieve the result you seek.

Unfortunately, I need to edit the subform.

I'm trying the following code:

DSum("[Source Code subform].[Form]![Circulation]", "[Source Code subform]", "[Source Code subform].[Form]![Camp Sched Proj ID] = Forms![Campaign Schedule]![Camp Sched Proj ID] = [Source Code subform].[Form]!Camp Sched Proj ID")

I've added the additional "=" because Access requests for one. I'm really lost here.

[alpnz]

Unfortunately, I need to edit the subform.

I'm trying the following code:

DSum("[Source Code subform].[Form]![Circulation]", "[Source Code subform]", "[Source Code subform].[Form]![Camp Sched Proj ID] = Forms![Campaign Schedule]![Camp Sched Proj ID] = [Source Code subform].[Form]!Camp Sched Proj ID")

I've added the additional "=" because Access requests for one. I'm really lost here.

A lookup like DSum usually takes the form of
DSum("[the field you want summed]", "[In the table]", "[Where the field in the table equals]" -- "[An Object on the form you are referring to]")

So

DSum("[qty]","widgets","[widgetid] =" & Forms!widgetview![widgetid])

Would give you the sum of [qty] in the widgets table where [widgetid] in the table equals [widgetid] on the form widgetview.
If widgetview is a subform then it is referred to as Forms!wadget.wi dgetview![widgerid].

Now Mary and many of the others that frequebt this site will quickly correct my syntax, as I am very adept at getting the quotes wrong etc, but try this and see what you get. Obviously insert your field and form names etc for your database.

[AccessHelp32]

A lookup like DSum usually takes the form of
DSum("[the field you want summed]", "[In the table]", "[Where the field in the table equals]" -- "[An Object on the form you are referring to]")

So

DSum("[qty]","widgets","[widgetid] =" & Forms!widgetview![widgetid])

Would give you the sum of [qty] in the widgets table where [widgetid] in the table equals [widgetid] on the form widgetview.
If widgetview is a subform then it is referred to as Forms!wadget.wi dgetview![widgerid].

Now Mary and many of the others that frequebt this site will quickly correct my syntax, as I am very adept at getting the quotes wrong etc, but try this and see what you get. Obviously insert your field and form names etc for your database.

I'm sorry to be a pest about this, but it doesn't work. I get a syntax error "Expected:= "

In any event, the exact code I'm using is:

DSum("[Circulation]","Source Code subform","[Camp Sched Proj ID] =" & Forms![Campaign Schedule]![Camp Sched Proj ID])

.

Is there something wrong with the code? The spaces in my form names? The fact I'm using a Subform? I've wrecked my brain here.

[alpnz]

I'm sorry to be a pest about this, but it doesn't work. I get a syntax error "Expected:= "

In any event, the exact code I'm using is:

DSum("[Circulation]","Source Code subform","[Camp Sched Proj ID] =" & Forms![Campaign Schedule]![Camp Sched Proj ID])

.

Is there something wrong with the code? The spaces in my form names? The fact I'm using a Subform? I've wrecked my brain here.

OK here is an example from a database I am working on.

=DSum("[Count]","PakTrak","[PalletID] =" & [Forms]![frm_pcardboxed]![PalletID])

Some things you will notice straight away. I do not have fields with multiple word names, And it is referring just to a form, not a subform, You should also note the use of bracketing, and quote marks, I was a bit rough the first attempt at answering this.

So what we need to do is sort out some values.

1. Is [Circulation] a field in a Table. What is the name of the table.
2. If So, is it a Number, not a text field.
3. Is [Source Code Subform] a Table
4. Is [Camp Sched Proj ID] a field in that table.
5. Forms![Campaign Schedule]![Camp Sched Proj ID] does not refer to a subform, it is referring to a Form called [Campaign Schedule]
6. What Form is [Campaign Schedule] a subform of.

If we can discover what each object above is, it will help decipher how the code should look.

[AccessHelp32]

OK here is an example from a database I am working on.

=DSum("[Count]","PakTrak","[PalletID] =" & [Forms]![frm_pcardboxed]![PalletID])

Some things you will notice straight away. I do not have fields with multiple word names, And it is referring just to a form, not a subform, You should also note the use of bracketing, and quote marks, I was a bit rough the first attempt at answering this.

So what we need to do is sort out some values.

1. Is [Circulation] a field in a Table. What is the name of the table.
2. If So, is it a Number, not a text field.
3. Is [Source Code Subform] a Table
4. Is [Camp Sched Proj ID] a field in that table.
5. Forms![Campaign Schedule]![Camp Sched Proj ID] does not refer to a subform, it is referring to a Form called [Campaign Schedule]
6. What Form is [Campaign Schedule] a subform of.

If we can discover what each object above is, it will help decipher how the code should look.

Great Thanks!
[*] [Circulation] is a field in a subform called [Source Code subform]
[*] [Source Code subform] is derived from a Table called [Source Code], so yes "Source Code subform" is a Table.
[*] [Camp Sched Proj ID] is a field in the [Source Code subform]/Table.
[*] [Campaign Schedule] is not a subform. It is a Main Form, and [Camp Sched Proj ID] is a field in [Campaign Schedule].

In short, [Campaign Schedule] is a Main Form and [Source Code subform] is a subform on the [Campaign Schedule] main Form.

Objective: Where Form and Subform [Camp Sched Proj ID] fields are equal, SUM the subform [Circulation] and print the value in a text field on the main form (possibly in the footer).

[AccessHelp32]

Great Thanks!
[*] [Circulation] is a field in a subform called [Source Code subform]
[*] [Source Code subform] is derived from a Table called [Source Code], so yes "Source Code subform" is a Table.
[*] [Camp Sched Proj ID] is a field in the [Source Code subform]/Table.
[*] [Campaign Schedule] is not a subform. It is a Main Form, and [Camp Sched Proj ID] is a field in [Campaign Schedule].

In short, [Campaign Schedule] is a Main Form and [Source Code subform] is a subform on the [Campaign Schedule] main Form.

Objective: Where Form and Subform [Camp Sched Proj ID] fields are equal, SUM the subform [Circulation] and print the value in a text field on the main form (possibly in the footer).

I just figured out the code! I typed:

=DSum("[Circulation]","Source Code","[Camp Sched Proj ID] = Forms![Campaign Schedule]![Camp Sched Proj ID]")

into the Control Source of the Text Box and it worked! Instead of referring to the [Source Code subform], I referred to the table "Source Code".

Thanks for the example Aplnz, it really helped.

[alpnz]

I just figured out the code! I typed:

=DSum("[Circulation]","Source Code","[Camp Sched Proj ID] = Forms![Campaign Schedule]![Camp Sched Proj ID]")

into the Control Source of the Text Box and it worked! Instead of referring to the [Source Code subform], I referred to the table "Source Code".

Thanks for the example Aplnz, it really helped.

Coooool!!, To sum up, I always think of DLookups as an aggregate of the data, in the tables. There are people frequenting this site far more qualified than myself to give you a more coherent explanation, dark shady types, who when you speak their names, you must whisper .... :-) in reverance ....
Don't suppose you know how to Consolidate 4 reports into one report, so that I can send it on one Email?.

[AccessHelp32]

Coooool!!, To sum up, I always think of DLookups as an aggregate of the data, in the tables. There are people frequenting this site far more qualified than myself to give you a more coherent explanation, dark shady types, who when you speak their names, you must whisper .... :-) in reverance ....
Don't suppose you know how to Consolidate 4 reports into one report, so that I can send it on one Email?.

Reports huh? That's my weak spot. I'm great at queries, just learned Forms but I've never created a Report before. Sorry. I'll be posting some Report questions soon, because I need to generate a few myself.