Access 2007 Subform Total Error
Contents |
Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Page 1 of access 2007 subform datasheet view 2 1 2 > Thread Tools Rating: Display Modes 10-20-2010, 01:37 AM
Access 2007 Subform Control
#1 sharq Registered User Join Date: Oct 2010 Location: Kuwait Posts: 8 Thanks: 0 Thanked 0 Times in 0
Ms Access 2007 Subform
Posts Sum Total #error in continuous sub-form footer Hi Everyone, I am not an IT guy, I have designed an access database for inventory and sales details for some analysis and reporting. I have
Access 2007 Subform Wizard
a subform within a form, but the problem is that when I want to calculate the sum total of all values for the rows/ records for the subform in the subform footer, it gives an #error message in the those unbound textboxes. I have three columns to be summed up, viz., Units, BankedAmt & OtherAmt (through debit/ credit cards) which are bound forms. The number of records in one access refer to subform single entry (main form) could run into three figures (for subform). I have two more columns after these three columns for TotalAmt (Banked Amt – Other Amt) and CostperUnit (TotalAmt / Units), the results of which are displayed correctly on each record in subform. However, the problem is that I cannot sum total all the units/ BankedAmt and OtherAmt in the subfooter of the subform for confirming the total of each Bank receipt which is my one single transaction for the main form (when I use Sum/Agg). I’ve even tried a combination of some formulas after checking on the net but just cannot get the result of sum of all units. I am novice in creating an access database though I have used the applications some years back. Help in this regard would be really appreciated coz I want to display the total for that single transaction for the main as the details are captured in the subform and I want the totals on the subform footer only to know that all the details are captured correctly. I guess that has nothing to do with the last row which is by default blank and has null values but that is normal I suppose
GUI Design GUI Design Examples Submit Your Examples Resources Resources Database News Knowledge Base Microsoft Access Products, Tools & Add-In's Online Shop UK Visitors US Visitors Info Contact Us Advertise With Us Link To Us Write ms access 2003 subform For Us Competitions Subform Totals and Mainforms Displaying Subform Totals in a Main Form ms access query subform in Microsoft Access In this article we are going to show you how to display totals taken from your subform, ms access vba subform and display these in controls in your main form. We need to be able to reference the subform control from the main Microsoft Access form. If you attempt to sum a subform field by http://www.access-programmers.co.uk/forums/showthread.php?t=200461 using a calculated control on a main form, you may receive an error message in the main form control. For example, the following expression in a main form control generates a "#Error" error message: =Sum(sfrmAlbumTracks.Form!txtLength) We can see this in use on the following form, where we would like to show the Total Album Length on our main form by calculating the sum of the tracks displayed http://www.databasedev.co.uk/subform-total.html in the subform. The control with the error has been highlighted: The form containing the calculated control, which is attempting to sum the values from the subform To get around this problem, and to avoid returning an error with our calculation, we can sum the subform field by using a calculated control placed in the subform's form footer. If we do not wish to display this control, as we will be returning the result in the main form, we can set the controls property to Visible: No. We can then reference the calculated control from the main form to display the summed value. See this article over at The Access Web for details of Referring to Form and Subform Properties and Controls To solve this problem, we first add a calculated control in the footer of our Subform, which sums the subform's Track Length field to create a subtotal. It uses the following syntax: =Sum([Length]) We can see this in the image shown below: The subform, containing the calculated control within the form footer We have named this control txtTotalTrackLengths and also set the control properties to Visible: No Now, a textbox control on our main form refers to the sub
help? Post your question and get tips & solutions from a community of 418,417 IT Pros & Developers. It's quick & easy. #Error on SubForm https://bytes.com/topic/access/answers/931975-error-subform-field-totalling field totalling. P: 45 JayF Hi all, I'm stumped and it hurts to http://www.utteraccess.com/forum/totals-subform-display-t1942361.html have to give up and ask for help, but even the omniscient Google limited by my mere biology can't solve this, and I'm still learning Access so here goes. I have a main form containing a subform, which lists products within an order, in the footer of which is a text box called access 2007 sfrmTotal whose control source contains the expression: =Sum(Nz([SubTotal],0)) Back on the main form I have a text box with control source: =[sfrmcOrderDetails].[Form]![sfrmTotal] The subform also contains fields such as Discount, Tax, Quantity, etc. I have tried the above Sum() expression on all of the fields and every single one of them returns #Error on both the subform control and the main form one. [Total] and access 2007 subform [SubTotal] are calculated query fields, but [Quantity] and [Discount] are not. Should that even make a difference? I have tried a function called nnz (not numeric zero), which makes no difference. I was tweaking queries, checking names and messing with things for the best part of yesterday. I've learned a lot on the way, but I'm no closer to figuring out what is causing the #Error on ALL of the fields. My last resort is to appeal to the Gurus of Bytes.com for divine assistance or at least guide me in the right direction of where I should be looking for a solution to banish the #Errors from my project. Thanks in advance. Jay Feb 11 '12 #1 Post Reply ✓ answered by ADezii Puzzling situation, JayF. Set the Control Source of the sfrmTotal Text Box in the Sub-Form Footer to: Expand|Select|Wrap|Line Numbers =DSum("[Quantity]","eqryPODetailsExtended","[PurchaseID]="&[Forms]![dfrmPODetails]![sfrmcOrderDetails].[Form]![PurchaseID]) Share this Question 13 Replies Expert 5K+ P: 8,394 ADezii I think the problem is that you are trying to Summarize a Calculated Field. You would need to include the entire Expression within the Sum() Function, something similar to: Expand|Select|Wrap|Line Numbers =Sum(Nz(([Quantity]*[Price])-(1-[Discount]),0)) Feb 11 '12 #2 reply P: 45 JayF Thanks
Custom Search UtterAccess Forums>Microsoft® Access>Access Forms Get totals from subform and display on main form, Office 2007 Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Access Wiki Options hbsclaimsView Member Profile Apr 7 2010, 11:03 AM Post#1Posts: 177Joined: 5-March 09From: TexasI have a form that contains several subforms in separate tabs. What I would like to do is display a total of one of the subforms on the main form. Otried adding totals to the query of the subform, but that just made the subform not show up on the main form.oes anyone have a good idea on how to accomplish this task?ThanksDavid theDBguyView Member Profile Apr 7 2010, 11:07 AM Post#2Access Wiki and Forums ModeratorPosts: 67,393Joined: 19-June 07From: SoCal, USAHi David,One idea is this: