Solved

Why does a "Continuous" Form in Access produce "#Error" in a text box?

Posted on 2012-03-30
15
659 Views
Last Modified: 2012-06-27
I am developing an Access 2007 "project" (.adp) as a front-end to data stored in a SQL-Server 2005 Express database.

In the Access application I have "Continuous" Form that lists records from a Table as rows.
In the "Form Footer" section I have 3 text box controls that count and sum the values from the displayed rows.

txtLineCount, data source : "=Count([ShopSales_UniqID])"
txtTotBalnQnty, data source : "=Sum([ShopSales_Qnty])"
txtTotBalnValue, data source : "=Sum([ShopSales_Value])"

These 3 text boxes display the expected values OK, but my problem arises when I try to added a 4th text box in the Form Footer.

txtTotCostValue, data source : "=Sum([ShopSales_Qnty] * [ShopSales_CostPrice])"

The "ShopSales_CostPrice" field is part of the underlying Table and is also displayed in each row. I want to display the total cost value in the Form Footer.

When I add this 4th text box I get "#Error" displayed in all 4 text boxes in the Form Footer.
I can understand there might be a problem with my "data source" syntax for my 4th box (or that "Continuous" Forms don't like such data manipulations) but why would it cause "#Error" to appear in the original 3 text boxes that work OK when the 4th box is removed?

Can anyone explain what is happening? Many thanks.
0
Comment
Question by:colinasad
  • 8
  • 5
  • 2
15 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Does this variation work?

Sum([ShopSales_Qnty]) * Sum([ShopSales_CostPrice])
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Also, have you ensured that ShopSales_CostPrice is included as a field in the form's recordsource, and is spelled correctly, etc...?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
have you tried:

=Sum(NZ([ShopSales_Qnty],0) * NZ([ShopSales_CostPrice],0))
0
 

Author Comment

by:colinasad
Comment Utility
Thanks for the very prompt replies, mbizup.

Your first suggestion produces the same "#Error" message in all 4 boxes.
I'm not sure that the arithmetic would be correct anway. Would that not sum all the quantities and sum all the unit cost prices then multiply these 2 values. That would not always be the same as the sum of all the individual products.

I'm pretty sure my typing is OK. When I just do a "sum" on one or other of the fields I get the correct value (duplicating what is already in the other 3 boxes), but as soon as try doing a multiplication in the data source I get the error.

I could understand the "#Error" appearing in my 4th text box alone, but why does it also appear in my other 3 (unaltered) text boxes which work OK normally?
0
 

Author Comment

by:colinasad
Comment Utility
Thanks also for your suggestion, fyed.

Strangely, introducing "Nz" into the mix actually causes more problems.

The multiplication you suggest still causes "#Error" to appear in all 4 text boxes.

But, even using "Nz" in the  simpler data source expression, "=Sum(Nz([ShopSales_Qnty], 0))" also causes the "#Error" result. Without the "Nz", the "ShopSales_Qnty" values would be summed OK.

That seems strange because I would have thought that "Nz" would help make the calculation more robust.

Regards.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Is it an issue with just that field?

Try this, on its own:

= Sum([ShopSales_CostPrice])
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
And just double-checking...

ShopSales_CostPrice is a FIELD in the underlying recordsource, right?  (as opposed to a textbox or other control)
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
have you checked your references?

Can you take a screen shot of your form and post it here.
0
 

Author Comment

by:colinasad
Comment Utility
Thanks again for your replies.

Yes, "=Sum(ShopSales_CostPrice)" works OK, but just sums the "unit prices" of each row displayed on the screen.
Similary, "=Sum(ShopSales_Qnty)" also works OK (and is already used in one of the first 3 text boxes).
The problem arises as soon as I try to combine these two fields into the text box's "data source".
They are both fields in the Form's underlying data source, which is a Table in the SQL Server database.

I have attached a Word document containing 2 screen dumps. The text box in question is at the bottom of the "Unit Ready" column that contains the "ShopSales_CostPrice" field.
The first screen dump shows all values displayed OK when the data source is just a simple sum of one field. The second image shows what happens when I try to introduce multiplication into the data source.

I may just have to add a "ShopSales_CostValue" field into the underlying Table, so that I can do a sum on a single new field. But that does seem a bit cumbersome considering it should just be a calculation on two fields that already exist.
AccessForm.doc
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
>> I may just have to add a "ShopSales_CostValue" field into the underlying Table

Fwiw, That sounds like a fairly simple solution - and one that I would use myself, with the difference that I would use a query rather than adding the field to your table.  So by using something like this as the recordsource for your form:

SELECT ShopSales_UniqID, ShopSales_Qnty,ShopSales_CostPrice,  ShopSales_Value, ShopSales_Qnty * ShopSales_CostPrice AS ShopSales_CostValue
FROM YourTable...


And then sum the calculated query field as you described.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Is the recordsource of your form currently JUST the table, or is it a query?

Can you post the recordsource of your form, and a screenshot showing your table's design?
0
 

Author Closing Comment

by:colinasad
Comment Utility
Thanks, mbizup.
In the Form_Open procedure I set my Form's ".recordsource" to be "SELECT *, ShopSales_Qnty * ShopSales_CostPrice AS COSTVALUE FROM TABLEXYZ" and made my problem text box's "data source" to be "=Sum([COSTVALUE])" and it worked!
I guess "Continuous" Access Forms just don't like controls to have data manipulations in their data source.
Many thanks.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<<I guess "Continuous" Access Forms just don't like controls to have data manipulations in their data source.>>

You've perked my curiosity - and I'm going to test that out when I get a chance.

I have never had any trouble with data maniuplation like that - ie:

SUM ([Field1] *123)

And I THINK that I have used multiple fields like that in the past with no trouble:

SUM (Field1 + Field2)


The one problem I *have* run into when using aggregate functions in control sources is if a field is one of those new "Calculated" field types that Access 2007 brought upon us.  Those do need to be manipulated through a query (but I don't think that was the issue here).
0
 

Author Comment

by:colinasad
Comment Utility
And the other strange thing was that a problem with one text box control impacted on three others.
If you do discover anything further I think you can post again to this question.
Thanks & regards.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Ok - I'll let you know tonight how accurate my memory is on this one...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now