calculated control to table

[Guest]

I realize that Access will not allow moving a calculated control source to a
table. I've read a lot of responses on saying if you want to calculate
something from a table, do it in a query. I really need this value placed in
a table. Either that, or is there any way to create a separate text box on
my form, have it hidden so no one can see it, but have it equal what is in my
calculated text box without using the control source field? I have one text
box doing a dlookup off another combo box, but because I have the formula in
my control source, it won't save to my table. All I want is the text that is
in my calculated field to be placed in my table. Or the text that is in my
calculated to be placed in the hidden text box (not using the control source
field) and then I can use the control source to place it in my table.

Is this possible?

 

[Jeff Boyce]

Matt

If you've seen that the recommendation is to NOT store a calculated value in
your table, what (?unique?) business need do you have that makes storing a
calculated value a necessity?

If you describe "what" your business need is, instead of "how" you are
trying to solve it, the 'group may be able to offer you more viable
solutions.

Good luck

Jeff Boyce
<Access MVP>

 

[Al Camp]

Matt,
Bear with me... read the example, and the last paragraph should give you
what you need...
Usually, you don't save the result of a calculation!
Using this example...
If you had [Price] * [Qty] = LineTotal, you wouldn't have to save the
Line Total. As long as you save Price and Qty, you can always recalculate
LineTotal "on the fly", in any query subsequent query, form, or report.

**If you must save LineTotal...
You probably already have a [LineTotal] field with =Price*Qty in the
ControlSource... (an unbound calculated field).
Remove the calculation from the ControlSource and replace it with the
LineTotal field from your table (=LineTotal), and use the afterUpdate event
of BOTH Price
and Qty to calculate and update the [LineTotal] field.
[LineTotal] = [Price] * [Qty]
Every time Price or Qty change, LineTotal will be updated, and saved to
the table..
hth
Al Camp

 

[Guest]

You can copy all information from a table to a query. By designing a form
off of that query you can have the same controls as a table. You can input
information into the querie's fields and it will store them in the table. It
will do exactly what you want it to do and it has all same effects except you
can sort it and have calculated fields.

I hope this is helpful, I use queries to design forms all the time and use
the tables for restrictions.

 

[Guest]

I know using a query is better to calculate a field, but I'm not adding
1+1=2. The text box that is calculated is a lookup. It's looking in a table
and giving me what's in the second column once someone chooses a company name
off another combo box. Let's say I have a combo box "Box A", and a text box
"Box B", and two tables "Table A" & "Table B". Box A allows a user to choose
a company name off Table A (in the first column). Box B auto fills text that
is in the second column of Table A. Once the person is done entering, it
saves the whole form to Table B.

Is there any way to have say another text box "Box C" that is hidden, but
will autofill from what's in Box B, However it doesn't use the control source
field to do the calculation.

I am going to be exporting Table B into Excel to run additional reports
because not everyone in my company has Access on their machine. If there's a
way to do this in a query, great, but again, I'm not doing 1+1=2.

 

[Jeff Boyce]

Matt

I still don't understand the business need driving this, but perhaps I don't
need to.

Are you aware that you can export a query (result) as well as a table?
Perhaps you only need to build a query that gathers and calculates what you
want, then export THAT, rather than create a new table.

Good luck

Jeff Boyce
<Access MVP>