This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Bogdan Zamfir

Modern programming environments offer many features that enable software applications to be developed with multi-language support. However, what many companies need is an application that can be deployed in different languages in different locations or for different employees. Bogdan Zamfir shows how to create a multi-language application.

Internationalizing your application can mean using locale-specific number and date/time settings, among other options. Many of those options are controlled in Windows by the user's environment settings. For the user interface, string resources can be built into the compiled application (usually into the EXE) to support swapping in different sets of text. These resources are usually hard-coded into the application file at compile time. If an application needs to be deployed in different languages, different application files need to be compiled and deployed.

	An elegant solution is to have a single application file with all the required "languages" built into it–with the ability to switch languages dynamically at runtime, based on some internal setting. In this article I'll describe how to support dynamic translation of an Access application.

Interface controls

My solution doesn't address numeric and date/time settings, since Windows can handle those. What I do address are the text strings in the various Access controls. This requires some understanding of those controls.

	In Access applications, there are basically four types of interface elements: forms, reports, controls hosted on forms or reports, and CommandBars. Forms, reports, and the controls that can be hosted on them are Access native controls (for this article, I'll treat ActiveX controls as native controls). The hosted objects can be further subdivided into two main categories: container and non-container controls. Container controls have the ability to "host" other controls, including other container controls.

	Container type controls include subforms, subreports, Tab controls, and Page controls. Subforms and subreports are actually forms and reports that are hosted on other forms/reports. Tab controls can only be hosted by a form or subform and can have only a single type of child control: a Page (Pages can contain any other type of control, including subforms, except for a Tab control).

	In Access, all controls on a form or a report are members of the Controls collection of the host form or report object, no matter how deeply nested the controls are. That is, regardless of whether a textbox sits directly on a form or on a Page of a Tab control, it still belongs to the Controls collection of the host form.

	One of the principles of object-oriented design is that any object used to design the user interface must have a property that uniquely identifies it during its lifetime. That's certainly the case with hosted controls that are uniquely identified by their Name properties.

	But when it comes to CommandBars (the objects behind all menus, popup menus, and toolbars in Office), things are a little different. While there are several types of controls that can be hosted on a CommandBar (CommandBarButton, CommandBarComboBox, and CommandBarPopup), a variable of CommandBarControl can be used to reference all of them.

	To make matters more interesting, the CommandBar and CommandBarPopup controls are also containers and have a Controls collection property. CommandBar is the root point of a menu, and it can host many CommandBarControls (including CommandBarPopup controls), which also can contain other CommandBarControls. That is, CommandBarControls can be nested to an unlimited number of levels. However, unlike the hosted controls, CommandBar controls belong only to the Controls collection of their direct parent control.

	In addition, those controls simply don't have a unique Name property. These controls can be accessed through the Controls collection of the CommandBar either by position or by using the control's Caption property. But the Caption property is exactly the property that will be changed during a translation, so I can't use that property to find a specific CommandBarControl. To get around this, I use the Tag property of the CommandBar items to identify them. Every child control on a menu has its own, unique Tag value, regardless of whether it's a direct child of the root CommandBar container, or belongs to the Controls collection of a CommandBarPopup control hosted on the root CommandBar. However, on two different CommandBars (toolbars), I can use the same Tag for two different CommandBarControls.

	For example, in the application in the accompanying Download file, I have two CommandBars named bzAddressMainMenu (used as the startup main menu) and bzAddressMainToolbar (used as a shortcut toolbar for common modules). On both bzAddressMainToolbar and bzAddressMainMenu I have a CommandBarButton with the caption "Addresses", which opens the Addresses form. Both of those CommandBarButtons have the same Tag value: Address. For information on how to set the Tag property on a custom toolbar, see the sidebar "Setting Custom Toolbar Properties."

Sidebar: Setting Custom Toolbar Properties

To set the Tag property of a CommandBar, first right-click on any CommandBar and select Customize from the popup menu. In the Customize dialog, on the Toolbars tab, scroll down the Toolbars list until you find the custom toolbar that you've created. Once you get to the toolbar, check its checkbox to make the toolbar visible.

	The next step is to right-click on the CommandBarControl whose Tag property you want to change and select Properties from the popup menu. This will open the dialog shown in Figure 1, which allows you to set the Tag property (the figure is from Access 97, but the Access 2000 and 2002 dialogs look similar).

Managing translation

With that review of the issues in dealing with controls out of the way, I can start discussing the translation. I use a table to store localized strings to be swapped into the controls that make up the user interface. The table is called tblTranslateInterface, and it has the structure shown in Table 1.

Table 1. Structure of the tblTranslateInterface table.

Field name	Type	Size
LangID	Text	2
RootControlType	Text	1
RootControlName	Text	50
ControlName	Text	100
Caption	Text	100
ControlTipText	Text	100
StatusBarText	Text	100

	Here's a description of the fields:

• LangID–The field that stores LanguageID for the record. LanguageID can be any string that makes sense to you, but I recommend using the ISO language codes. Examples include EN—English, DE—German, FR—French, and RO—Romanian. See http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt for more details.
• RootControlType–The type of the root container for the control to be translated: C—CommandBars, F—Forms, R—Reports.
• RootControlName–The name of the container control. For forms or reports, this is the object's Name property. For CommandBars, use the name of the toolbar as displayed by selecting View | Toolbars | Customize.
• ControlName–A string that uniquely identifies the control to be translated:
• For controls on forms or reports, this is the value of the Name property of the control to be translated. Leaving this field empty causes the translation to apply to the form or report (for example, the caption of a form or report).
• For CommandBarControls, this is the value of the Tag property.
• Caption–The text for the Caption property of the target control, in the language specified by the LangID field.
• ControlTipText–The text for the ControlTipText property for the target control in the language specified by the LangID field.
• StatusBarText–The text for the StatusBarText property for the target control in the language specified by the LangID field.

	A second table, called tblLangSettings, is also used by the translation process. It has two fields:

• LangID–The current LanguageID for the application.
• LangChangeHook–The name of a function or subroutine to handle translation tasks outside of the scope of the standard translation processing.

The translator class

In order to be able to implement my translation code with any application, I wanted to make my code as self-contained as possible. I put all my translation code in a class module called bzClsTranslateInterface. The class has two properties:

• LanguageID–The ID of the language to be used for the new text. It should match one of the LangID values from the tblTranslateInterface table.
• IgnoreErrors–If True, some errors are ignored during runtime.

	The class also has three methods:

• TranslateInterface–Calling this method does the translation for a form or report. The method accepts two parameters: toObject (the form or report object to have its text replaced) and lRecursive (which, when set to True, causes any subforms to also be translated).
• TranslateMenu–This method handles replacing text on a menu. It receives an optional parameter (toMenu, the name of a CommandBar object). If the parameter is empty or omitted, the class uses the application's default menu name, as found in the StartupMenuBar property of the current database.
• TranslateApplication–This method is called when the LanguageID is changed. It dynamically translates all open forms or reports. The method also calls the procedure set in the LangChangeHook field to execute any custom code.

	Both the TranslateInterface and TranslateMenu methods use the LanguageID property of the class to determine what language to translate into.

Triggering translation

Setting the LanguageID property triggers translation. However, I'm going to start with the Property Get procedure that's run when the LanguageID property is read. The Get property returns the current language setting. If no setting is available yet (the tblLangSettings table is empty or the LangID field is null or empty), the routine returns EN as the default, and also sets this as the default value (for clarity, I've stripped out the error handling code):

Property Get LanguageID() As String
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim cLangID As String

  If cLangID = "" Then
    cLangID = "EN"
  End If
  Set db = CurrentDb
  Set rs = db.OpenRecordset(bzLanguageTableName, _
    dbOpenTable)
  If rs.RecordCount = 0 Then  
    rs.AddNew
    rs!langID = cLangID
    rs.Update
  Else
    cLangID = nz(rs!langID, "EN")
  End If
  LanguageID = cLangID
End Property

	The Property Let procedure saves the LanguageID setting to the tblLangSettings table and also triggers dynamic translation of the whole application by calling the TranslateApplication method:

Property Let LanguageID(cLangID As String)
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim cLangChangeHook As String

  Set db = CurrentDb
  Set rs = db.OpenRecordset(bzLanguageTableName, _
    dbOpenTable)
  If rs.RecordCount = 0 Then  
    rs.AddNew
  Else
    rs.Edit
  End If
  If cLangID = "" Then
    cLangID = "EN"
  End If
  rs!langID = cLangID
  cLangChangeHook = rs!LangChangeHook
  TranslateApplication
End Property

	Just to complete the discussion of the class's properties, I'll look at the IgnoreErrors property. During development time, it's possible to add, delete, or rename controls on a form. If you've set up replacement text for those controls and forget to remove these entries from the translation table, a runtime error will be raised as my translation routine tries to update that control. With IgnoreErrors set to True, such errors are ignored. However, at development time, it's better to set this property to False to discover what controls have been removed or renamed and to keep your translation table clean.

Translating the application

The TranslateApplication method translates all open forms/reports and all custom CommandBars defined in the application. The method uses the built-in Forms collection to scan through all open forms and the CommandBars collection. After doing all of the standard processing, the routine calls the function named in the LangChangeHook field to perform any custom processing:

Public Sub TranslateApplication()
  On Error GoTo TranslateApplication_Err
  Dim cb as CommandBar
  Dim cLangChangeHook As String
  Dim oFrm As Form, oRpt As Report

  For Each oFrm In Forms
    TranslateInterface oFrm, true
  Next

  For Each cb In Application.CommandBars
    If Not cb.BuiltIn Then
      TranslateMenu cb.Name
    End If
  Next  

  cLangChangeHook = Nz(DLookup("LangChangeHook", _
    bzLanguageTableName), "")
  If cLangChangeHook <> "" Then
    Application.Run cLangChangeHook
  End If

TranslateApplication_Exit:
  Exit Sub

TranslateApplication_Err:
  Select Case Err
    Case 2517
      MsgBox "Hook procedure or function [" & _
        cLangChangeHook & "] used by " & _
        "TranslateApplication method is missing!", _
        vbInformation
      Resume TranslateApplication_Exit
'… the rest of error handler routine
end sub

	Unfortunately, reports already open in Preview mode can't be dynamically translated because the report's preview window is actually a Windows metafile image generated from the report object definition. So a report can be translated only by closing and opening it again in Preview mode.

Translating an object

The work of updating a single form or report is handled by the TranslateInterface method. I create this object and store a reference to it in a public variable with this code:

Public oTranslate As New clsBzTranslateInterface

	In addition to calling this method from the TranslationApplication routine, I also call it from the Open or Load event of forms or reports. Effectively, this means that each form or report is translated as it's opened, catching any forms or reports that weren't open at the time that TranslateApplication was called. Here's the code:

Private sub Form_open(Cancel as Boolean)
  oTranslate.TranslateInterface me
end sub

	The code first checks whether the object passed as a parameter is a form or report. Then, the code retrieves all of the translation settings for the object in order to replace the Caption, ControlTipText, and StatusBarText with the translated text:

Public Sub TranslateInterface(_
  oParent As Object, _
  optional byval lRecursive as Boolean = true)

  On Error Resume Next
  nTmp = oParent.HasData
  nTmp = Err.Number
  On Error GoTo TranslateInterface_Err
  cObjType = IIf(nTmp = 2465, "F", "R")
    
  Set db = CurrentDb
  Set rs = db.OpenRecordset("select * from " & _
    bzTranslationTableName & " where LangID='" & _
    Me.LanguageID & "' and RootControlType='" & _
    cObjType & "' and RootControlName='" & _
    oParent.Name & "'")
  If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do While Not rs.EOF
      If Nz(rs!ControlName, "") = "" Then
        oParent.Caption = rs!Caption
      Else
        On Error Resume Next
        Set oCtrl = oParent.Controls(rs!ControlName)
        oCtrl.Caption = rs!Caption
        oCtrl.ControlTipText = rs!ControlTipText
        oCtrl.StatusBarText = rs!StatusBarText
        On Error GoTo TranslateInterface_Err
      End If
      rs.MoveNext
    Loop
  End If

	Once all of the form properties have been changed, if the lRecursive parameter has been set, the routine processes any subforms. Subforms are identified by checking to see whether the control has a Form property:

  If lRecursive Then
    For Each oCtrl In oParent.Controls
      On Error Resume Next
      Set oFrm = oCtrl.Form
      If Err.Number = 0 Then
        TranslateInterface oFrm
      End If
    Next
  End If
  Exit Sub

End Sub

	Why bother with a parameter to control subform translation? The TranslateApplication method finds all open forms by inspecting the Forms collection, calling the TranslateInterface method on every open form. But subforms aren't members of the Forms collection, which includes only open forms. On the other hand, when a form has subforms, the Open and Load events for each subform will be triggered when the main form is opened. But inserting the TranslateInterface method into all of your application's subforms can be time-consuming. To solve this problem, setting the TranslateInterface method's second parameter finds all subforms of a form. The AddressBrowse form that's available in the Download file shows this option in action.

Translating menus

This method supports translating a custom menu or toolbar created in the application. The method receives a single optional parameter, which is the name of a custom CommandBar. If the parameter is missing, the function tries to find the name of the default menu bar for the application using my routine GetMainMenuName. If GetMainMenuName can't find a default menu (for example, because there's no default startup menu bar), the method just exits:

Public Sub TranslateMenu _
  (Optional ByVal cMenuName As String = "")

  If cMenuName = "" Then
    cMenuName = GetMainMenuName()
  End If
  If cMenuName = "" Then
    Exit Sub
  End If
    
  Set db = CurrentDb
  Set rs = db.OpenRecordset("select * from " & _
    bzTranslationTableName & " where LangID='" & _
    Me.LanguageID & "' and RootControlType='C'" & _
    " and RootControlName='" & cMenuName & "'")
    
  If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do While Not rs.EOF
      SetMenuCaptionFromTag cMenuName, _
        rs!ControlName, rs!Caption
      rs.MoveNext
    Loop
  End If
End Sub

	GetMainMenuName finds the default startup menu by checking the StartupMenuBar property (this property is created when an application has a default startup menu set). The value of this property is read using the Properties collection of the current database:

Private Function GetMainMenuName() As String
  On Error GoTo GetMainMenuName_Err
  GetMainMenuName = CurrentDb.Properties(_
    "StartupMenuBar")

GetMainMenuName_Exit:
  On Error Resume Next
  Exit Function

GetMainMenuName_Err:
  Select Case Err
    Case Err.Number = 3270
      GetMainMenuName = ""
      Resume GetMainMenuName_Exit
    Case Else
      ' the usual error handling code
  End Select
End Function

	This method tries to read the StartupMenuBar property. It fails if the property is missing, generating error 3270 ("Application-defined or object-defined error"). In the error handler routine, I assume that the property is missing and return an empty string as the startup menu name.

	If the TranslateMenu method receives a menu name as a parameter, or finds the name of the default startup menu, it retrieves all of the translation records related to this menu. The routine then calls the SetMenuCaptionFromTag method for every child object of CommandBar. Here's the SetMenuCaptionFromTag method, which actually does the translation:

Private Function SetMenuCaptionFromTag(_
  ByVal cParentMenu As String, _
  ByVal cTag As String, _
  ByVal cNewCaption As String) As Boolean

  Dim cb1 As CommandBar, cb2 As Object
  Set cb1 = CommandBars(cParentMenu)
        
  Set cb2 = cb1.FindControl(Tag:=cTag, _
             Recursive:=True)
    
  cb2.Caption = cNewCaption
  SetMenuCaptionFromTag = True

SetMenuCaptionFromTag_Exit:
  Exit Function

SetMenuCaptionFromTag_Err:
  '..error handling code
  SetMenuCaptionFromTag = False
  Resume SetMenuCaptionFromTag_Exit
End Function

	The function uses the CommandBar name to get a reference to the CommandBar object. The routine then uses the CommandBar's FindControl method to find the control to translate, searching on the Tag property. Things get complicated here because a CommandBar can also contain CommandBarPopup controls, which are also container controls, so the control to be translated can be a child of a CommandBarPopup control and not a direct child of the CommandBar object.

	To handle this, FindControl's Recursive parameter is set so that a recursive search through all levels is performed to find the desired control. If the FindControl routine succeeds, it returns a reference to the CommandBarControl. Once the CommandBarControl is found, the function replaces the Caption property of the control.

The demo application

I developed the sample application in Access 97 to ensure that the code only uses features that are available in all current versions of Access. To make the code portable across all of the various versions of Access, I used DAO for data access. To use my sample code in versions of Access after Access 97, you'll have to add a reference to either DAO 3.51 or DAO 3.6. However, I've included versions of the demo application in both Access 97 and Access 2000.

	The demo application is a simple contact management program. It has a form that allows users to browse contacts (see Figure 2). That form has buttons to open multiple contact detail forms and an Addresses details form (see Figure 3).

	 There are also three reports to demonstrate translations in reports and the two custom CommandBars I mentioned earlier. The sample application also includes an Options form to change the default language for an application (see Figure 4).

	Custom processing
With all forms open, you can open the Options form, change the default language setting, and click OK or Apply. All opened forms will be automatically translated to the new language and the tblLangSettings table updated with the new language setting. As subsequent forms are opened, they'll pick up the new language setting and replace the text on the form.

	When the application is first opened, an Autoexec macro starts and calls the application's Startup function, which includes this code:

oTranslate.IgnoreErrors = True
oTranslate.LanguageID = oTranslate.LanguageID

	The second line is the important one. Setting the LanguageID property triggers translation processing, including calling the custom processing routine named in tblLangSettings. I set the LanguageID property primarily to call that routine (I set the LanguageID to the current language setting so that I don't re-translate anything).

	When might you use a custom routine? In the sample Options form, you can select the new default language for the application and have your application automatically translated. If you look at the combo box on the Options form, you can see that the entries in the combo box are translated also. I handled this through the custom code routine, which provides an example of how to extend my class module.

	My custom routine depends on a global variable. It holds a language-specific RowSource for the combo box:

Public cLangSettingsSource as string

	In my TranslateHook procedure, I assign the proper translated string to cLangSettingsSource, using my LanguageID property to pick the right language. The next logical step is to assign the new RowSource to the combo box. So why use the global variable? When the application starts, no forms are open, including the Options form. So I store the RowSource in a global variable to have it available when the user finally does open the Options form. The last statement in this sample procedure is useful when I change the language setting from within the Options form, so that I can immediately translate values:

Public Sub TranslateHook()
        
  Select Case oTranslate.LanguageID
    Case "EN"
      cLangSettingSource = _
        "'English';'EN';" & _ 
        "'German';'DE';" & _
        "'Romanian';'RO';" & _
        "'French';'FR';" & _
        "'Dutch';'NL'"
    Case "DE"
      cLangSettingSource = _   
        "'Englisch';'EN';" & _   
        "'Deutsch';'DE';" & _ 
        "'Rumänisch';'RO';" & _ 
        "'Französisch';'FR';" & _
        "'Holländisch';'NL'"
    Case "FR"
        '…etc, etc
  End Select
  If FIsLoaded("Options") Then
    Forms!Options.cmbLanguage.RowSource = _
      cLangSettingSource
  End If
End Sub

	To complete the process, I need some piece of code in the Options form's Open event to update the combo box when the Options form is finally opened:

Private Sub Form_Open(Cancel As Integer)
    If cLangSettingSource <> "" Then
        Me.cmbLanguage.RowSource = cLangSettingSource
    End If
End Sub

	This example is simplistic. In real life, there might be many forms with several combo boxes or list boxes that need to be translated. Instead of using global variables, you can set up a table to store various translated strings that are needed in different places of the application. The routine can perform any other processing required for multi-language applications, including changing masks for controls.

Other issues

So far, I've showed you how to make the user interface of your application truly "multi-language" aware. However, there are a few more issues you must be aware of in order to successfully implement a full-featured, multi-language interface.

Reports

As I noted before, reports are translated automatically when open but can't be dynamically re-translated while they're open in Preview mode. However, this isn't really an issue since reports are usually opened in Preview mode and printed or closed immediately when the user sees the data.

Strings used in MsgBox and other places in the code

To handle other strings in your application, I'd recommend creating a table with three fields: LangID, StringID, and StringValue. The next step would be to create a new method in the clsBzTranslation class named TranslateString, which receive a parameter StringID (number) and returns the corresponding StringValue for the current application's LangID. A call to MsgBox looks like this:

Global const STRID_MSGTST = 1
dim nRetVal as integer
nRetVal = MsgeBox(;
  oTranslate.TranslateString(STRID_MSGTST), vbYesNo)

Forms displayed in Continuous view or Datasheet view

To display a form in Continuous view, the form usually takes the appearance of a table (or grid). In these kinds of forms, developers add labels for columns to the Header section of the form–and then delete the default labels added by Access at design time. To be able to open a form in Datasheet view and make the form translation-aware, it's important to keep labels added automatically by Access when you place textbox controls on the form. Access uses the Caption from these automatically added labels, which are linked to the corresponding textboxes, as the caption for columns when the data is shown in Datasheet view. Without those columns, Access will use the field name from the underlying table as the column's caption–something that you can't translate.

Switchboard

Many Access applications use a switchboard as the main entry point for the application. But labels from the switchboard are set up dynamically at runtime, so translating them when the Switchboard form is displayed has no result. Translating the switchboard is another situation where you can use the custom code procedure. You can create a table with the same structure as the Switchboard Items table, named tblLangSwitchboardItems but with one more field–LangID. The next step would be to add to this table all of the records from the original Switchboard Items table plus a duplicate set for every language to be supported. The custom procedure would delete all records from the Switchboard Items table and then insert all records for the language currently selected. When the Switchboard form opens, the new language-specific text will be displayed.

	One last tip about multi-language interfaces: It's well known that the same word has different lengths in different languages. Because of this, for a multi-language interface it's necessary to reserve enough space on a form for the longest possible string. The English language is more analytical than other languages; the same message in another language usually requires 120-180 percent more space. You should be aware of this in order to design a good-looking user interface.

Download 407ZAMFIR.ZIP

To find out more about Smart Access and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the July 2004 issue of Smart Access. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.