Change Regional options temporary

[Larsson]

Hi!

Problem:
My company have started to work in other countries and now we are sending invoices to our customers in these countries.
We want to make each invoice to be localized so that date and currency is in the correct format.

I have thought a lot about how to do this in Access. So far as I know there is no possibility to change location for a report or a textbox in Access, you have to do in Windows.

Solution 1:
The user that is printing the invoices change her regional options before she print the invoices and then change it back.

Not a good solution, lots of work for the user and easy to forget.

Solution 2:
Change the regional options through code.
I know, you should newer ever change regional options for a user through code.
But here is the exception to the rule.
To make it easier for the user, I have to do it through code.

My question is, how to do this?
How do I change regional options for a user?

Solution 3:
Use another reporting program.
Is there alternatives out there that is as easy as Access reports and where I can change localization?

Soultion 4:
Somethin totaly different, maybe?

 

[Remou]

Perhaps you could set up some code that localises the dates and times? For example, your report would have a function:

=GetCo(Total,Country)

Which would translate to:

Function GetCo(Cur, Co)
Select Case DLookup("LocaleType", "Country", "CoCode=" & Co)
    Case "EU"
        Cur = (Format(Cur, "#,###.00"))
        Cur = Replace(Cur, ".", "|")
        Cur = Replace(Cur, ",", ".")
        Cur = Replace(Cur, "|", ",")
        GetCo = Cur
    Case "US"
        'And so on
End Select

Just a thought

 

[Larsson]

Thats one solution, but I need to add formateing code every time we move into a new country.

I already has a table with country information.
I want to add the country code from windows to this table so that it easy to switch when it's needed.

It's super easy to do with .NET.
But I can't move to .NEt :-(

 

[CautionMP]

Larsson,
If your "table with country information" has the formatting strings in it, and your looking to change the way reports look have you considered an [tt]OnOpen()[/tt] routine for the report that looks at the destination country then loops through all the controls looking for named formats and changes them based on the formatting string in your table?

It's the same concept as Remou suggested but instead of changing your code everytime a new country is added, you just add a record to your country table.

Just a thought,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Remou]

CautionMP
That is more or less what I was thinking of, hence the DlookUp. While there are quite a few countries, there are only a limited number of ways of formatting dates and times. I had thought that countries could be divided into these groups, for example, EU type, US type and so on.

 

[Remou]

But perhaps I have missed the main issue, which is currency. If the bill is in dollars or euros, is it not best formatted for that locale? If the currency is to be converted, then a further step of formatting is surely quite minor compared to the more intricate job of assigning the correct conversion?

 

[Remou]

This is cobbled together, I hope it does not violate any copyrights. I have left in all the constants but have only used a few. It was barely tested (almost tried?) with Access 2000 and Windows XP, with a limited user. The Debug.Print stuff worked, and the computer did not crash, yet.

Nearly everything is from:

http://www.freevbcode.com/ShowCode.asp?ID=116

Change bits

Sub Change_LocaleInfo()

   Dim LCID As Long
   Dim sNewFormat As String
   
   LCID = GetSystemDefaultLCID()
   
   'European #1
   FormatSymb = "€"
   FormatDec = ","
   FormatThou = "."
   FormatSDate = "d.MM.yy"
   FormatLDate = "d MMMM yyyy"
    
   'European #2
   FormatSymb = "€"
   FormatDec = "."
   FormatThou = ","
   FormatSDate = "dd/MM/yyyy"
   FormatLDate = "dd MMMM yyyy"
    
     'set the new long date format
      Call SetLocaleInfo(LCID, LOCALE_SCURRENCY, FormatSymb)
      Call SetLocaleInfo(LCID, LOCALE_SMONDECIMALSEP, FormatDec)
      Call SetLocaleInfo(LCID, LOCALE_SMONTHOUSANDSEP, FormatThou)
      Call SetLocaleInfo(LCID, LOCALE_SLONGDATE, FormatLDate)
      Call SetLocaleInfo(LCID, LOCALE_SSHORTDATE, FormatSDate)
     
     'send a system notification 
      Call PostMessage(HWND_BROADCAST, WM_SETTINGCHANGE, 0&, ByVal 0&)
      
      Debug.Print Format$(10000.56, "Currency")
      Debug.Print Format$(Date, "Short Date")
      Debug.Print Format$(Date, "Long Date")

End Sub

What makes it work

'These declarations are designed
'for use in a .bas module
'since the constants are public


Declare Function GetLocaleInfo Lib "kernel32" Alias _
"GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String, ByVal cchData As Long) As Long

Public Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long

Public Declare Function PostMessage Lib "user32" _
   Alias "PostMessageA" _
  (ByVal hwnd As Long, _
   ByVal wMsg As Long, _
   ByVal wParam As Long, _
   lParam As Any) As Long

Declare Function SetLocaleInfo Lib "kernel32" Alias _
"SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Declare Function GetUserDefaultLCID% Lib "kernel32" ()

Public Const LOCALE_ICENTURY = &H24
Public Const LOCALE_ICOUNTRY = &H5
Public Const LOCALE_ICURRDIGITS = &H19
Public Const LOCALE_ICURRENCY = &H1B
Public Const LOCALE_IDATE = &H21
Public Const LOCALE_IDAYLZERO = &H26
Public Const LOCALE_IDEFAULTCODEPAGE = &HB
Public Const LOCALE_IDEFAULTCOUNTRY = &HA
Public Const LOCALE_IDEFAULTLANGUAGE = &H9
Public Const LOCALE_IDIGITS = &H11
Public Const LOCALE_IINTLCURRDIGITS = &H1A
Public Const LOCALE_ILANGUAGE = &H1
Public Const LOCALE_ILDATE = &H22
Public Const LOCALE_ILZERO = &H12
Public Const LOCALE_IMEASURE = &HD
Public Const LOCALE_IMONLZERO = &H27
Public Const LOCALE_INEGCURR = &H1C
Public Const LOCALE_INEGSEPBYSPACE = &H57
Public Const LOCALE_INEGSIGNPOSN = &H53
Public Const LOCALE_INEGSYMPRECEDES = &H56
Public Const LOCALE_IPOSSEPBYSPACE = &H55
Public Const LOCALE_IPOSSIGNPOSN = &H52
Public Const LOCALE_IPOSSYMPRECEDES = &H54
Public Const LOCALE_ITIME = &H23
Public Const LOCALE_ITLZERO = &H25
Public Const LOCALE_NOUSEROVERRIDE = &H80000000
Public Const LOCALE_S1159 = &H28
Public Const LOCALE_S2359 = &H29
Public Const LOCALE_SABBREVCTRYNAME = &H7
Public Const LOCALE_SABBREVDAYNAME1 = &H31
Public Const LOCALE_SABBREVDAYNAME2 = &H32
Public Const LOCALE_SABBREVDAYNAME3 = &H33
Public Const LOCALE_SABBREVDAYNAME4 = &H34
Public Const LOCALE_SABBREVDAYNAME5 = &H35
Public Const LOCALE_SABBREVDAYNAME6 = &H36
Public Const LOCALE_SABBREVDAYNAME7 = &H37
Public Const LOCALE_SABBREVLANGNAME = &H3
Public Const LOCALE_SABBREVMONTHNAME1 = &H44
Public Const LOCALE_SCOUNTRY = &H6
Public Const LOCALE_SCURRENCY = &H14
Public Const LOCALE_SDATE = &H1D
Public Const LOCALE_SDAYNAME1 = &H2A
Public Const LOCALE_SDAYNAME2 = &H2B
Public Const LOCALE_SDAYNAME3 = &H2C
Public Const LOCALE_SDAYNAME4 = &H2D
Public Const LOCALE_SDAYNAME5 = &H2E
Public Const LOCALE_SDAYNAME6 = &H2F
Public Const LOCALE_SDAYNAME7 = &H30
Public Const LOCALE_SDECIMAL = &HE
Public Const LOCALE_SENGCOUNTRY = &H1002
Public Const LOCALE_SENGLANGUAGE = &H1001
Public Const LOCALE_SGROUPING = &H10
Public Const LOCALE_SINTLSYMBOL = &H15
Public Const LOCALE_SLANGUAGE = &H2
Public Const LOCALE_SLIST = &HC
Public Const LOCALE_SLONGDATE = &H20
Public Const LOCALE_SMONDECIMALSEP = &H16
Public Const LOCALE_SMONGROUPING = &H18
Public Const LOCALE_SMONTHNAME1 = &H38
Public Const LOCALE_SMONTHNAME10 = &H41
Public Const LOCALE_SMONTHNAME11 = &H42
Public Const LOCALE_SMONTHNAME12 = &H43
Public Const LOCALE_SMONTHNAME2 = &H39
Public Const LOCALE_SMONTHNAME3 = &H3A
Public Const LOCALE_SMONTHNAME4 = &H3B
Public Const LOCALE_SMONTHNAME5 = &H3C
Public Const LOCALE_SMONTHNAME6 = &H3D
Public Const LOCALE_SMONTHNAME7 = &H3E
Public Const LOCALE_SMONTHNAME8 = &H3F
Public Const LOCALE_SMONTHNAME9 = &H40
Public Const LOCALE_SMONTHOUSANDSEP = &H17
Public Const LOCALE_SNATIVECTRYNAME = &H8
Public Const LOCALE_SNATIVEDIGITS = &H13
Public Const LOCALE_SNATIVELANGNAME = &H4
Public Const LOCALE_SNEGATIVESIGN = &H51
Public Const LOCALE_SPOSITIVESIGN = &H50
Public Const LOCALE_SSHORTDATE = &H1F
Public Const LOCALE_STHOUSAND = &HF
Public Const LOCALE_STIME = &H1E
Public Const LOCALE_STIMEFORMAT = &H1003

 

[Larsson]

This is almost what I need.
If I just could set the locale (SetLocaleInfo) with a known LCID, then I would be happy.
Because then I can use the different locale settings already in Windows and don't need to write my own.

So two questions.
1. How do I get the LCID that the user already has selected?
GetSystemDefaultLCID gets the default for the system, not the user.

2. How do I set the users locale with a known LCID?

 

[CautionMP]

Larsson,
I was doing some diggin' and ran across the [tt]LanguageSettings[/tt] in the Office object library, have you taken a look at this?
support.microsoft.com ACC2000: Sample Function to Determine Language Version

It looks like it may be a solution, as near as I can tell it will allow you to manipulate the language settings within Microsoft Office, independent of the Windows settings. At the very least I believe it provides an answer to your second question.

Remou,
You’re an animal!

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Remou]

CautionMP
The savage strikes.

The user locale is a user variable that cannot be changed programmatically. The only way to change it is for the user to do so manually.

http://www.microsoft.com/globaldev/getwr/steps/wrg_lclmdl.mspx

From the code above:
[tt]Declare Function GetUserDefaultLCID% Lib "kernel32" ()[/tt]
GetUserDefaultLCID%
Would return the User Default LCID, but it cannot be changed, as far as I can gather from Microsoft.

I have not looked it to this in depth, but the two functions, above and ACC2000: Sample Function to Determine Language Version, return different answers. ACC2000: Sample Function to Determine Language Version seems to give the wrong answer.

 

[Larsson]

I have almost the answer now.
I have read some parts of the link that Remou sent (

http://www.microsoft.com/globaldev/getwr/steps/wrg_lclmdl.mspx).

And I can do what I want with a vbs-file (vbScript), ex:

currentLocale = GetLocale
Original = SetLocale("sv-SE")
MsgBox "Datetime: " & FormatDateTime(Date, vbLongDate)
MsgBox "Currency: " & Formatcurrency("451354,02")
Original = SetLocale(currentLocale)

Change the language-code to different locales to test.
Ex. sv-SE Swedish, en-GB United Kingdom, es-ES Spaine, en-US USA

Now I just have to figure how to do this in Access.

 

[Larsson]

This is the link to the page where I found the vbscript:

http://www.microsoft.com/globaldev/getWR/steps/wrg_date.mspx