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:

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 subroutine—which does the work of replacing text when you load a form or report—calls 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 headings—that way you won't need the extra step of translating the IDs to names. You could also redesign the solution to use three columns—MsgNum, LanguageID, and ErrorMessage—which 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.