How to get query to update table

[Golfinray]

I have a form that the source of is a table. However, I have a query that
does a calculation that I need inputed into the same table. One text box on
the form needs to show the results of that calculation. BUT the table is the
recordsource of the form so how do I get that one textbox to update FROM the
query and then update the table which it is currently not doing. In other
words, what I would like is for every time the form is run, it runs the query
to get the latest results of the calculation, then fills that textbox with
the result, and then stores that result in the table when the form is saved..
Thanks a bunch!!!!!

 

[Jeff Boyce]

This may seem like a semantic quibble, but it bears repeating that in Access
forms are NOT the source. Tables store data, forms give you a way to look
at it and add/edit/delete it)

You mention a query that does a calculation and that you wish to store the
calculated value in the table. This is rarely necessary, and rarely a good
idea. If your query calculates the value based on other fields in the
table, let it! Don't store the value also, as that opens your table up to
data integrity risks.

The calculated value in the query can be displayed on your form, but
(usually) doesn't need to be saved into the table.

If you'll explain more about your specific situation, it may prove to be one
of those rare circumstances for which storing the calculated value does make
sense...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Golfinray]

Thanks, Jeff. I am not trying to store data in the form, just use it to save
data into the table. I probably DON'T need to store the calculation, just
have it show up in the form when the managers use the form. That would mean I
have to have that listbox as a subform with a different recordsource than the
table? I can do that, but I thought it would just be easier to let the form
add the calculation to the table. Thanks!

 

[Beetle]

Create a query with all the necessary fields from your table, plus the
calculated field, then use the query as the recordsource of your form.
The calculated field will be available in your forms field list just the same
as if it were in the table.

I prefer using queries, rather than tables, for the recorsource because
you can easily do calculations, sort records how you want, etc.

 

[Jeff Boyce]

I'll 'second' Beetle's preference. I use queries as my source for forms,
for exactly the same reasons, plus doing so allows me to have a form that
uses less than all the table's fields, or, if appropriate (e.g.,
display-only), fields from more than one table.

Regards

Jeff Boyce
Microsoft Office/Access MVP