Inserting Calculation / Expression in a Form

[Guest]

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 created from the fields within the Table. I have
linked a Relationship from the Primary Key of the Table to the Expression /
Calculation field in the Query, but I am not able to insert the Expression /
Calculation field of the Query into the Form because the Form will not allow
you to insert fields from two different sources.

I have tried inserting a text box into my form and have used several methods
from copying and pasting the expression formula I used in the Query that
works perfectly, to using the Wizard to develop the expression. I have tried
inserting this text box with the formula into both the Form Header section
and the Form Detail section (my numeric values that are being added / totaled
up are being keyed into the Form from the Form Detail section).

It will not calculate the values for me - I get an error stating that I need
to edit the Control's Control Source Property and edit the Form's Record
Source Property. For someone who has been using Access 2003 for just two
weeks at the most this is just as good as Greek to me. It would seem to me
that such a simple and straighforward calculation should be easy to
accomplish without so dam much Greek. Anyone out there able to translate for
me and point me in the proper direction?

 

[Pieter Wijnen]

To use the query use this as the controlsource of your control

=DLookup("MyTotalsField","MyQuery")

You could also skip the query by using
=DSum("MyField1 + .. + MyField9","MyTable")
if none of the fields can contain a Null value (blank)

HtH

Pieter

 

[Guest]

Pieter,

Thank you for your recommendations, but it still won't work????

I tried the following:

Confirmed that all my numeric fields had a at least a minimum default value
of '0' instead of a Null value.

Inserted a text box in the Form Design View, and typed the following text:

=DLookup("Expr 1" , "QRY 1")

It no longer generates an error control box in the Form Design View, but
when I switch to the Form View the new text box field says "#Error"

So I tried the alternate method as follows:

=DSum("[Vendor QTY] + [Cat 1 QTY] + [Cat 2 QTY] + [Cat 3 QTY] + [Cat 4
QTY] + [SE 1 QTY] + [SE 2 QTY] + [SE 3 QTY] + [SE 4 QTY] ","TBL - Data Entry")

And it FINALLY populates a numeric value - HOORAY, but darn it, it still
doesn't work correctly because it populates a QTY value of the entire
database, not a QTY value for just the individual record. If I try to remove
the brackets [ ] used in the expression I get an "#Error" displayed in the
Form View, if I try to add a bracket [ ] around the Table File Name I also
get an "#Error".

What am I missing here?