Make Your Application Interface Multilingual

by Patrick L. Steiner

With the growth of the global marketplace, your application interfaces may require a facelift. Specifically, they may need to support multiple languages.

In this article, we'll show you a technique that will allow your Access forms to do this. And the best thing about this solution is that it's completely contained within Access-no OLE servers or resource files are required, as in other approaches.

Form basics

To get started with our technique, we need an Access form. Figure A shows a simple form with the basic objects that we'll be customizing for a multilanguage interface. (You can also download our sample files from www.zdjournals.com/adj; click on the Source Code hyperlink.)

First, we'll customize the form's caption (or title bar) with a language-specific description (e.g., "Customer Information"). The form property we'll be affecting is called Caption.

Next, each text box will get a language-specific control tip and status-bar message. The text-box properties we'll be working with are ControlTipText and StatusBarText.

For each text box, there's a label. The label's Caption property will hold a language-specific description.

Finally, we'll give each command button a language-specific control tip and status-bar message. The command-button properties we'll be affecting are called ControlTipText and StatusBarText.

Tables required

In addition to whatever tables your application needs, you'll have to create several tables since the customization technique is completely self-contained within Access. Tables A through F show their structure definition and names (all of the table names begin with "z" so that they're grouped together in your database window). Note the use of multiple-field primary keys. For example, in table ztblControlTipText (Table A), the Primary Key is comprised of fields FormName, ControlName, and LanguageID.

Table A: Structure of ztblControlTipText

Table B: Structure of ztblFormCaption

Table C: Structure of ztblLabelCaption

Table D: Structure of ztblLanguage

Table E: Structure of ztblStatusBarText

Table F: Structure of ztblSystemDefault

You'll notice that all these tables are similar in structure. If you're creating these from scratch, you can eliminate a lot of work by using table cut-and-paste operations (then modifying the table design as required).

You might be wondering why we didn't consolidate some of these tables into one table, since the structures are basically the same. The answer is simply a bookkeeping issue. If your database application has lots of forms that need multilanguage support, keeping all of the controls' language text in one table would become unwieldy. The way our tables are named and designed, you can quickly locate your control text descriptions, as we'll see later.

Naming conventions

Your application will have tables and forms (in addition to the ones we've created). As you create and design your forms, you'll want to use a standard naming convention for the controls you place on them. Table G shows the conventions we use in this article.

Table G: Form-control naming conventions

For example, if you have a text box called CustomerName, make the control name txtCustomerName. Similarly, its associated label should be lblCustomerName. A standard naming convention is good design practice and allows other designers to easily understand your application (and it will simplify the bookkeeping required for this application).

Entering "z" data

Now that we have all the parts in place, let's enter some data into ztblLanguage and ztblSystemDefault. Then, you'll quickly see the purpose of these two tables. Figure B and Figure C show the example data you should enter.

As you can see, ztblLanguage contains an entry for each language you plan to support. We'll enter the LanguageID value in the other supporting "z" tables later, so keep this table and its values handy. The value shown in Figure C is "2," which equates to Spanish, as Figure B shows.

As for ztblSystemDefault, it should only contain one record. You can add more fields (such as a default tax rate, percent markup, or other system defaults), but not more records.

Entries for the other "z" tables

For each form or control element that requires a multilanguage interface, you'll make entries into the other "z" tables that we defined earlier. For the form caption (title bar), make an entry into ztblFormCaption for every language you plan to support. (See Figure D).

For text-box and command-button control tips, make an entry into ztblControlTipText for each language, as Figure E shows. Similarly, for text-box and command-button status bar messages, make an entry into ztblStatusBarText for each language (see Figure F). Finally, for label captions, make appropriate entries in ztblLabelCaption, as Figure G shows.

As you might have noticed in the "z" table data, there are two forms in our complete database: frmCustName and frmCustomer. The frmCustName form only displays a list of customer names and therefore doesn't have as many controls as frmCustomer, which shows all information related to a customer.

Thus, there aren't as many entries in the "z" tables for frmCustName, although we've provided support for all five languages with it. The frmCustomer form only has entries for two languages-English and Spanish-but the other languages could easily be added.

The VBA function

To tie everything together, you'll need to create a VBA function named AssignFormLabels and store it in a module called modAssignLanguageObjects. The code for this function is shown in Listing A. To help explain the code, we've broken it into 10 sections.

First, note that we're passing the form name that's calling the function through an input argument of the function (sFormName). Also, the function itself will return a integer value of 0 (if failed) or -1 (if successful), which isn't critical to its operation but is a good programming practice.

Beginning in section 1 of Listing A, we simply define or declare the variables (and their data types) that the function will use. Section 2 assigns values to some local variables that are used for message boxes (in case of errors) and initializes our function value to 0 (failed status). The function value will be set to -1 (success) if it completes normally, as we'll see in section 10. Next, section 3 defines the function label for an error handler-again, just good programming practice.

Section 4 checks ztblSystemDefault to find the language (LanguageID) that we've selected for the application. Note that if no entry is found in the table, English (iLanguageID = 1 in this case) is assumed.

Not quite as simple (but still short) is section 5. We know the "string" name of the form that's calling this function because it's passed in as an argument (sFormName). But we need to assign a "form" variable (F) that's equivalent to the "string" name so we can update the form's labels, captions, etc.

We can't use the Screen.ActiveForm object because when this function is called (as we'll see later), there's no active form as far as Access can determine. Thus, we simply use the Access Forms container. One of its properties is Count, which indicates how many open forms are in the current database. We assign iForms equal to Forms.Count. The Forms container has an item (or form) for each open form, and each item has a Name property. We simply loop through all possible forms until the Forms.Item(iLoop).Name equals sFormName. When we find the match, we set our form variable (F) equal to Forms.Item(iLoop).

In section 6, we know our form name (sFormName) and our language (iLanguageID). We open ztblFormCaption and set the index to PrimaryKey. For this table, the key is sFormName and iLanguageID. We do a table seek looking for a match (Not TB.NoMatch). If we find a match, we set the form's caption (F.Caption) equal to the description found in the table (TB![Description]). Obviously, if no match is found, the form's caption won't get set by this function, so we'll use whatever value is stored in the form's design.

Code sections 7, 8, and 9 work much like section 6. However, the "z" table names are different, the PrimaryKey is different, and the label or text control being set is different.

Another note worth pointing out is that a form has only one "caption" property. But since a form can have any number of controls on it, we determine the number of controls on a form by its Controls.Count property. Then, we loop through every possible control and see if it has an entry in the "z" table we're working with.

If we get to code section 10 (which we should!), we're basically finished. We set the function value equal to -1 (success) and exit the function.

Following code section 10 is our error-trap label and code. We hope to never get here, but we could if tables are missing or a bad form name is passed into the function. In this case, we could expand the error handler to more gracefully handle these conditions.

Listing A: Function AssignFormLabels

Function AssignFormLabels(sFormName As String) As Integer

'------------------------------------------------

' Purpose:  Read all label entries in <ztbl...>'s

'   for the active form and the default

'   language chose in <ztblSystemDefault>.

'------------------------------------------------

' By:       Pat Steiner

' Date:     1/14/98

' Version:  1.0

'------------------------------------------------

'------------------------------------------------

' 1. Declare variables and types

'------------------------------------------------

Dim DB As Database

Dim TB As Recordset

Dim F As Form

Dim sMessage As String

Dim sMsgTitle As String

Dim sFunction As String

Dim sSQL As String

Dim sControlName As String

Dim iLanguageID As Integer

Dim iControls As Integer

Dim iForms As Integer

Dim iLoop As Integer

'------------------------------------------------

' 2. Assign values to local variables ...

'------------------------------------------------

sFunction = "AssignFormLabels"

sMsgTitle = "FN: " & sFunction & " ( )"

AssignFormLabels = 0

'------------------------------------------------

' 3. Define error handler ...


'------------------------------------------------

On Error GoTo Err_AssignFormLabels

'------------------------------------------------

' 4. Get default language entry ...

'------------------------------------------------

Set DB = CurrentDb()

Set TB = DB.OpenRecordset("ztblSystemDefault", dbOpenTable)

If TB.EOF Then

  Beep

  sMessage = "Error: No entries in table <ztblSystemDefault> ... English language will be used!"

  MsgBox sMessage, vbExclamation, sMsgTitle

  iLanguageID = 1

Else

  TB.MoveLast

  iLanguageID = TB![LanguageID]

End If

TB.Close

'------------------------------------------------

' 5. Determine and set form being opened ...

'------------------------------------------------

iForms = Forms.Count

For iLoop = 0 To (iForms - 1)

  If Forms.Item(iLoop).Name = sFormName Then

    Set F = Forms.Item(iLoop)

    Exit For

  End If

Next iLoop

'------------------------------------------------

' 6. Set form's .Caption (title) property

'------------------------------------------------

Set TB = DB.OpenRecordset("ztblFormCaption", dbOpenTable)

TB.Index = "PrimaryKey"

TB.Seek "=", sFormName, iLanguageID

If Not TB.NoMatch Then

  F.Caption = TB![Description]

End If

TB.Close

'------------------------------------------------

' 7. Set form labels' .Caption property

'------------------------------------------------

Set TB = DB.OpenRecordset("ztblLabelCaption", dbOpenTable)

TB.Index = "PrimaryKey"

iLoop = 0

iControls = F.Controls.Count

For iLoop = 0 To (iControls - 1)

  sControlName = F.Form(iLoop).Name

  TB.Seek "=", sFormName, sControlName, iLanguageID

  If Not TB.NoMatch Then

    F.Form(iLoop).Caption = TB![Description]

  End If

Next iLoop

TB.Close

'------------------------------------------------

' 8. Set form text boxes' .StatusBarText property

'------------------------------------------------

Set TB = DB.OpenRecordset("ztblStatusBarText", dbOpenTable)

TB.Index = "PrimaryKey"

iLoop = 0

iControls = F.Controls.Count

For iLoop = 0 To (iControls - 1)

  sControlName = F.Form(iLoop).Name

  TB.Seek "=", sFormName, sControlName, iLanguageID

  If Not TB.NoMatch Then

    F.Form(iLoop).StatusBarText = TB![Description]

  End If

Next iLoop

TB.Close

'------------------------------------------------

' 9. Set form text boxes' .ControlTipText property

'------------------------------------------------

Set TB = DB.OpenRecordset("ztblControlTipText", dbOpenTable)

TB.Index = "PrimaryKey"

iLoop = 0

iControls = F.Controls.Count

For iLoop = 0 To (iControls - 1)

  sControlName = F.Form(iLoop).Name

  TB.Seek "=", sFormName, sControlName, iLanguageID

  If Not TB.NoMatch Then

    F.Form(iLoop).ControlTipText = TB![Description]

  End If

Next iLoop

TB.Close

'------------------------------------------------

' 10. Set successful function value (-1)

'------------------------------------------------

AssignFormLabels = -1     'Successful function value

Exit Function

'====================

Err_AssignFormLabels:

'====================

Beep

sMessage = "Error #" & Err & ": " & Error(Err)

MsgBox sMessage, vbCritical, sMsgTitle

Exit Function

End Function

Testing the technique

Our application database needs a table and some forms to test the multilanguage interface. Table H shows the structure of tblCustomer, which will be the basis for our test forms.

Table H: Structure of tblCustomer

Figure H shows our test form in design mode. Its record source is table tblCustomer. You can quickly generate this form by using Access's built-in AutoForm menu function, or you can build it from scratch. Either way, make sure you rename the controls (text boxes, labels, and command buttons) according to the naming conventions we outlined earlier. For example, the text box ZipCode's Name property should be txtZipCode (although the ControlSource property would still be ZipCode).

While in design mode, you must tell the form to call our VBA function AssignFormLabels. To do this, place the following line on the form's Activate event:

=AssignFormLabels("frmCustomer")

Note that we're passing the form name (frmCustomer) as a string to our function.

After you've completed and saved frmCustomer, set the value in table ztblSystemDefault (for LanguageID) equal to 1 (English), then open frmCustomer in form view. It should look like Figure I.

Figure I: Our form looks like this when English is the chosen language…

Now, close frmCustomer and set the value in table ztblSystemDefault (for LanguageID) equal to 2 (Spanish). When you reopen frmCustomer in form view, it should look like Figure J. Note that the form caption, text-box labels, control tips, and status-bar messages are now in Spanish! Magnífico!

Icing on the cake

There's one small problem with our technique at this point: The user can't easily switch languages. Imagine for example, if you were an English speaker and discovered that that the database was in Spanish when you opened it. You wouldn't know what to do.

Fortunately, we can easily fix that problem. Just add an AutoExec macro that opens an introductory menu (frmAMainMenu) like the one shown in Figure K. To do this, create a two-line macro. On the first line, make the action OpenForm and choose frmAMainMenu from the Form Name dropdown list. On the second line, make the action StopAllMacros. Save the macro as AutoExec.

The main menu form's record source is tied to table ztblSystemDefault, and the values in the combo box come from ztblLanguage. (The form's caption or title bar says "Language" in the five different languages we've set up).

Conclusion

The multilanguage interface solution shown here can be expanded to more languages without additional programming. Simply add more languages in ztblLanguage and make the necessary entries into the other "z" tables.

The technique we demonstrated can be expanded to include reports (similar to forms). You'd basically cut and paste our VBA function (AssignFormLabels) and create a new function called AssignReportLabels. In the new function, change all references to the Forms container to use the Reports container (Reports), then set the report's OnActivate event as:

=AssignReportLabels("rptname")

Our example database on the Web site also includes another form (frmCustName) that has fewer controls on it but already has data in the "z" tables to support all five languages mentioned in this article.

Good luck, buena suerte, bon chance, viel Glück, and buono felcità! I apologize for leaving out other languages, but I have dictionaries for only these five!

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.