Form not updating Table???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

So how do I make this work?

I have a form, on this form we enter product amount in LBS, then we enter
Price per LBS.

Then the form auto calculates the amount due based on these two numbers.

what setting/code is needed to have this number that was automatically
calculated entered into our table were we want it stored.

we need this because our final number is missing from reports due to not
being entered into the table.

I think this should be a simple issue but I cant find the solution.
 
You would not store it. Just calculate it on your report the same way you
did on your form.

Calculated values should not be stored in a table. If one of the calues
changed, nothing would prompt Access to correct the calcualted value and
your data would be all messed up.
 
Hi, Plate.

You're right in that it's a simple solution, but perhaps not the one you had
in mind.

In general, it is almost never desirable to store the result of a
calculation in a table field, because:

1. It risks being wrong if a user changes one of the elements of the
calculation outside the context of your form where the calculation occurs.
2. It requires VBA code to do so, since a ControlSource can *either* be set
to an expression or the name of a field, but not both.
3. It is much faster to recalculate the accurate amount on the fly in a
query than it is to read the suspect value from disk.

The solution is to create a query that includes all of the fields needed for
your report, and add the calculated field, following the syntax:

CalculationLabel: Expression

For example, to calculate area from length and width, enter the following in
the Field: row:

[Area]:[Length]*[Width]

Base your report on the query, and you can place the calculated field just
as you would one from the table.

Hope that helps.
Sprinks
 
Ok I see what you are saying. Let me ask this:

In this specific situation I actually need to copy and paste the table to
someone elses excel sheet. I understand this is not very orthodox just trust
me its my only option.

But my table is missing the Amount Due, It will take up hours to manually
input this data.

So can I set my autocalculated box to input into my table and update any
time a change is made?

It amazes me how quick you guys answer. Many thanks!
 
Just add the formula in Excel = A1 * B1

Then copy it down the column.




Or, build a query as Sprinks suggests, and then export that to Excel. The
calculation would already be there. Of course, if the Excel recipient makes
a change to any figure, the calculation would not recalculate since you are
only exporting the numbers you see on the screen, not the underlying
formula.
 
Plate,

All you need to do is create the query I described. When you execute it, it
will calculate all of your AmountDue fields. Then cut and paste from IT. A
query behaves exactly like a table.

But, if you have to do it this way (cringing)...the code would be, in the
AfterUpdate event of all elements of the calculation:

Me![MyFieldName] = Me![MyCalculatedControl]


Sprinks
 
Sprinks,

Thanks I will give it a shot.

Everyone else may thanks for all your suggestions

Im sure ill be posting later, "this crazy idea I had wrecked database how do
i fix?"

hehe, take care and have a good weekend all.

Sprinks said:
Plate,

All you need to do is create the query I described. When you execute it, it
will calculate all of your AmountDue fields. Then cut and paste from IT. A
query behaves exactly like a table.

But, if you have to do it this way (cringing)...the code would be, in the
AfterUpdate event of all elements of the calculation:

Me![MyFieldName] = Me![MyCalculatedControl]


Sprinks


Plate said:
Ok I see what you are saying. Let me ask this:

In this specific situation I actually need to copy and paste the table to
someone elses excel sheet. I understand this is not very orthodox just trust
me its my only option.

But my table is missing the Amount Due, It will take up hours to manually
input this data.

So can I set my autocalculated box to input into my table and update any
time a change is made?

It amazes me how quick you guys answer. Many thanks!
Click to expand...
Click to expand...
 
Back
Top