Faster solution for multilingual interface available?

[perlfan]

Hi,

I am adding the functionality of changing interface languages to my application, however, I realized that now it takes 3-4 seconds until the form loads, which is a bit frustrating. This time lag appears already with small forms with few labels and buttons. So obviously I would like to reduce this lag to a minimum.

Do you know a more efficient way to achieve a multilingual solution/another code method? I am using a MySQL database/ODBC connection.

Thanks a lot for advice! FRANK

This is the code in my onLoad-event:

For Each ctl In Me.Controls
    If TypeOf ctl Is Label Then
        If ctl.Name <> "" And Not IsNull(ctl.Name) Then
        ctl.Caption = LoadString(ctl.Name, Language)
        End If
    ElseIf TypeOf ctl Is CommandButton Then
        If ctl.Name <> "" And Not IsNull(ctl.Name) Then
        ctl.Caption = LoadString(ctl.Name, Language)
        End If
    End If
Next ctl

This is the function:

Function LoadString(lngNumber, Language) As String

On Error GoTo Ende
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

If Language = "English" Then
ID = 2
ElseIf Language = "German" Then
ID = 9
End If


Set db = CurrentDb
strSQL = "SELECT " & lngNumber & " FROM translations_TM WHERE translations_TM.ID = " & ID & ""
Set rst = db.OpenRecordset(strSQL)

LoadString = rst.Fields(lngNumber)

Ende:
End Function

 

[pbaldy]

I've never had to do this, so untested. I assume lngNumber is actually a field/control name? My guess is that the time is taken up by the repeated calls to the data. How about making the recordset public, opening it with all fields before the loop, then have your function just return the specified field from it? Then close it after the loop. That way you only open the recordset once. In fact if I'm reading it right at that point you don't even need the function, just

ctl.Caption = rst.Fields(ctl.Name)

You could have a function to open the recordset on the appropriate language, and another to close it and set to nothing (which you aren't doing by the way).

 

[smig]

I think the best will be to create a public array that will hold both ctl.Name and ctl.Caption

If you will give your controls numbered names (Though it will be harder to maintain) you can even get a better result by going directly to the correct variable in the array.
Something like:

me.Ctrl1.Caption = CaptionsArray(1)
me.ctrl2.Caption = CaptionsArray(2)

In any case I think using an aray will be the fastest way, espacially if you want to add more languages

 

[perlfan]

Thank you - will go for the array solution. In addition, I chose to use the language table as offline Access table within the application, so the language strings will not be pulled from MySQL anymore as it will always take a little to do that.
Thanks again - FRANK

 

[smig]

This should be as part of your application so you can update with no need to touch the data.
You can put the number of the location in the array in the tag property of the cotrol
code will look like:
me.ControlName.Caption = CaptionsArray(me.ControlName.Tag)


and for all controls:

[COLOR=darkred]dim Ctrl as control[/COLOR]
 
[COLOR=darkred]for each Ctrl in Me.Controls[/COLOR]
[COLOR=darkred]Ctrl.Caption = CaptionsArray(Ctrl.Tag)[/COLOR]
[COLOR=darkred]next[/COLOR]

you can also make it into a public:

in the Form open event:
[COLOR=darkred]Call fnSetCaptions(me)[/COLOR]
 
' -- The Public Sub
[COLOR=darkred]Public Sub fnSetCaptions(frm as form)[/COLOR]
[COLOR=darkred]dim Ctrl as control[/COLOR]
 
[COLOR=darkred]for each Ctrl in frm.Controls[/COLOR]
[COLOR=darkred]Ctrl.Caption = CaptionsArray(Ctrl.Tag)[/COLOR]
[COLOR=darkred]next[/COLOR]
[COLOR=darkred]end Sub[/COLOR]