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!
13 16113
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)
'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 "tblLocSubLocQty"?
ADezii,
I had the same problem. I don't understand your code above. What is "tblLocSubLocQty"?
A Table that stores various Inventory values
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?
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.
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.
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.widgetview![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.
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.widgetview![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.
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. - Is [Circulation] a field in a Table. What is the name of the table.
- If So, is it a Number, not a text field.
- Is [Source Code Subform] a Table
- Is [Camp Sched Proj ID] a field in that table.
- Forms![Campaign Schedule]![Camp Sched Proj ID] does not refer to a subform, it is referring to a Form called [Campaign Schedule]
- 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.
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. - Is [Circulation] a field in a Table. What is the name of the table.
- If So, is it a Number, not a text field.
- Is [Source Code Subform] a Table
- Is [Camp Sched Proj ID] a field in that table.
- Forms![Campaign Schedule]![Camp Sched Proj ID] does not refer to a subform, it is referring to a Form called [Campaign Schedule]
- 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).
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.
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?.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Josh C. |
last post by:
Hello everyone. I'm a bit of an Access newbie, so please bear with me.
Please go to http://www.dumoti.com/access/ to view the database - 536kb.
I'll go straight into examples:
In the form...
|
by: xmp333 |
last post by:
Hi,
I have a form that is designed as a data sheet view. Attached to this
is a subform with some VB code. When the user clicks on a row, the
subform should pop up and run the VB code which...
|
by: xmp333 |
last post by:
Hi,
I have a data sheet with a subform; each time the user clicks on the
"+" to show the subform, I want some code to run. I tried attaching
the code to a wide variety of events, but at best...
|
by: kkrizl |
last post by:
I have a form that displays general information about an alarm permit
location. There's a subform that shows detailed information about
burglar alarms that have gone off at the location. When a...
|
by: Thelma Lubkin |
last post by:
I have a form/subform with the common one-to-many relationship.
The form allows user to display records and move to other records
via the selector, to add,delete, and edit them, with the related...
|
by: sparks |
last post by:
I have a form/table with an autoid it is linked to a table/form with
and ID as a 1 to many.
Under this form/table I need another table with many records for each
on of the many in the previous...
|
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...
|
by: Christina123 |
last post by:
Currently working with Microsoft Office 2000 and whatever version of Access came with that.
I am developing a database to track the comings and goings of shared tools.
Everything works...
|
by: Neekos |
last post by:
I have a form called frmPaxInfo, and on this form is a subform called subfrmPaxInfo. This subform (which is filtered by cabin) contains the passengers name along with all charges associated with each...
|
by: sumit kale |
last post by:
Hi,
Can somebody help me resolve my problem ?
I am getting error when calculating total using unbound textfiled in subform.
I have a main form called purchase_register_master and a subform...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |