how get calculated amount from form to underlaying table

[Guest]

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

 

[Wayne Morgan]

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?

 

[Guest]

One of the basic rules of database design is never store a calculated value.
You have all the data necessary to calculate the amount in the base currency
and the ability to display it. That is all you need. Any time you need to
display it, whether on this form, another form, a report, on in a query,
calculate it at that point.
You are already doing the calculation for a new record, so you have that
under control. To display it for records already in the database, use the
form's Current event to do the same calculation. In fact, I would suggest
you put the calculation routine in a function in a standard module so you can
call it from anywhere and the calculation will be consistant across your
application.

Now that the preaching is over and you are frustrated because I haven't told
you how to do it, I will tell you how, but strongly suggest you don't do it.

If you have a field in your table for storing the calculation, just bind the
control where you display the calculated value to that field.

And one last word....

Don't do it.

 

[Guest]

Thanks to both Wayne Morgan and Klatuu.
I believe you have both given essentially the same advice.
I'd like ask one or both of you one followup question (which requires some
explanation).

From financial accounting standpoint:
1) the original amount and currency is important from standpoint of auditing
2) concurrency conversion of a single transaction is made only once, and
once converted, the calculated value in your base currency is by definition
the correct one.

As a matter of standard practice for the company, employee expenses are
organised by calendar month. Currency conversion is accomplished by looking
up an average rate exchange rate over that month. I have created a database
table that stores exchange rate data by currency and by month.

So, provided nothing changes in the currency database, I can see that I
could always re-create the calculation. But, if the currency database ever
alters, then I will have a gap in the audit trail.

However, if I store the calculated value and the original value, then there
can never be a gap in the audit trail (and the conversion rate used is
implicit in the two currency values).

I am not an expert in databases or in accounting, however, in the
comprehensive accounting database programmes I've used, they go to great
lengths to ensure that a currency conversion for individual transactions
can't be changed.

So the question ==>

It did (and still does) seem to me that it is correct to hard wire the
currency conversion calculation into the data.

However, I understand your advice and see that it can work provided nothing
ever changes the currency conversion table.

I light of my accounting/auditing concern, do you still advise me not to
store the calculated value?

regards,
slowuptake

 

[Wayne Morgan]

Historical tracking is one reason to store a calculated value. However,
rather than worrying about the conversion factor table changing, I would let
it change. Instead I would also modify how it changes. I would include a
date field that would indicate when the change was made, that way you would
know the value on the date the transaction was made, even if the rate for
that month was changed later; especially since you're keeping that data
already anyway. (i.e. each change should be a new record in conversion
table)

 

[Guest]

After studying practical implications of advice from both Wayne and Klatuu, I
think I'll store the calculated value - but manually instead of automatically.

I've started to turn my attention to a help facility that sits within the
employee expense input form, contains both foreign exchange rates and agreed
rates and values for the different projects, and allows employee to quickly
calculate expenses ... but requires ultimate expense values and descriptions
to be manually input by user.

Thank you both for your advice. It changed my approach to the problem in a
useful way.

regards,
slowuptake

 

[Guest]

Although I would take Wayne's approach, I can understand your decision to
store the value. However, from an audit standpoint, will you be able to
demonstrate to an auditor how the calculation was done and what the exchange
rate was at the time of the calculation. That would be even more important
to the auditor than would be the actual value.

Now, the way to store it programmatically would be to bind a textbox on your
form to the field in the table where your calculated amount will be carried.
Since it is a calculated amount, you would at least want the text box on the
form to be locked so it could not be changed manually. If you don't want the
users to see that value, then make the text box invisible. In both cases,
you will want the tab stop property of the text box to be No.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

I guess I'm confusing myself. I need to pick a future date option in three
places on a table. The choices are to add 15, 30, 45, or 60 days to the date
of an event (record). So I created a FutureDates Lookup Table. If I need
the info in three places, I guess I'd need 3 FutureDates Lookup Tables
(FutureDateTbl1, FutureDateTbl2, FutureDateTbl3)? I can't reference the same
lookup table three times in the same query without them all being the same
choice.

I started down the path of putting an unbound combo box on the form for the
lookup and the field calculated just fine. My problem was getting the result
into the underlying query so I could use the result for reports. I guess the
paragraph above makes the most sense. I was headed for SetValue in a hidden
box, but guess this won't work either. I know you don't put calculations
into tables. Forms and Reports lie, tables never do.

 

[Guest]

If you could be more specific, we can resolve this. What I was suggesting
would work if structured correctly, not require a future dates table, and
would run faster because it would not have to do the lookups.

If I knew what you mean by needing the data in 3 places, it would be useful.
Also, how do you know how many days to add?

Essentially, you would use a calculated field in the query something like
this:

FutureDate: DateAdd("d", ??howmanyDays, originaldate)

 

[Guest]

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...

 

[Guest]

I don't know if I fully understand everything you need to do; however, from
what I do understand, it appears you could put a command button on the main
form and put the code in it's Click event to do whatever needs to be done.

 

[Guest]

Interesting..so if I follow you, creating a command button to display in a
text box from the calculated data from a text box on the subform isn't the
same as storing the calculated field in the table. Yes?

I think another reason I'm having a hard time getting the vb code right on
this after update thing is that the calculated field is on the subform but
the field I want it captured in is actually on the main form.

 

[Guest]

Interesting..so if I follow you, creating a command button to display in a
text box from the calculated data from a text box on the subform isn't the
same as storing the calculated field in the table. Yes?

You are correct. Creating a table, populating it, and retrieving the data
from it will be a lot slow and will add to database bloat. One technique to
use would be to construct a query that would calculate the value you need,
open it as a recordset, and copy the value into the text box.

I think another reason I'm having a hard time getting the vb code right on
this after update thing is that the calculated field is on the subform but
the field I want it captured in is actually on the main form.

It is just a matter of correct syntax to get what you want. Also, I would
suggest you have a look at the orders form and orders sub form in the
Northwind database. It has an example where there is a calculation performed
on each row in the sub form that updates a text box (if I remember correctly)
the subform footer section which is hidden, then picked up by a text box on
the main form. See if something like that might work for you.

 

[Guest]

Thanks for your help- I will see if I can get that to work.

 

[Guest]

Wayne,
I have a quick question about your directions for storing the calculated
field that I'm hoping you can clarify for me.
My calculated field is on a subform footer that gives a tally for all those
records on the subform. I need the stored calculated field on the MAIN form,
so I have the sql statement
=[frm subform test].Form!Text36
which, indeed, displays the same correct data as the subform footer field.
However, how in the heck do I get it to actually save in the main form table?
Specifically, you had written to write a code on the corm to copy the value
fromtha calculated text box to the hidden text box but I don't know the vb
jargon well enough to create that. Can you help?

 

[Guest]

Hi, sorry, me again...so I should create a query, then create a macro that
runs the query and displays the data in a text box, and finally have the
command button execute the whole thing.

 

[Guest]

Almost. You don't actually run the query. You use it as a recordset to
retrieve the values and populate the text box.

 

[Guest]

Hi,
Could you please elaborate for me the vb code for the before update on the
form? My calculated field is on the subform, which tallies the subform
records. However, I need to store that value on the MAIN form. I have a
total field that displays the correct data using a sql statement:
=[frm subform test].Form!Text36
But the value doesn't store in that field. I create another txt box on the
main table, added it to the form and bound the control to the total field,
but it didn't store the value in the table. I'm obviously doing something
very wrong. Can you help?