Foreign Curency Conversion

[Guest]

Hiyas

I've got an Expense Report form in Access for our International travelers.
Sometimes (quite often, really) their receipts are in Euros or whathaveyou.
I've a basic subform inputing the expense data, like this:

DATE | EXPENSETYPE | DESCRIPTION | CURRENCY | AMOUNT

I'd like to have this:

DATE | EXPENSETYPE | DESCRIPTION | CURRENCY | RECEIPTAMOUNT | USDAMOUNT

where "USDAMOUNT" is performing a calculation like this:

If CURRENCY is not "United States"
Then
If CURRENCY is like "Euro"
...convert by whatever the "ExchangeRate" for that currency is as per
T-Currency
...loop through all other 16 currencies stored in the table T-Currency

I have the needed exchange rates stored in table T-Currency as field
"ExchangeRate".

What say you?

/amelia

 

[Graham Mandeno]

Hi Amelia

Your T-Currency table should have three fields: CurrencyCode (text(3),
primary key), CurrencyName (text, no duplicates), and ExchangeRate (single
or currency), with values like this:

USD US Dollar 1
EUR Euro 0.89
GBP Pound Sterling 0.53
....etc

You should then set up a relationship between the "CurrencyCode" field in
your expenses table and the primary key (CurrencyCode) in T-Currency.

Now, change the textbox under "CURRENCY" on your form to a combo box, with
these properties:
Name: CurrencyCode
ControlSource: CurrencyCode
RowSource: Select CurrencyCode, CurrencyName, ExchangeRate
from [T-Currency] order by CurrencyName;
Columns: 3
BoundColumn: 1
ColumnWidths: 0;;0
AfterUpdate: =CalcAmount()

Also, set the AfterUpdate property of your ReceiptAmount textbox to:
=CalcAmount()

Now, in your form's module, add the following function:

Private Function CalcAmount()
If IsNull(CurrencyCode) or IsNull(ReceiptAmount) then Exit Function
USDAmount = ReceiptAmount / CurrencyCode.Column(2)
End Function

Now, when you select a currency from your combo box, the current exchange
rate will be instantly available in Column(2) (the *third* column), so when
both the currency and the receipt amount have been entered, the USDAmount
can be easily calculated.

You might also want to check if the USDAmount already contains a value, and
give the user the option to either recalculate it or cancel and undo the
changes.

 

[Guest]

Private Function CalcAmount()

If IsNull(CurrencyCode) or IsNull(ReceiptAmount) then Exit Function
USDAmount = ReceiptAmount / CurrencyCode.Column(2)
End Function

My script is bugging out at .Column(2)

any ideas why?

 

[Graham Mandeno]

Is "CurrencyCode" the name of the combo box?

Does it have (at least) three columns?

What do you mean by "bugging out"?

 

[Guest]

CurrencyCode is the name of the combo box.

by "bugging out" i mean, the script debugger pops up there. VB seems most
unhappy with it :]

 

[Graham Mandeno]

But what is the error message?