Solved

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

Posted on 2012-03-30
15
676 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 37786724
have you tried:

=Sum(NZ([ShopSales_Qnty],0) * NZ([ShopSales_CostPrice],0))
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 48

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

752 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