Solved

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

Posted on 2012-03-30
15
666 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
ID: 37786648
Does this variation work?

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

Expert Comment

by:mbizup
ID: 37786662
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)
ID: 37786724
have you tried:

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

Author Comment

by:colinasad
ID: 37786728
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
ID: 37786791
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
ID: 37786796
Is it an issue with just that field?

Try this, on its own:

= Sum([ShopSales_CostPrice])
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37786803
And just double-checking...

ShopSales_CostPrice is a FIELD in the underlying recordsource, right?  (as opposed to a textbox or other control)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37786806
have you checked your references?

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

Author Comment

by:colinasad
ID: 37786903
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
ID: 37786929
>> 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
ID: 37786962
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
ID: 37786993
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
ID: 37787026
<<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
ID: 37787063
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
ID: 37787078
Ok - I'll let you know tonight how accurate my memory is on this one...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

895 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

11 Experts available now in Live!

Get 1:1 Help Now