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
1766 Views

Similar Articles

[PageSpeed] 26

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:

Sum based on three variables
Hello Group I have created a database of labels we send to outside fillers. What I would like to do (without using autofilter or pivot tables) is sum the number of labels sent to a filler by month. So for example range a1:a200 = date with a short date in range b1:b200 (mmm - I thought it would be easier to calculate), range c1:c200 = filler (we have five), range d1:d200 = label description and finally range e1:e200 is the quantity of labels sent. So a running total could be maintained by Month, Filler and total of labels sent for the month. I do not have permission to access VBA/Macros (A...

Form Opening with Blank Column(s)
I have created a couple applications that are working well on our remote access server, Access 2003. However, when I have people put the application on there standard desktop on the network, one or more columns appear blank when the form is opened in Access 2003. Any ideas why the data retrieval or function would change between our server addition of Access vs client workstations? -- Message posted via http://www.accessmonster.com I have one user in my organization that is experiencing this problem also. The problem started the week of the 25th of February. The user could not give me a ...

Cell formating and summing imperial weights
Hi all, Does anybody know how to format cells for using imperial weight (pounds, ounces and drams) and how do I then summ these values to als give the result in pounds, ounces and drams -- Wood ----------------------------------------------------------------------- Woody's Profile: http://www.msusenet.com/member.php?userid=575 View this thread: http://www.msusenet.com/t-187117693 On Wed, 9 Nov 2005 03:39:09 -0600, Woody <Woody.1ydm2c@no-mx.msusenet.com> wrote: > >Hi all, > >Does anybody know how to format cells for using imperial weights >(pounds, ounces and dram...

Using Access Forms On the Web
I have never developed an Access DB for online use. I would like to develope some Access Forms to collect information OnLine. I am told by my IT department that ASP is required to get Forms working on line and we don't use ASP. However our Training Department is telling me otherwise, saying that ASP is NOT required to put forms designed in Access on to a web server. Could someone please clarify this for me? I can easily create a form in Access to collect user input. I want to put that form Online. Thank you very much for any input. On Fri, 20 Nov 2009 13:04:52 -0500, &q...

"Potentially dangerous" Request.form error
Hello all, I have a new installation of CRM on my server, and I get the following error when I try to save anything. I can't find anything on this on the microsoft website. I'm sure it's a security issue, but if I try to change that and save, it gives me the same error. Helpful suggestions will be welcome. Thanks, Daniel. Alright, here's the error: Server Error in '/' Application. ---------------------------------------------------------------------------- ---- A potentially dangerous Request.Form value was detected from the client (crmFormSubmitXml="<syste...

linked Forms
I have a form linked to my main data entry form via a command button. How do i get it to display a "blank" or new form for data entry when I click the button and the form opens? Currently when the button is clicked it opens the form and it is displaying the first record and you have to click the add new or scroll all the way through the records in order to add a new one. =?Utf-8?B?U2FyYWhK?= <SarahJ@discussions.microsoft.com> wrote in news:A0B2C476-13EC-4F5D-A1E2-62183740DB4F@microsoft.com: > I have a form linked to my main data entry form via a command > button...

Why do forms sometimes minimize on their own?
I have 2 forms. The first one is opened with docmd.maximize. I click on a button on this form which opens a second form. I do some stuff and then I close the second form. The first form is still there but it appears as if it has shrunk some and is not maximized in the database pane. How can I correct this? Robert The standard Windows behaviour for an MDI application (which is what Access is) is that *all* windows are maximised, or they are *all* 'normal'. If you 'restore' a maximised window to it's 'normal' size, then all other maximised windows w...

Can I Sum by Cell Colour or Format?
I have a large list of values, these values have been colour coded. I would like to sum each of the colours seperately. I know i can add a filter, sort by colour & sum this for each coulour, but am wondering if there is a formula that would do this for me? Ashley, have a look here http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ashley" wrote: > I have a large list of values,...

command button to email form
Hi there I am trying to email a form with a command button that uses Outlook to email. I am using the following code: Private Sub btnEMAIL1_Click() Dim mess_body As String Dim rst As DAO.Recordset Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Set appOutLook = CreateObject("Outlook.Application") Set MailOutLook = appOutLook.CreateItem(olMailItem) Set rst = Form_F_People_Mailings.RecordsetClone rst.MoveFirst Do While Not rst.EOF If IsNull(rst!Email) Then MsgBox "skipping " & _ Form_F_People_Mailings.LastName & _ " no email address....

Change and refresh OLEObject showing Excel chart on Access form.
(Sorry this is not strictly Excel charting but I have had no hints in the Access groups where I posted this first - hoping someone in the Excel world can help me) Hi All I display an Excel chart in a MS Acess User form using an unbound OLE object. I am trying to change the Excel workbook(and therefore chart) that my OLEObject displays: Private Sub Form_Load() With Me.oleExcelChart ' SourceDoc is currently "W:\Public\83.xls" Debug.Print .SourceDoc .SourceDoc = "W:\Public\XX.xls" Debug.Print .SourceDoc ' ## how can I get ...

Inserting Calculation / Expression in a Form
I have my Table trimmed down and working perfectly. I developed a beautiful Form and it also works perfectly. After extensive trial and error I managed to developed a Query that will calculate the totals keyed into the table (via the Form, or the Table) from a series of 9 different fields, all ranging in value from 0 to 5. The calculation is a simple addition for the grand total sum or quantity of all fields. My problem is that I want my Form to have a single (non-editable) field which will auto-calculate the total QTY from these same values that I enter into the Form. My Form is c...

query form 12-29-09
I have a form that opens when you run a query for an employee name. It does exactly what it is suppose to do. It displays all information that I have requested. The problem I am having is when I enter a name of an employee that is not in the database the form comes back as a grayed out box. Is there any way to set the form or query to prompt the user that the name is not in the database and allow the user to run the query again. Eric Check to see if record exist before you open the form. If you are using a command button to open your form containing code like the follow...

Controlling display of forms
Hi, I'm trying to control what parts of a form are visible or not depending on the user of the computer. I've managed to find and adapt some code to tell me who the user is and I have a table set up with user names and a permission level (1 or 0) I can manually alter the On_Load event of my form to hide the parts I want (tabs) Can anybody help me out with putting it all together so that the forms On_Load event looks at GetUserName function which looks at the UserPermissions table and sets Visible True or False accordingly. This is taking me so long and it looks like it ought ...

How can I set up an RSVP form for external recipients
I'm trying to send an email to several people using Publisher. I would like people to be able to RSVP to an event through a link but all the links I have tried won't work. How can I make it so an email can be sent to me through a link in Publisher with information already inbedded in the email? Like a form with check marks and areas to enter a name and phone number. ...

Ability to execute reports directly from CRM forms
Currently our users are doing the following in order to print a report (Example): - Open quotes list - Double-click a specific quote - Copy the quote ID to clipboard - Go to MyWorkspace > Reports - Select the Quotes report - Paste the the copied quote ID in the Quote-ID filter field and then run the report Obviously, the above is really hectic !! Is it possible to simplify the above through customization by adding a link or button in the original quotes & opportunities forms... etc so that user can open the quote form and then click this button and then teh report for this quote...

Activate another form in Maximised mode
I have a form which opens up another form in pivot chart view. I want to Save the pivot form as bmp. I have used the code from Steve lebans to save the form as bmp. My problem is whenever i click on the first form it opens the pivot form in small size , picture of form is captured and then the pivot form gets maximised. thus the picture saved is of small size. Even after maximising the form on its open event it gets maximised only after the image is captured. If only I could open the pivot form in maximised mode in the first instance the complete image will be captured. Or else I open the piv...

Help using conditional count in form header
Is it possible to have a textbox in the form header to count the number of a conditional textbox in detail section? I get #Error. Thanks ...

Form display
Hello, I have created a form in Access for users to use. My form is about 6" x 6". Everytime I double click the Access file to open, I setup the form to be automatically opened. But the Access backgroud is also displayed. Is there a way to not show the Access background? To make it more like a real application. thanks. In startup options (Tools--Startup), uncheck 'display database window'. This is for version 2003. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Boon"...

Search information using the main form
I am making a access db with a main form and linked a subform. I can show the record by entering the primary key in the main form and the subform will show the relevant information --- that is ok! But i try to enter infomation in the other text box in main form but no action only the table content changed! ---- problem How to use the other text boxes to search the record and the result can be seen in main form and of course related record will be shown in the sub-form as using the primary key. How to prevent the content to be changed as mentioned but only after the button will created lat...

Populating calculation from one form to another
I am trying to use a form (TotalsForm) to run a calculation over several records in and then save those totals to a subform(Summary) that is already open.The main form that the Summary subform is in is Info form. I am using a simple forms!x=y statement and it will send the data over, but have to click the save button the code is attached to twice before it will appear on the other subform. I have tried refresh and it still takes two attempts. Any suggestions? ...

Importing form 2002 to 2000
I have tried to import contacts from Outlook 2002 to Outlook 2000, but to no avail - it tells me that I do not have sufficient privaleges to access the file I am trying to copy from. I have Admin privaleges on Win 2000, but have copied the file from XP onto a CD and this is what I am trying to access?? Any ideas? ...

Making a Time book Form
I'm creating a time book in access to record Shift work and time off and such things. so far everything seems Ok but the Form. i need the Form to lay out as a Biweekly calender, dates across the top employees down the left side. The User puts in a Payperiod Number and the rest of the Form populates on Refresh and the user can make adjustments. I can't seem to find away to make it work. Does any one have any suggestions? Have a look at the Flex Grid Demo program at :- http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=21&SID=5debzz2c87c6957859f13b1d2zcccf...

change property on subform
Hi and thank you for your time, I have a form/subform with a one to many relationship. In the subform, I have fields Oct, Nov, Dec, etc in each record. I want to compare the Nov date to the Oct date and change the BackColor if the dates do not equal. The code works for the current record but the Backcolor will also change in the next record on the many side without making the proper comparison. I have the following code on the On Current property of the subform. Let Nov.BackColor = 123 If Me.Nov.Value > Me.Oct.Value Then Me.Nov.Backcolor = 456 End If So, the Nov Backcolo...

Print all records on Form
I am having a problem. I created a main form that filters many different options and then opens another form. From there the user can double click any record to get more detail, but I would like to also give them the option to print only the records on the form as well in a report. But everytime I click the button to print the report all the records from the query print. Not just my filtered records. Can anyone help me? Thanks! You can apply the form's filter to the report by putting code like this in the Click event procedure of a command button on your form: Dim strW...

not summing all the cells
I wanted to sum the values from an column but for some cells it doesnt sum it to the total. Why is this? Maybe some of the numbers have been formatted as text, are any of them left-aligned instead of right-aligned? To convert text cells to number format you will need to highlight them and use Format->Cells to set the format to number, then click on each cell and press F2 followed by Enter as a bug in Excel means text cells won't convert immediately back to number unless you re-enter the data. Andrea Jones www.stratatraining.co.uk "Alina Roumania" wrote: ...