jwcolby
jwcolby at colbyconsulting.com
Sun Feb 22 20:43:33 CST 2009
An AccessD member, Rocky Smolin, has an application that he translates into different languages.
This next set of lectures will create a class system to perform that translation. There are many
different ways to do this, and a class system is certainly not required, but using classes has
advantages that other methods do not.
One of the advantages is that while Rocky’s current requirements do not require anything other than
a simple translation of labels and command buttons, it is entirely possible that the next person
desiring to perform such a translation might wish to use things like the control tip text, status
bar text, and even the form’s caption. Once we have the basic class set up, adding additional
property translations can be added as required. Yes there will be refactoring but we go to the same
classes we have already created to add the code.
One other “advantage”, and I quote that because there was intense debate about whether this was a
good thing, is that using this system we cache the translation strings the first time the form opens
and never have to go to the disk again as that form opens and closes. I like to cache such data,
others don’t. This code will cache the data but if you don’t want it cached, then don’t save the
form class in the clsXlateSupervisor’s collection. Voila, no caching.
By the way, Rocky graciously donated the translation tables, and the forms to be translated to this
lecture. All of this structure already existed, all I did was write the classes to use the table to
translate the forms.
At any rate, the following is my solution to this problem, using classes.
The overall strategy will be to design a class clsXlateFrm to hold all of the translation phrases
for a given form. This class will also hold all of the code to load the translation phrases into a
collection for caching, and code to load the caption property of controls on the form from that
collection of phrases.
Above this class will be another class clsXlateSupervisor that is a “supervisor” class, it
supervises the process of translating forms. That class will have a collection to hold instances of
clsXlateForm. As a form loads the first time clsXlateSupervisor will load an instance of
clxXlateFrm, and call a method mInit() of that class to cause the form to be translated. Once it
has a loaded functioning instance of clsXlateFrm for the form of the moment, it will store a pointer
to that class instance into a collection for the next time.
From that moment on, the data for the form just loaded will already exist and the second and
subsequent times the form loads, the existing class instance will be used to translate the form.
Just a note, as Rocky designed the system the only things that are translated are the captions of
labels and command buttons. There are however additional properties of controls that could
conceivably be used and thus need translation, specifically the status bar text and the control tip
text. If these properties required translation as well I would in fact create a third class
clsXlateCtl, where each control that ended up being translated gets this class. ClsXlateCtl would
then have three properties that correspond to these control properties – Caption, ControlTipText and
StatusBarText. Instead of simply placing the caption translation string into a collection keyed on
control name, I would create clsXlateCtl instances for precisely the controls needing translation,
and then store those class instances into the collection.
In fact we have previously designed a form class clsFrm and a couple of control classes. We could
simply place this code into those classes and suddenly we have translation capability built in to
any form. The beauty of classes and frameworks.
However for the system as Rocky designed it, that simply adds unnecessary complexity and so I do not
do that. I just added that note for those who might be thinking “what about the control tip?”.
clsXlateFrm:
• In the demo database, click Insert / Class.
• Immediately save the class as clsXlateFrm.
• Type the following in the header of the class:
'---------------------------------------------------------------------------------------
'This class loads all of the phrase strings into a collection for a single form
'the first time the form loads, keyed on control name. It ignores control
'names not in the language table.
'
'It then uses those strings in the collection to translate the form.
'The second and subsequent times the form loads, the class already has the
'strings in the collection so the collection load does not happen again (cached)
'and the translation happens from the collection.
'
'One of the tenants of class programming is to place the code in the class that needs the code.
'In this case that means opening the translation recordset here in this class, since this class
'is where those phrases are loaded into the collection for a given form.
'
'---------------------------------------------------------------------------------------
Const cstrModule As String = "clsXlateFrm"
Option Compare Database
Option Explicit
Private mcolPhrase As Collection 'A collection of translated phrases keyed on the control name
Private mstrName As String 'The name of the form that is being translated
Private mstrLanguageFldName As String 'The name of the field in the translation table that holds
the translation phrases
Private mlngCacheSize As Long 'The size of the cache required (simple length of all the
strings)
The above code simply declares variables for a collection to hold the translated strings, the name
of the form being translated, the name of the field in the translation table that holds the phrases
for the selected language, and a place to accumulate the total length of the translation phrases for
this form.
• In the editor’s left combo select the class. The Initialize event stub will be created.
• In the Initialize event stub place the following code:
Private Sub Class_Initialize()
Set mcolPhrase = New Collection
End Sub
• In the editor’s right combo select Terminate and the Terminate event stub will be created.
• In the terminate event, place the following code:
Private Sub Class_Terminate()
Set mcolPhrase = Nothing
End Sub
• Create the Minit() method as shown below
'---------------------------------------------------------------------------------------
'Called the first time the form loads. Loads the translation phrases from the table
' and translates the form.
'---------------------------------------------------------------------------------------
'
Function mInit(lfrm As Form, lstrLanguageFldName As String)
mstrName = lfrm.Name 'Store the name of the form
mstrLanguageFldName = lstrLanguageFldName 'And the name of the translation field
'
'If nothing in the collection then load the collection
'
If Not mcolPhrase.Count Then
mLoadColPhrase 'Load the phrases into the collection
End If
'
'Now translate the form
'
mXlateFrm lfrm 'Translate the form
End Function
The code is documented. It stores the name of the form, and the name of the language field in the
language table. It then simply calls two functions. The first function loads the translation
phrases into a collection. The second function then uses that loaded collection to load the
properties of controls on the form.
• Add the following code to create properties to expose the private variables in the class header.
The variables exposed include the name of the form, the size of the cache, and the phrase collection.
'
'Return the size of the cache - the total length of all the strings
Property Get pCacheSize() As Long
pCacheSize = mlngCacheSize
End Property
'
'Return the name of the form
Property Get pName() As String
pName = mstrName
End Property
'
'Return the collection that holds the phrases
Function colPhrase() As Collection
Set colPhrase = mcolPhrase
End Function
• Below the properties, add the following code:
'---------------------------------------------------------------------------------------
'This function translates the form. By the time this function is called the
'collection already contains all of the translation strings keyed by the
'control name. All it has to do is attempt to index into the collection
'using each control name. If there is something in the collection for that
'control name then the string is returned from the collection and placed in
'the control property.
'
'if nothing there, the error is ignored and the next control is tried.
'---------------------------------------------------------------------------------------
'
Function mXlateFrm(frm As Form)
Dim ctl As Control
On Error GoTo Err_mXlateFrm
'
'Check every control on the form
'Expand this case to add new control types.
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acLabel
'
'Try to get a translation phrase from the collection
'Any errors will be ignored
ctl.Caption = mcolPhrase(ctl.Name)
Case acCommandButton
ctl.Caption = mcolPhrase(ctl.Name)
Case Else
End Select
Next ctl
Exit_mXlateFrm:
On Error Resume Next
Exit Function
Err_mXlateFrm:
Select Case Err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case 5 'Control name not in translation table, ignore the error
Resume Next
Case Else '.All other errors will trap
Beep
MsgBox Err.Number & ":" & Err.Description
Resume Exit_mXlateFrm
End Select
Resume 0 '.FOR TROUBLESHOOTING
End Function
This code is responsible for loading the translation phrases out of the translation table. The
pointer to the form is passed in so that this method can manipulate the form’s control collection.
The function simply dimensions a control variable, then uses that to step through the form’s control
collection, processing each control.
As each control is retrieved, the name of the control is used to index into mColPhrase, the
collection of stored phrases. If the collection does not contain an entry for the current control’s
name the code errors ahd drops into the error case at the bottom fo the class. Case 5 is executed
and control simply resumes on the next line of the function. Essentially the case is exited when
the phrase is not found, and the next control is pulled from the controls collection. The code
continues until every control is checked.
'---------------------------------------------------------------------------------------
' Procedure : mLoadColPhrase
' Author : jwcolby
' Date : 2/21/2009
' Purpose : Loads the translation phrase strings out of the table for one form
' Builds a query dynamically based on the form name and the translation
' language field name
'---------------------------------------------------------------------------------------
'
Private Function mLoadColPhrase()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCtlName As String
Dim strPhrase As String
On Error GoTo Err_mLoadColPhrase
'
'Dynamically build the query to pull the translation strings for a specific form and language
'This query will pull a recordset of all the control names and the translation phrases for
those controls
'
strSQL = "SELECT fldLanguageControl, " & mstrLanguageFldName & " " & _
"FROM [usystblLanguage-Controls] " & _
"WHERE ((([usystblLanguage-Controls].fldLanguageForm)='" & mstrName & "'));"
'debug.print strsql 'To see the actual query SQL, uncomment this line and look in the
immediate window
'
'Open the recordset
'
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
With rst
While Not .EOF
'
'Drop all phrases found into the phrase collection
'Keyed on the control name fldLanguageControl
'
On Error Resume Next
strCtlName = .Fields(0).Value 'Get the control name
If Not Err Then 'This error handles null values that might be in the control name field
strPhrase = .Fields(1).Value
If Not Err Then 'This error handles null values that might be in the phrase field
mcolPhrase.Add strPhrase, strCtlName 'Now place the phrase in the control,
keyed on the control name
mlngCacheSize = mlngCacheSize + Len(strPhrase) 'Add up the size of the
translation strings
End If
End If
.MoveNext
Wend
End With
Exit_mLoadColPhrase:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Err_mLoadColPhrase:
Select Case Err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case Else '.All other errors will trap
Beep
MsgBox Err.Number & ":" & Err.Description
Resume Exit_mLoadColPhrase
End Select
Resume 0 '.FOR TROUBLESHOOTING
End Function
This code is responsible for loading the phrases out of a recordset into a collection for all future
processing. Translation cannot occur until this code runs, and once it runs the recordset never
needs to be opened again since the translation data is now cached.
This code builds up a dynamic SQL string which pulls two fields out of the control table. It pulls
the name of the control in the first field, and the translation phrase for a specific language into
the second field. It does so filtered by the form name so that it only pulls translation records
for one specific form.
The dynamic code is a little complicated by the fact that the source table is denormalized, with an
additional language field being added to the table as new languages are required. Since the name of
the language field is not fixed, this requires getting creative and having the supervisor class give
us the name of the translation field in the table as well as the form name. Once we have these two
pieces of information, we can build a query to pull exactly the correct language field, with the
recordset filtered by the form name.
Once the data is sitting in the recordset, the recordset is iterated and the control name and
translation string are pulled into string variables. There is no requirement to pull these pieces
into a string, and in fact it slows the code down a very small amount, but it makes the code easier
to read. By creating string to hold the data, you can see that:
strCtlName = .Fields(0).Value 'Get the control name
strPhrase = .Fields(1).Value
Then you can read that the data is added to the collection:
mcolPhrase.Add strPhrase, strCtlName
It is simply much more readable. If doing this significantly impacted performance, and particularly
if this wasn’t a tutorial on “how to”, I probably would not perform this extra step.
That is pretty much all there is. The recordset steps through all of the translation phrase
records, storing the data into the collection. Reading the data and using the data occurs in two
separate functions both for readability but also because after the first time the data is already in
the collection and this function is never again called.
In this lecture we have discussed a strategy for performing a translation of labels and command
buttons on forms from one language to another. Using existing forms as well as existing translation
data provided by Rocky Smolin, we designed a class to open the translation table and pull the
translation strings out into a collection, each translation string keyed by the control name.
Once we had the data in the collection, we then designed code to load the data out of the class into
the caption properties of the controls on the form. The data is loaded from the recordset into the
collection one time, and from then on the data in the collection is used to translate the form. In
other words, the data is cached and ready to go the next time the form needs translation.
--
John W. Colby
www.ColbyConsulting.com