sum subform to form

=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])

Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a
field named Pledge Amount. I want to put a field on the form that gives me a
total of the subform's Amount Pledged. I have tried the above, but I get an
#error in the unbound text box.

Do  you need any more info? Any thoughts on why this does not work?

Thanks in advance,
Scott

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1

0
Scott_Brasted
12/8/2009 4:34:06 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
1250 Views

Similar Articles

[PageSpeed] 53

The proper syntax is

=Sum([Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form![Amount Pledged])

(note the addition of .Form in there)

That assumes that the name of the subform control on form frmCamPledgeList 
is frmCamPledgeListSub. Depending on how the subform was added, the name of 
the subform control may be different than the name of the form being used as 
a subform. It's the subform control name you must use.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Scott_Brasted via AccessMonster.com" <u56211@uwe> wrote in message 
news:a049e794b22f5@uwe...
> =Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])
>
> Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a
> field named Pledge Amount. I want to put a field on the form that gives me 
> a
> total of the subform's Amount Pledged. I have tried the above, but I get 
> an
> #error in the unbound text box.
>
> Do  you need any more info? Any thoughts on why this does not work?
>
> Thanks in advance,
> Scott
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1
> 


0
Douglas
12/8/2009 5:40:42 PM
Doug, I am getting #error in the control. So I assume I have the wrong
subform name. 

In the property sheet for the form, 
when I select the subform and get little squares around it, It says:
Name: frmCamPledgeListSub
Source Object: frmCamPledgeListSub

When  I click in the blank area of the subform, it says:
Record Source: qryContributorPledges
Caption: frmCamPledgeListSub

When I open the subform directly and select the form, I get:
Record Source: qryContributorPledges
Caption: frmCamPledgeListSub

Every name seems the same to me. Is there somewhere else I should look?

Best,
Scott


Douglas J. Steele wrote:
>The proper syntax is
>
>=Sum([Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form![Amount Pledged])
>
>(note the addition of .Form in there)
>
>That assumes that the name of the subform control on form frmCamPledgeList 
>is frmCamPledgeListSub. Depending on how the subform was added, the name of 
>the subform control may be different than the name of the form being used as 
>a subform. It's the subform control name you must use.
>
>> =Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])
>>
>[quoted text clipped - 9 lines]
>> Thanks in advance,
>> Scott

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1

0
Scott_Brasted
12/8/2009 11:31:29 PM
Yes, it does sound as though your subform control is named the same as the 
form being used as a subform.

While the form's open, go to the Immediate Window (Ctrl-G), type the 
following

?[Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form.RecordSource

then hit Enter.

If you get qryContributorPledges, then the references are correct.

To be honest, I'm not certain whether you can actually use Sum like that: my 
original response was more about how to refer to controls on subforms than 
the entire problem. You may have to use DSum instead.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Scott_Brasted via AccessMonster.com" <u56211@uwe> wrote in message 
news:a04d8c5d5e344@uwe...
> Doug, I am getting #error in the control. So I assume I have the wrong
> subform name.
>
> In the property sheet for the form,
> when I select the subform and get little squares around it, It says:
> Name: frmCamPledgeListSub
> Source Object: frmCamPledgeListSub
>
> When  I click in the blank area of the subform, it says:
> Record Source: qryContributorPledges
> Caption: frmCamPledgeListSub
>
> When I open the subform directly and select the form, I get:
> Record Source: qryContributorPledges
> Caption: frmCamPledgeListSub
>
> Every name seems the same to me. Is there somewhere else I should look?
>
> Best,
> Scott
>
>
> Douglas J. Steele wrote:
>>The proper syntax is
>>
>>=Sum([Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form![Amount 
>>Pledged])
>>
>>(note the addition of .Form in there)
>>
>>That assumes that the name of the subform control on form frmCamPledgeList
>>is frmCamPledgeListSub. Depending on how the subform was added, the name 
>>of
>>the subform control may be different than the name of the form being used 
>>as
>>a subform. It's the subform control name you must use.
>>
>>> =Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged])
>>>
>>[quoted text clipped - 9 lines]
>>> Thanks in advance,
>>> Scott
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1
> 


0
Douglas
12/9/2009 2:50:31 PM
Doug,

Got it!. Field in subform footer: =sum([AmountPledged]). name control:
txtAmMountPledged. Control in form footer: =[frmCamPledgeListSub].[Form]!
[txtAmountPledged] works like a charm. I got it after I tried your imm. win.
experiment and it worked and your comment that it probably would not work as
an =sum control.

Many thanks,
Scott

Douglas J. Steele wrote:
>Yes, it does sound as though your subform control is named the same as the 
>form being used as a subform.
>
>While the form's open, go to the Immediate Window (Ctrl-G), type the 
>following
>
>?[Forms]![frmCamPledgeList]![frmCamPledgeListSub].Form.RecordSource
>
>then hit Enter.
>
>If you get qryContributorPledges, then the references are correct.
>
>To be honest, I'm not certain whether you can actually use Sum like that: my 
>original response was more about how to refer to controls on subforms than 
>the entire problem. You may have to use DSum instead.
>
>> Doug, I am getting #error in the control. So I assume I have the wrong
>> subform name.
>[quoted text clipped - 36 lines]
>>>> Thanks in advance,
>>>> Scott

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1

0
Scott_Brasted
12/10/2009 12:42:00 AM
Reply:

Similar Artilces:

Referencing Sub-form field from the main form. ------H E L P
I have a Form & Sub-Form What is the proper syntax to reference a field in the sub-form or visa versa. I tried accessing the field from the subform below: :: [Forms].[pagosdet subform3].[fieldname] but this doesn't seems to work. I need to put the value of a subform field into a variable and add that variable to a field in the main-form. Please Help Thanks. You shouldn't have a space in the subform name. Change it to pagosdet_subform3 Then on your main form create an unbound text box and use this as the control source =pagosdet_subform3!fieldname Use ...

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

Waiting for form to load
I have a form with several cascading subforms. Everything works OK but it takes several seconds for all the subforms to load and for all the calculated textboxes to populate. Is there a way to lock the user from the form until it is completly loaded? I have tried to use Application.Echo = True/False on either side of the Form Load code but so much happens after the main form is loaded that it doesn't seem to help. I don't know of any way to do what you are asking. The problem is with multiple subforms, it has to populate the recordsets for all the subform and then the main fo...

Can Form responses be emailed ?
Can we setup a Form using FP 2003 so that it would be emailed to us ? Like a job application form... or sales report form... or whatever? I can't see where to put an email address in the Submit button code... maybe I'm missing it... or maybe that isn't the way to do it. Can I do a form with only FP Exeensions at my ISP or must we use ASP? thanks for any help on how to do simple FP forms. Mel Hi Mel This forum is for access not frontpage - try the links on the left of the page for a better answer. In FP you don't code the submit button (just hyperlink it javascript:;)....

Menus & Windows vs Forms in Field Level Security
We have GP9.0. What is the difference between a "window" and a "form" in Field Level Security? I am trying to make it so that all users have to enter a password before they can access any of the windows/forms under Tools>>Routines>>Financial, but I'm not sure if I should select each of the forms or each of the windows in the Resource Explorer. Also, is there an easier way to set field security for an entire menu like this? I know in GP7.5 we had the field level security set on the palette, but I don't see any way of doing this for a menu. Keep in ...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

dynamically change sub form (using code) on button click...
Hi, I have a form and the subform allows users to view data or modify data in the table. But for adding new records, I want a simplified view of the table, with only therequired columns to be shown in the sub-form. How can I either change the subform to be opened when the user clicks 'Add New' or hide all the fields that are optional? thanks Philip You can change the form serving as the SourceObject of the subform easily enough by including : Me.MySubForm.SourceObject = "frmMySubForm" in the OnClick event of the "Add New" command button on the main form....

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

how to find subforms not dirty ...
Hi, Thanks in advance.... I have main form frm1 it containds two subforms subfrm2 and subfrm3 I would like how I can code if any of the form frm1 , subfrm2 , subfrm3 is dirty (editting made to any control of these forms ) main problem is not when first time data is entered but would like to know if at all any subform or main form got dirty before udate of the main form which events of the form and subform i could use to popup messege before update of mainform please help me arif Ahmed wrote: > I have main f...

Why am I not allowed to relay form a compuer that is in the same domain as my exchange server
OK. Here's my dilemma..... Why am I not allowed to relay form a computer that is in the same domain as my exchange server if I have the following setting checked " Allow all computers which successfully authenticate to relay, regardless of the list above is selected" I have verified my settings and it appears that my server is not open relay .. However,if I'm not mistaken shouldn't relaying be allowed for authenticated users. But, why when I telnet from a computer that is a member of the same domain as my exchange server I get relay denied.. Is there something I...

pivot table form
i have access 2007 and have created a pivot table form. it works fine for me. the users are using access runtime and when they go to open the form, it displays in data view mode. there doesnt seem to be an option to change to pivot table view. is there a way around this so the users can view the pivot table. thanks ...

keeping track of flow for modules/forms/sheetcodes
In the process of creating a workbook, I am accumulating many forms, Modules, sheets, etc., some of which are linked backward and forwards. Is there a process whereby I can either print the code out, or view the procedures, in a way that will give some order to it for checking. Failing that.... an easy way to print all code including those within userforms to a file that I can view and edit and print? I realise there is a print option under file in VBA that will print to file, but not in a format I can read. Rob Hi Ron, Chip Pearson has code to export modules from a project. You can then o...

Referencing Subform from another form 01-04-10
I have a search form and am trying to pick up data from another form to be displayed in my search form when criteria matches. The search form worked perfectly until I changed the way my database worked and changed some forms. Now I am getting #Name in my text fields of the search form. Some background information regarding my database is that the search form is currently bound to a table risk. I have a data entry form (ptinfo) that has tabs (ptinfo,incident,complaint). The form ptinfo also is bound to table risk. On the incident tab i have a subform occurrence where data is enter...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

Access 2003 Subform Display Bug?
I have a database application developed using Access 2002 SP3 (created using Access 2002 file format). One of the forms in that application displays donors in the main form and a datasheet of their donations in the attached subform. The subform was created using the subform wizard. All fonts in the main and subform are MS Sans Serif 8 pt. The form and subform display perfectly on my system. Today, I took the application to the user who is running Access 2003 SP2. Everything runs as expected except for the one form/subform previously described. On the user's machine, information in the mai...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Sum sum sum
Let me describe my problem... I have a form with with item number with is unique and then i put another form (subform) to linked to record the number of item in and out.... This means a item can have many in and out transactions which make the item number not unique.. My problem is I want to calculate the sum of in quantity within the same item. I want the sum to be keep on increasing when the user enter the quantity. Example user key in 2 then new user key 4 and i want the total quantity to be 6... Please help.... No idea of what i am talking? I will explain... -- Message posted via http:/...

Squished Form
Hey....On startup, I have a form which opens minimized within the main Access window. Upon restoring the window, the form is "squished up" smaller than its true size. Settings for the form are: AutoResize TRUE PopUp FALSE Modal FALSE Style THIN Thank you. -- BrerGoose Moose-Tech Software: http://ourworld.compuserve.com/homepages/webspace/MTS/Home.htm I would try changing border style to sizable. Run the file so you get the squished form. Resize, go to design view, and check the size of the form again and save. Open the file again, if the form stays that the size needed, cha...

create excel spreadsheet to display data from filled text forms
I want to combine the data from surveys (forms) that are uniform in content and filled in electronically using Microsoft Word in order to display the results automatically into an Excel spreadsheet. I realize I must have to import this data into an excel spreadsheet. I also see that this process can be done somewhat by creating an Access database from a text file (Word) which is similiear but I haven't seen specific advice about how to do this for Excel. Please help me. ...

Box to apear in Subform
I have a report with a subreport that shows products in a list and i would like a box to aprear next to it if a product code is displayed I have the following VB code but can not get it to work Private Sub Report_Open(Cancel As Integer) If Me.ProductCode Is Null Then Me.Box.Visible = True End If End Sub Could any one tell me what i have done wrong Thanks very much try If IsNull(Me.ProductCode.Value) Then Pieter "Simon" <S.Dickson@shos.co.uk> wrote in message news:1192524750.007440.143550@k35g2000prh.googlegroups.com... >I have a report with a subreport that sho...