Regional settings modification

[Rob]

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

[Tom Wickerath]

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]

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]

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.

[Douglas J. Steele]

It's generally considered A Bad Thing to change the user's settings. They
chose those settings, presumably for a reason, so it's our responsibility as
developers to live with their settings. Remember that your application will
not be the only application they're using (and don't forget that they could
be multi-tasking, so the fact that you're planning on resetting to their
original choice when the application is closed doesn't matter)

I know that I would quickly turf any application that changed my settings.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Rob" <R...@discussions.microsoft.com> wrote in message
news:C0D838FE-5006-48EE...@microsoft.com...

[Alex Dybenko]

Hi Rob,
I also came to this problem once, but did not find any good way to solve
this completely.
I am trying to avoid using Currency format, I use "standard" format instead,
which do not show any currency symbol, and if symbol required - I showed it
in a separate field (user can select a favorite one, like Tom suggested).

as for decimal symbol - I think there is no way to solve this, unless you
change regional settings, what, as Doug mentioned, is A Bad Thing. Just
imagine - user have comma as decimal separator, and he enter it in any
program, so he will try to do in yours, but it will not accept it (if you
change it to dot), user will certainly stop using your program.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

"Rob" <R...@discussions.microsoft.com> wrote in message

news:B0070E8F-2FAB-4852...@microsoft.com...