By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

multilingual Access 2002 application (best practices)

P: n/a
Hi all

I have to localize an Access 2002 application: The application using
several form, tables etc. is currently only in English. Now the
frontend has to be bilingual, so the user can choose if he wants the
form content in English or in French. I have never done something like
this before with Access. One way I think would be like that:

- creating a table tblLanguage with the following fields:
- FormName
- ControlName
- ControlType
- English
- French

- on the forms Open event, i would loop through all the items
collection and set the captions of all labels, comboboxes, buttons
etc. according to the appropriate language value in tblLanguage.

Is this the common way to do such a task? I've heard about the
'Multilingual User Interface Pack' for Office XP, but I think this is
only to change the language for the menues, windows messages etc.,
right? Or would I be able to create multilingual form with the
'Multilingual User Interface Pack'? I couldn't figure it out visiting
the microsoft website.

Any advice would highly be appreciated!

Thank you,

Juerg
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On 15 Oct 2003 09:52:17 -0700, de**@simsy.ch (J?rg Keller) wrote:
Any advice would highly be appreciated!


Hello, I've done this recently and it's working very nice.
I have set up a table 'tblVertaling' with two fields.
- Number
- Text
I've had reserved 1000 numbers for each language used (you could
reserve more if necessary, but 1000 is already much).
for instance 1-1000 = Dutch, 1001 to 2000 = English ...
The table has been filled in like this:
1 Annuleren
1001 Cancel
2001 cancel in other language
and so on

I've declared a public variable
Public lnglanguage As Long

You should set this variable for instance by opening your app. In my
example for dutch this would be 0, and for english this would be 1000.

In the onopen event of forms, reports (I've only used it for reports)
I put code like this:

************************************************** ***************
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is Label Then
If ctl.Tag <> "" And Not IsNull(ctl.Tag) Then
ctl.Caption = LoadString(ctl.Tag + lnglanguage) 'so eg 1 +
1000 is number 1001 out the table
End If
End If
Next ctl
************************************************** ***************
With the loadstring I call this procedure that resides in a module
pasted below.
Notice that I put the number of the translation required in the
tagfield of every object.
eg. a label that says 'Hello There!' (and has number 2 in the table
with translations) has in the tag the number (2) referencing the one
in the table with translations.

************************************************** ***************
Function LoadString(lngNumber As Long) As String
On Error GoTo HandleErrors
Dim strLoadString As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT tblvertaling.* FROM tblvertaling WHERE
tblVertaling.number = " & lngNumber & ";"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
If .RecordCount <> 0 Then
.MoveFirst
strLoadString = !Text
End If
End With

exithere:
On Error Resume Next
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
LoadString = strLoadString
Exit Function
HandleErrors:
MsgBox "basVertaling - LoadString:" & vbNewLine & Err.Number & " "
& Err.Description
Resume exithere
End Function
************************************************** ***************

Hopes this clear. One could always point me at bad code, or better
ways of handling this.
regards,
--
bebelino
Nov 12 '05 #2

P: n/a
On 15 Oct 2003 09:52:17 -0700, de**@simsy.ch (J?rg Keller) wrote:
Any advice would highly be appreciated!


Hello, I've done this recently and it's working very nice.
I have set up a table 'tblVertaling' with two fields.
- Number
- Text
I've had reserved 1000 numbers for each language used (you could
reserve more if necessary, but 1000 is already much).
for instance 1-1000 = Dutch, 1001 to 2000 = English ...
The table has been filled in like this:
1 Annuleren
1001 Cancel
2001 cancel in other language
and so on

I've declared a public variable
Public lnglanguage As Long

You should set this variable for instance by opening your app. In my
example for dutch this would be 0, and for english this would be 1000.

In the onopen event of forms, reports (I've only used it for reports)
I put code like this:

************************************************** ***************
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is Label Then
If ctl.Tag <> "" And Not IsNull(ctl.Tag) Then
ctl.Caption = LoadString(ctl.Tag + lnglanguage) 'so eg 1 +
1000 is number 1001 out the table
End If
End If
Next ctl
************************************************** ***************
With the loadstring I call this procedure that resides in a module
pasted below.
Notice that I put the number of the translation required in the
tagfield of every object.
eg. a label that says 'Hello There!' (and has number 2 in the table
with translations) has in the tag the number (2) referencing the one
in the table with translations.

************************************************** ***************
Function LoadString(lngNumber As Long) As String
On Error GoTo HandleErrors
Dim strLoadString As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT tblvertaling.* FROM tblvertaling WHERE
tblVertaling.number = " & lngNumber & ";"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
If .RecordCount <> 0 Then
.MoveFirst
strLoadString = !Text
End If
End With

exithere:
On Error Resume Next
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
LoadString = strLoadString
Exit Function
HandleErrors:
MsgBox "basVertaling - LoadString:" & vbNewLine & Err.Number & " "
& Err.Description
Resume exithere
End Function
************************************************** ***************

Hopes this clear. One could always point me at bad code, or better
ways of handling this.
regards,
--
bebelino
Nov 12 '05 #3

P: n/a
Hi,
...
- creating a table tblLanguage with the following fields:
- FormName
- ControlName
- ControlType
- English
- French
How about value lists in combo and list boxes?
How about messages, tool tips, menus?
- on the forms Open event, ...
In case the user changes language while more than one open is open, all
other forms need to be updated too. Better use Activate.
... i would loop through all the items
collection and set the captions of all labels, comboboxes, buttons
etc. according to the appropriate language value in tblLanguage. Is this the common way to do such a task?


That's at least how I do it.

Peter

--
No mails please.
Nov 12 '05 #4

P: n/a
On Wed, 15 Oct 2003 21:18:16 +0200, Peter Doering <no****@doering.org>
wrote:
How about value lists in combo and list boxes?
How about messages, tool tips, menus?


One can easily call the LoadString(languagenr) in code.
So displaying a messagebox could be done like this:

'52 = Hello there
msgbox loadstring(52 + lnglanguage), vbinformation

value lists, tooltips, menus: all can be set via code and the
loadstring.
I really don't know any other way. At least at this moment.
- on the forms Open event, ...


In case the user changes language while more than one open is open, all
other forms need to be updated too. Better use Activate.


Yes, it seems that the activate-event is a better choice.
--
bebelino
Nov 12 '05 #5

P: n/a
I have an example file with the same technique. Email me if interested.

"J?rg Keller" <de**@simsy.ch> wrote in message
news:99**************************@posting.google.c om...
Hi all

I have to localize an Access 2002 application: The application using
several form, tables etc. is currently only in English. Now the
frontend has to be bilingual, so the user can choose if he wants the
form content in English or in French. I have never done something like
this before with Access. One way I think would be like that:

- creating a table tblLanguage with the following fields:
- FormName
- ControlName
- ControlType
- English
- French

- on the forms Open event, i would loop through all the items
collection and set the captions of all labels, comboboxes, buttons
etc. according to the appropriate language value in tblLanguage.

Is this the common way to do such a task? I've heard about the
'Multilingual User Interface Pack' for Office XP, but I think this is
only to change the language for the menues, windows messages etc.,
right? Or would I be able to create multilingual form with the
'Multilingual User Interface Pack'? I couldn't figure it out visiting
the microsoft website.

Any advice would highly be appreciated!

Thank you,

Juerg

Nov 12 '05 #6

P: n/a
On Wed, 15 Oct 2003 19:31:31 GMT, bebelino wrote:
How about value lists in combo and list boxes?
How about messages, tool tips, menus?
One can easily call the LoadString(languagenr) in code.
So displaying a messagebox could be done like this: ...


I placed my question below the OP's language table which is laid out for
captions only. Value lists, menus/sub-menus etc. require a different table
layout. Displaying them is no problem.

Peter

--
No mails please.
Nov 12 '05 #7

P: n/a
de**@simsy.ch (J?rg Keller) wrote:
I have to localize an Access 2002 application: The application using
several form, tables etc. is currently only in English. Now the
frontend has to be bilingual, so the user can choose if he wants the
form content in English or in French. I have never done something like
this before with Access. One way I think would be like that:

- creating a table tblLanguage with the following fields:
- FormName
- ControlName
- ControlType
- English
- French

- on the forms Open event, i would loop through all the items
collection and set the captions of all labels, comboboxes, buttons
etc. according to the appropriate language value in tblLanguage.


One comment one person had a while back was to loop through all the records in the
recordset updating the controls. This is much faster than looping through the
controls collection and doing an indexed read into tables for each control.

You may want to have common transalations for controls, ie "Customer" vs equivalent
in French would appear on many controls in many forms, to appear once in table.
Thus you may wish to have an "expander" routine which will create records in the
table you mention for each occurence of "Customer"

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.