RE: Regional settings modification
- From: Tom Wickerath <AOS168b AT comcast DOT net>
- Date: Wed, 17 Jan 2007 03:31:01 -0800
Hi Rob,
The following seems to work for me, using the sample Northwind database.
First, create the following table and name it "tblCurrencyFormats" (without
the quotes, of course):
tblCurrencyFormats
CurrencyCode Text (Primary Key)
Country Text Indexed (No Duplicates)
CurrencyName Text
CurrencyFormat
Populate this table with data for some of the countries represented in the
Northwind Customers table (there are 21 unique countries represented). I used
the following web page as a source of data:
http://www.xe.com/symbols.htm#list
Here are some sample entries:
CurrencyCode Country CurrencyName CurrencyFormat
BEF Belgium Francs (obsolete) ₣ 0.00
BRL Brazil Reais R$ 0.00
CAD Canada Dollars $ 0.00
DKK Denmark Kroner kr 0.00
FRF France Francs (obsolete) ₣ 0.00
GBP Britain Pounds £ 0.00
Copy and paste the following code into a new form module for the "Customer
Orders Subform2" form:
Option Compare Database
Option Explicit
Private Sub Form_Current()
On Error GoTo ProcError
Dim strFilter As String
Dim varResult As Variant
' Evaluate filter before it's passed to DLookup function.
strFilter = "Country = '" & Me.Parent.Form.Country & "'"
Debug.Print Me.Parent.Form.Country, _
DLookup("CurrencyFormat", "tblCurrencyFormats", strFilter)
Me![UnitPrice].Format = Nz(DLookup("CurrencyFormat", _
"tblCurrencyFormats", strFilter), "$ 0.00")
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Current of Customer Orders Subform 2..."
Resume ExitProc
End Sub
Save the code module. Click on Debug > Compile Northwind. Hopefully, your
new module will compile without any errors. Close the subform. Then open the
"Customer Orders" form and scroll through the records.
Notes:
1.) No currency conversions attempted! This is merely for demonstration
purposes only. Also, I don't really know if the 0.00 part is accurate in all
cases.
2.) I had to update the Country field in the Customers table from UK to
Britian (since I wanted this table to be a copy of the web site).
3.) I'm using $ 0.00 as the default result, in the event that a country is
not present in the tblCurrencyCodes table. This is done by wrapping the
result of the domain aggregrate function, DLookup, with the Nz (Convert Null)
function. My bias showing!
4.) You could just as easily create a startup form with a combo box, where
the user picks their country the first time. In the AfterUpdate event of the
combo box, set all records to 0 (false). Then set a boolean (Yes/No) data
type to -1 (true) for the selected record. Only one record would be checked
for this field. Then use this field in the criteria expression for the
DLookup.
Hope this helps.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
"Rob" wrote:
Hello Tom, thank you for replying.
I do not think it is as simple as that (alltough i would like it to be so....)
Let me explain a bit more:
I have an application with about 900 money form fields over 90 different
forms.
This application is intended for different countries and differend currency
symbols.
I know how to achieve the correct formating using the locale regional
setting and with Me![fieldname].Format = "Currency" one can apply those
settings.
But now i was contacted by a user who has an english regional setting (and
want's to keep it) and who would still like to get values in the fields like
1 000,00 € for instance without changing his regional settings. Changing his
settings also affects his other applications and he has to switch over all
the
time.
Meanwhile, other users want to have 1 000,00 FrS for instance....
So i defined a public variable 'moneyunit' to represent the currency symbol
and changed my format statements as follows:
Me![fieldname].Format = "#,##0.00 " & moneyunit & ";-#,##0.00 " & moneyunit
& "; ; "
Ok so good, but now the thousand separator symbol and decimal symbol are
giving me: 1,000.00 € with english regional settings and not 1 000,00 €.
They are still derived from the regional settings.
So i was wondering if it is possible to define ones own money formating
string or to influence the regional settings on startup and set it back to
the original country afterwards.
Any suggestion?
Rob
"Tom Wickerath" wrote:
Hi Rob,
I don't think there is any need to change the regional setting to accomplish
this. I live in the US, where $ is the currency setting. A few years ago, I
helped on an Access application for the Merceyside Police Dept. in England. I
simply copied the pound symbol and used it in the format property for text
boxes. It worked just fine. For example: Format: £ 0.00
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
"Rob" wrote:
Hi all,
to follow up on a still open question about currency formating, could
someone tell me if it is possible to change the computers regional settings
form currency with vb-code on startup of an Access2003-application and set it
back again when quitting?
This is to achieve a customised currency format that would not imply
permanent display changes for other applications (unless they are run
simultaniously).?
Many thanks in advance
Rob
- References:
- RE: Regional settings modification
- From: Rob
- RE: Regional settings modification
- Prev by Date: RE: Regional settings modification
- Next by Date: Re: Regional settings modification
- Previous by thread: RE: Regional settings modification
- Next by thread: Re: Regional settings modification
- Index(es):
Relevant Pages
|
