Data does not show up in table

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

Guest

When i manualy enter data into a form it gets displayed in it's table,
however data that is generated from a forulma in the form does not get
displayed.
example:
i have the fields "Qty", "RecievedQty" and "OnOrdQty".
the control source for "OnOrdQty" is =nz(Qty)-(RecievedQty)
so when i enter an Qty of 2 and RecievedQty of 1, the text box shows
OnOrdQty of 1
however this information is not showin up in the table.
 
Hi David,

No, it won't show up in the table while you have the data source set to a
formula. You need to have the data source set to be a field in your table
(the field you want the data stored in), then use the formula to populate
your control in an event procedure, eg: in the "after update" event of the
Qty and RecievedQty fields.

Hope this helps...

Damian.
 
The Data Source field of your control on your form is currently set to simply
calculate a value based on the values in other fields... it needs to be set
to the field you wish to store the data in.

If you want it to be automatically updated with a calculated value, add the
formula into the "After Update" event of the fields that the value is
calculated from, that way your calculated field will be updated, and the
result will be stored in the database field.

Damian.
 
thankyou for your help, and i dont mean to be a pain.
But could you please give me an example of what code to use.
 
Hi David,

You have the fields "Qty", "RecievedQty" and "OnOrdQty" right?

Alright, create three text boxes on your form, one called lngQty with data
source of Qty, one called lngRecievedQty with data source of RecievedQty, one
called lngOnOrdQty with data source of OnOrdQty.

In the AFTER UPDATE event of the field lngQty and lngOnOrdQty have the
following code:

me.lngOnOrdQty = nz(me.lngQty)-nz(me.lngRecievedQty)

Hope this helps.

Damian.
 
Thanks again for your help.

I have tried your code however i get the error message-
Microsoft Access can't find the macro 'me.'
when i enter data in the form
 
When i manualy enter data into a form it gets displayed in it's table,
however data that is generated from a forulma in the form does not get
displayed.
Click to expand...

I have to disagree a bit with Damian here, not about *how* to do this
- he's quite right there - but about *whether it should be done at
all*. Except in very specific cases, it usually should NOT.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson[MVP]
 
thanks for your input however i need to store this information in a table as
i need it to generate various reqorts and queries.
Regards dave
 
You aren't disagreeing with me at all... question asked, question
answered... see my post over in Database Design for my feelings on calculated
fields...

;-)

Damian.
 
David said:
thanks for your input however i need to store this information in a
table as i need it to generate various reqorts and queries.
Regards dave
"John Vinson" wrote:
Click to expand...

Then you use the same expression in your reports and queries. No need to store
the result. In fact you can build a query that is essenntially a copy of your
tabe plus the added field using the calculation. Then you just use the query in
all cases where you would otherwise have used the table and the value will
always be there ready to use.
 
You aren't disagreeing with me at all... question asked, question
answered... see my post over in Database Design for my feelings on calculated
fields...
Click to expand...

<g> ok, you answered the question - I "unasked" the question!

Sometimes it isn't a matter of getting the right answer to the OP's
question; sometimes it's better to make sure it's the right question.
This is a case which could have gone either way; you took one, I took
the other!

John W. Vinson[MVP]
 
thanks for your input however i need to store this information in a table as
i need it to generate various reqorts and queries.
Click to expand...

If it were the case that a field must be stored in a table in order to
generate various reports and queries, then you would be correct.
However that is emphatically NOT the case.

A query with a calculated field can be used as the basis of a Report,
of a Form, of another Query; you can sort by calculated fields, search
by calculated fields, and so on. The only thing you can't do with a
calculated field is to edit it; but in general you don't WANT it to be
editable, because if it gets edited then it is inconsistant with the
calculation which generated it.

John W. Vinson[MVP]
 
Back
Top