Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 4.6 Internationalize Text in Your Applications

4.6.1 Problem

You'd like to be
able to pop up translated error messages in your applications, based
on the currently running language version of Access.
You'd also like other text on your forms and reports
to adjust automatically based on the current language version. You
know there are a number of ways to do this, but you
can't decide which is best. How should you store and
retrieve messages in multiple languages?

4.6.2 Solution

The translated version of Access handles its own error messages (in
the German version, for example, the Access error messages appear in
German). But you do need to translate your own messages if you want
your application to run smoothly in other languages. Though there are
several methods of handling text, the most generic solution uses a
table of messages, which you can look up by ID number.

Load and run the form frmTestMessage
from

04-06.MDB . This form, shown in Figure 4-12, allows you to choose from three different
languages (English, French, and Spanish) in an option group. As you
choose each language, code attached to the option
group's AfterUpdate event changes accordingly the
captions for labels on the form and the status-bar text for text
boxes. To try a sample error message in the chosen language, click
the Test Message button.

Figure 4-12. The sample form, frmTestMessage, showing the French test error message

In each case, the messages are coming from the table tblMessages.
This table includes a column for the message identifier (the primary
key) and one column for each of the languages your application
supports. Figure 4-13 shows the table, filled in for
the sample application.

Figure 4-13. The message table, tblMessages, filled in for the sample application 04-06.MDB

To include similar functionality in your own applications, follow
these steps:

1. From

   04-06.MDB , import the modules
   basFileLanguage (which includes the procedures from the Solution in
   Recipe 4.5 for obtaining the current
   language version of Access) and basGetMessages (which looks up
   particular messages in tblMessages).

2. From

   04-06.MDB , import the table tblMessages.
   This is the table you'll use to hold your messages.
   Delete the existing rows, if you like. Also, you can modify the
   structure and add more languages if necessary.

3. Add the necessary rows to tblMessages, filling in each column with
   the translated text, as shown in Figure 4-13.

4. On any form for which
   you'd like to have language-sensitive captions and
   status-bar text, place the message ID (the MsgNum column from
   tblMessages) in the Tag property for the control whose text
   you'd like to change. For labels, the code
   you'll call is set up to change the Caption
   property; for text boxes, the code is set up to change the
   StatusBarText property. (If you want to include other control types,
   you can modify the code in the subroutine
   

   GetInfo , as described in Recipe 4.6.3.)

5. To set the captions for labels and
   the status-bar text for text boxes when your form loads, place the
   following code in the Open event procedure for your form:

   Private Sub grpLanguage_AfterUpdate( )
   acbSetText Me, Me.grpLanguage
   End Sub

   The

   acbSetText
   subroutine walks through all the controls on your form, searching for
   ones with a numeric value in the Tag property. For any such controls,
   it looks up the appropriate message and assigns it to the Caption or
   StatusBarText property.

4.6.3 Discussion

The technique presented in this solution includes two basic pieces of
functionality: retrieving the correct messages from the table of
messages and replacing all the required property values on your form
or report. Together, these two operations accomplish the goals of
changing labels and status bar text and providing translated error
messages.

The

acbGetMessage function retrieves the
messages you need from tblMessages. You pass to it, as parameters, a
long integer specifying the message number you want and an integer
specifying the correct language.

Public Function acbGetMessage( _
ByVal lngMessage As Long, _
ByVal lngLanguage As Long) As Variant
' Retrieve a message from tblMessages, given a message
' ID and a language.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varLanguage As Variant
Dim varResult As Variant
On Error GoTo HandleErr
varResult = Null
Set db = CurrentDb( )
Set rst = db.OpenRecordset("tblMessages", dbOpenTable)
With rst
If Not .EOF Then
' Set the index, which is the message number
.Index = "PrimaryKey"
.Seek "=", lngMessage
If .NoMatch Then
' You could raise an error here,
'  but we're just returning a null value.
varResult = Null
Else
varLanguage = GetLanguageName(lngLanguage)
If Not IsNull(varLanguage) Then
varResult = rst(varLanguage)
Else
varResult = Null
End If
End If
End If
End With
ExitHere:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
acbGetMessage = varResult
Exit Function
HandleErr:
varResult = Null
MsgBox Err.Number & ": " & Err.Description, , "acbGetMessage"
Resume ExitHere
End Function

This function starts by creating a table-type recordset based on
tblMessages:

Set rst = db.OpenRecordset(acbcMsgTable, dbOpenTable)

If there are any rows in tblMessages, the function looks for the row
you've requested. If it doesn't
find a match, you must have requested a message number
that's not in the table, so the function returns
Null:

With rst
If Not .EOF Then
' Set the index, which is the message number.
.Index = "PrimaryKey"
.Seek "=", lngMessage
If .NoMatch Then
varResult = Null

If
it does find a match, it converts the language number into the
table's column name for the language (using the

GetLanguageName function). If it finds a
language name, it retrieves the appropriate message from tblMessages:

      Else
varLanguage = GetLanguageName(intLanguage)
If Not IsNull(varLanguage) Then
varResult = rst(varLanguage)
Else
varResult = Null
End If
End If
End With

If any error occurs along the way,

acbGetMessage
returns Null. If things work out, it returns the
message it found in tblMessages.

You can call

acbGetMessage directly (e.g., to
fill the text for a message box or to build up a more complex error
string). In addition, the

acbSetText
subroutinewhich does the work of replacing text when you load
a form or reportcalls

acbGetMessage
multiple times, once for each message.

The

acbSetText procedure takes two parameters:
an object containing a reference to the form or report, and the
language ID. The procedure walks through all the controls on the
requested form or report, calling the

GetInfo
function for each. The complete text of the

acbSetText procedure is:

Public Sub acbSetText(obj As Object, ByVal lngLanguage As Long)
' Set text for labels (caption) and text boxes (status-bar
' text) on the specified report or form.
Dim ctl As Control
For Each ctl In obj.Controls
Call GetInfo(ctl, lngLanguage)
Next ctl
End Sub

The

GetInfo subroutine does the actual work;
this is the procedure you'll need to change if you
want to handle more than just labels' Caption
properties and text boxes' StatusBarText properties.
It checks the Tag property and, if it's numeric,
looks up the associated text string in the appropriate language. Once
it has the string, it checks the control type and places the string
in the correct property for the given control type. The complete
source code for the

GetInfo subroutine is:

Private Sub GetInfo(ctl As Control, lngLanguage As Long)
' Given a control and a language, look up the label
' or status-bar text for it.
Dim varCaption As Variant
With ctl
If IsNumeric(.Tag) Then
varCaption = acbGetMessage(.Tag, lngLanguage)
If Not IsNull(varCaption) Then
Select Case .ControlType
Case acLabel
.Caption = varCaption
Case acTextBox
.StatusBarText = varCaption
End Select
End If
End If
End With
End Sub

If you want to support more languages than just the three used in
this example, you'll need to modify the structure of
tblMessages (adding a new column for each new language) and modify
the

GetLanguageName procedure in the
basGetMessage module. As it is now,

GetLanguageName looks like this:

Private Function GetLanguageName( _
ByVal lngLanguage As Long) As Variant
' Make sure to set a reference to the Office Library.
' Given a language identifier, get the column name in
' tblMessages that corresponds to it. This function
' expects, for lngLanguage:
'   msoLanguageIDEnglishUS (1033),
'   msoLanguageIDSpanish (1034), or
'   msoLanguageIDFrench (1036).
Dim varLang As Variant
Select Case lngLanguage
Case msoLanguageIDEnglishUS
varLang = "English"
Case msoLanguageIDFrench
varLang = "French"
Case msoLanguageIDSpanish
varLang = "Spanish"
End Select
GetLanguageName = varLang
End Function

Add more cases to the
Select Case statement, matching
the new columns in your messages table. The constants come from the
Office Library, a reference to which you must add to your project. An
alternative approach is to use the language IDs themselves as the
column headingsthat way you won't need the
extra step of translating the IDs to names. You could also redesign
the solution to use three columnsMsgNum, LanguageID, and
ErrorMessagewhich would make adding a language a matter of
adding records rather than modifying code.

The sample form contains only a few controls. Attempting to modify
the properties of several hundred controls would noticeably increase
load time for a form. For forms that contain many controls, you might
be better off creating one version of the form per language and
distributing translated versions of your application. Alternatively,
you could preload the form invisibly when your application starts up
so that it appears instantly when made visible.

Another problem you should consider when attempting to modify
captions on the fly is that many non-English languages take more
space to present the same information. You'll find
that some languages require twice as much space (or more) for a given
text string. This may mean that dynamic translation
isn't feasible, due to real-estate problems. Again,
the best solution is to plan the translated versions carefully and
prepare a different set of forms and reports for each language, or to
leave enough space for the most verbose language you need to support.
You could also include width values for each language and adjust the
controls as needed, but this would get complicated because you would
also need to adjust their positions and perhaps even the size of the
form. A comprehensive solution would require you to store many
property values for each control and for each form and report.

Message boxes don't
present such a problem, of course, because Access automatically
resizes them to fit the data you send to them. The same goes for
ControlTipText. Call the

acbGetMessage function
to provide the text for any message box you wish to fill, as in this
example:

Call MsgBox(acbGetText(intLanguage, 1), vbExclamation, acbGetText(intLanguage, 2))

You can use this technique to alter any messages within your
application at runtime. For example, if you want to provide different
levels of help for different users, you can keep all your messages in
a table and retrieve the correct help messages depending on who the
current user is. In this case, rather than looking up language names,
you'd be looking up user or group names.