I've been away for a few days, so I didn't get a chance to reply. Are you
looking to convert from a single currency to another one, or from any
currency to any other?
Essentially I think you would need a table of currencies, with a related
table for conversions:
tblCurrency
CurrencyCode (primary key, or PK)
Description (actual name of currency)
tblConversion
ConversionID (PK)
CurrencyCode (linking field to tblCurrency)
ConversionCurrency (the currency to which you are converting)
ConversionRate (Number, probably Double)
EffectiveDate (in case you want to note when the Coversion Rate was applied)
You would have a main form based on tblCurrency, with a subform (continuous,
probably) based on tblConversion. For each main form record, list the
currencies and the conversion rates (one record for each conversion).
For the calculations, the details depend on just how you want to use this,
but one way is that on the main form you could have an unbound combo box
(cboConv) that gets it Row Source from tblConversion. For now I will say it
is a three-column Row Source with the fields CurrencyCode, ConversionCurrency,
and ConversionRate. The criteria for CurrencyCode in the Row Source query
could be:
Forms!frmMain!CurrencyCode
In the main form's Current Event:
Me.cboConv.Requery
The combo box Column Count is 3, the Bound Column is 1, and the Column Widths
are something like 0";1.5";1". There is an unbound text box (txtResult) for
displaying the result. In an unbound text box (txtAmt) you would enter the
amount, then select the currency from the combo box. In the combo box After
Update event:
Me.txtResult = Me.txtAmt * Me.cboConv.Column(2)
Note that the column numbering is 0-based, so Column(2) is actually the third
column (ConversionRate).
If you need to keep a historical record of the conversion rate that applied
to a particular transaction I suggest you store the conversion rate in that
transaction record, but perform the actual calculation on the fly (that is,
do not store it). The details depend on how you plan to use the data and for
what purpose you are making the conversion.
There could be a lot of maintenance if you need to keep currency rates up-to-
date. I suspect there could be improvements to what I have suggested. I
wanted to see if anybody else jumped in to the thread, but they did not, so I
took my best shot at it.
Steve said:
Ah...sorry Bruce.
A currency code would be something like "GBP" then I'd enter "1,000,000"
then I'd enter "USD".
I'd have a list of "FX Rates" in a table, where I'd look up GBP/USD and it
would give me 1.55 lets say, then using that rate I'd multiply it against the
1,000,000.
I'd then like to create a record that I can use stating GBP 1,000,000 USD
1,550,000
Hope I've explained myself....
Probably, but more details are needed. What is a currency code? How is it
used in the calculation? What are FX rates, how do they apply to the
[quoted text clipped - 6 lines]
