Access Cookbook - Ken Getz [96]
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:
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).
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.
Add the necessary rows to tblMessages, filling in each column with the translated text, as shown in Figure 4-13.
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.)
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.
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,