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
figs/acb2_0412.gif

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
figs/acb2_0413.gif

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 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.



Access Cookbook
Access Data Analysis Cookbook (Cookbooks)
ISBN: 0596101228
EAN: 2147483647
Year: 2003
Pages: 232

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net