Foreign Curency Conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Private Function CalcAmount()
If IsNull(CurrencyCode) or IsNull(ReceiptAmount) then Exit Function
USDAmount = ReceiptAmount / CurrencyCode.Column(2)
End Function
Click to expand...

My script is bugging out at .Column(2)

any ideas why?
 
Is "CurrencyCode" the name of the combo box?

Does it have (at least) three columns?

What do you mean by "bugging out"?
 
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 :]
 
Back
Top