Access Cookbook - Ken Getz [97]
' 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