[AccessD] Classes and Events - clsXlateForm

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



More information about the AccessD mailing list