×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Change Regional options temporary

Change Regional options temporary

Change Regional options temporary

(OP)
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?

RE: Change Regional options temporary

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:

CODE

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 ponder

RE: Change Regional options temporary

(OP)
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 sad

RE: Change Regional options temporary

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 OnOpen() 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

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

RE: Change Regional options temporary

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.

RE: Change Regional options temporary

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?

RE: Change Regional options temporary

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

CODE

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

CODE

'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

RE: Change Regional options temporary

(OP)
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?

RE: Change Regional options temporary

Larsson,
 I was doing some diggin' and ran across the LanguageSettings 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

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

RE: Change Regional options temporary

CautionMP
The savage monkey strikes.

Quote:

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:
Declare Function GetUserDefaultLCID% Lib "kernel32" ()
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.  ponder

RE: Change Regional options temporary

(OP)
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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close