Re: how get calculated amount from form to underlaying table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I know I'm late to this party but I have a related problem that I hope you
can assist me with and I always benefit from reading your posts.

I don't want to get too wordy and confusing but here's my situation:

I have a form based on a query that includes a ysn field and specific txt
field. There is a subform (continuous) that displays the sum from one
subform record and then the total for all subform records in the subform
footer.

What I want to do is run a query based on the main form's ysn and txt fields
that also displays the calculated subform footer. Once the query is run, I
need the user to be able to click the ysn field in order to update it without
having to go record by record in the form. The problem is, you can't click
into the ysn field in the query.

My only other option is to create the main form on a table, not a query,
however, then I don't have access to the calculated field, which I
desperately need. I can't do that, though, because I need to base the form
on 2 tables. I tried creating a form without querying those 2 tables - but
it didn't work because one of them has a primary key that I am using for a
combo box that I need to have multiple records for. Have I lost you yet?

So....if there is another way to store this data that I am unaware of that
doesn't involve my user to have to run make table queries, alter ysn fields
there and then run update queries (way too much room for error) then please,
I'm "all ears!". Thanks in advance...

"Klatuu" wrote:

Why not do the calculation in the query?
Storing calculated values in a table is almost always a bad idea.

"JDJones" wrote:

Item 2 - How do I copy the value from the calculated textbox to the hidden
textbox? I'm missing something basic here. I have a very similar situation
in that my calculation is only on the form and I need to get it to a query
for a report.

"Wayne Morgan" wrote:

You have a couple of options here,

1) store the exchange rate information, including the date it was in effect.
You could then use this to redo the calculation when necessary.

2) store the calculated value.

To store the calculated value, create a hidden textbox bound to the field
you want to store the value in. In the form's BeforeUpdate event, copy the
value from the calculated textbox to the hidden textbox. This value will now
be stored in the table when the record is saved.

There is another potential problem, what will you do when you review the
records? Do you use a different form that will have a textbox bound to the
field you stored the calculated value in?

--
Wayne Morgan
MS Access MVP


"slowuptake" <slowuptake@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6FDD38C1-0A07-4930-B27B-644373116763@xxxxxxxxxxxxxxxx
I have employee expense database. Due large amounts foreign travel, want
to
add foreign currency capacity into the expense input form - and record
into
the underlaying table both the original amount and currency and the amount
in
base currency.

Foreign currency amount entered by way of text box - no problems.

Combo box allows user to choose which foreign currency from a list - no
problems.

List box looks up current exchange rate based on combo box choice - no
problems.

A separate text box calculates and displays the amount in my base
currency -
no problems.

Problem - I can't figure out how to get calculated amount to be
automatically written into underlaying database. As temporary solution I
now
manually retype the result from last text box.

I suspect the problem isn't that hard, but as usual, I'm ... slowuptake




.

Relevant Pages

  • Re: how get calculated amount from form to underlaying table
    ... You don't actually run the query. ... text box from the calculated data from a text box on the subform isn't the ... It has an example where there is a calculation performed ... Foreign currency amount entered by way of text box - no problems. ...
    (microsoft.public.access.forms)
  • Re: how get calculated amount from form to underlaying table
    ... runs the query and displays the data in a text box, ... text box from the calculated data from a text box on the subform isn't the ... It has an example where there is a calculation performed ... Foreign currency amount entered by way of text box - no problems. ...
    (microsoft.public.access.forms)
  • Re: how get calculated amount from form to underlaying table
    ... store the exchange rate information, including the date it was in effect. ... You could then use this to redo the calculation when necessary. ... > the underlaying table both the original amount and currency and the amount ... > Foreign currency amount entered by way of text box - no problems. ...
    (microsoft.public.access.forms)
  • Re: how get calculated amount from form to underlaying table
    ... What I want to do is run a query based on the main form's ysn and txt fields ... You could then use this to redo the calculation when necessary. ... the underlaying table both the original amount and currency and the amount ... Foreign currency amount entered by way of text box - no problems. ...
    (microsoft.public.access.forms)
  • Re: how get calculated amount from form to underlaying table
    ... So I created a FutureDates Lookup Table. ... You could then use this to redo the calculation when necessary. ... the underlaying table both the original amount and currency and the amount ... Foreign currency amount entered by way of text box - no problems. ...
    (microsoft.public.access.forms)