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

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.

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

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.

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 Comman Bar 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 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).

Figure 2

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

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 de ault 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';" & _ 
            "'Rumnisch';'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.

Global const STRID_MSGTST = 1

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

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.