Dual languages (2000)

Topic

New Reply

I have developed a database with a variety of forms and reports in English (useful as that is the only language I am fluent in). I now need to adapt the system to display the forms and reports in Spanish. (someone else will be doing the translating).
What would people suggest as the best method to do this?
I’ve considered the on open event with lines to change the caption for each item on the form – or a database with an id for each piece of text and the english / spanish entries as alternate fields.
Any guidance appreciated
John

Reply | Quote

Replies

A simplistic solution would be to create a copy of the database and change all the captions. But of course, that is not very flexible – if you changed something later on, you’d have to do it in two places, with the chance of discrepancies.

A table with all captions in two (or more) languages is a very good idea.

You can set the captions in the On Open event of each form/report. If you choose this method, don’t enumerate the labels explicitly, but loop through them:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acLabel Then
ctlCaption = …
End If
Next ctl

Another possibility: change all labels to text boxes that have Locked = Yes and Enabled = No, and that are formatted to look like labels. Set the Control Source of these text boxes to an expression that uses DLookup to retrieve the correct caption.

Both options might cause a noticeable delay when opening forms and reports.

Yet another option is to write a procedure that loops through all forms in the database, opens them in design view, sets the captions of the labels, and closes/saves them. Then the same for reports. This requires the person that runs this procedure to open the database exclusively, and to have permission to modify the design of forms and reports.

Reply | Quote

[indent]

---

If you choose this method, don’t enumerate the labels explicitly, but loop through them

---

[/indent]With this method you’ll still have to determine what control will have what text.
I suggest a simple select case in the on open event of the form. Something like :

Select Case Language
   Case "English"
      Me.Label1.Caption = ...
      Me.Label2.Caption = ...
   Case "Spannish"
      Me.Label1.Caption = ...
      Me.Label2.Caption = ...
End Case

Second thought:
If you want to use Hans’s code you could put the two captions in the Tag property of the control separated by a / and use:
Me.ctl.caption = IIF(Language = “English”,left(me.ctl.tag,instr(Me.ctl.tag,”/”)-1),mid$(me.ctl.tag,instr(me.ctl.tag,”/”)+1)

Reply | Quote

[indent]

---

If you choose this method, don’t enumerate the labels explicitly, but loop through them

---

[/indent]With this method you’ll still have to determine what control will have what text.
I suggest a simple select case in the on open event of the form. Something like :

Select Case Language
   Case "English"
      Me.Label1.Caption = ...
      Me.Label2.Caption = ...
   Case "Spannish"
      Me.Label1.Caption = ...
      Me.Label2.Caption = ...
End Case

Second thought:
If you want to use Hans’s code you could put the two captions in the Tag property of the control separated by a / and use:
Me.ctl.caption = IIF(Language = “English”,left(me.ctl.tag,instr(Me.ctl.tag,”/”)-1),mid$(me.ctl.tag,instr(me.ctl.tag,”/”)+1)

Reply | Quote

See the attached database (Access 2000 format. zipped). I have used the Tag property of the labels and of the form to retrieve the captions from the table.

Note: since I don’t know any Spanish, the labels only indicate the language.

Reply | Quote

Nice but a lot of DLookups for complex forms and a table to maintain.
See my second thought in my previous post.

Reply | Quote

That is a clever solution too. It is simpler, but becomes unwieldy if you have more than two languages to deal with. The setup I proposed is more complex, but can be used with any number of languages.

It’s up to the OP to decide, we have given him some ideas to choose from.

Reply | Quote

I agree.

Reply | Quote

I agree.

Reply | Quote

That is a clever solution too. It is simpler, but becomes unwieldy if you have more than two languages to deal with. The setup I proposed is more complex, but can be used with any number of languages.

It’s up to the OP to decide, we have given him some ideas to choose from.

Reply | Quote

Nice but a lot of DLookups for complex forms and a table to maintain.
See my second thought in my previous post.

Reply | Quote

Thanks very much for that Hans. I hadn’t appreciated what the TAG field does – nice that it can be used as anything. I had presumed that your update was using the .caption directly – using the tag field gives it a method of linking information to the label which is much more straight forward.
I’ll have a play with the form you have sent – using the tag for the form/report to be modified is a great way to work it too.
If I wanted to make this routine a sub that could be accessed by any of the forms would I set it up as a public function and call it – I presume from looking at it that this procedure would work for both forms and reports without modification – just simply having the correct tag settings
John

Reply | Quote

The procedure is quite general, but if you want to make it into a public function, you must replace the references to Me with CodeContextObject.

Public Function SetCaptions()
Dim lngLanguageId As Long
Dim ctl As Control
lngLanguageId = DLookup(“Value”, “tblSettings”, “Setting = ‘LanguageID'”)
CodeContextObject.Caption = DLookup(“Caption”, “tblCaptions”, _
“ObjectID = ‘” & CodeContextObject.Tag & _
“‘ AND TagID = ‘0’ AND LanguageID = ” & lngLanguageId)
For Each ctl In CodeContextObject.Controls
If ctl.ControlType = acLabel Then
ctl.Caption = DLookup(“Caption”, “tblCaptions”, _
“ObjectID = ‘” & CodeContextObject.Tag & “‘ AND TagID = ‘” & _
ctl.Tag & “‘ AND LanguageID = ” & lngLanguageId)
End If
Next ctl
Set ctl = Nothing
End Function

Reply | Quote

The procedure is quite general, but if you want to make it into a public function, you must replace the references to Me with CodeContextObject.

Public Function SetCaptions()
Dim lngLanguageId As Long
Dim ctl As Control
lngLanguageId = DLookup(“Value”, “tblSettings”, “Setting = ‘LanguageID'”)
CodeContextObject.Caption = DLookup(“Caption”, “tblCaptions”, _
“ObjectID = ‘” & CodeContextObject.Tag & _
“‘ AND TagID = ‘0’ AND LanguageID = ” & lngLanguageId)
For Each ctl In CodeContextObject.Controls
If ctl.ControlType = acLabel Then
ctl.Caption = DLookup(“Caption”, “tblCaptions”, _
“ObjectID = ‘” & CodeContextObject.Tag & “‘ AND TagID = ‘” & _
ctl.Tag & “‘ AND LanguageID = ” & lngLanguageId)
End If
Next ctl
Set ctl = Nothing
End Function

Reply | Quote

Thanks very much for that Hans. I hadn’t appreciated what the TAG field does – nice that it can be used as anything. I had presumed that your update was using the .caption directly – using the tag field gives it a method of linking information to the label which is much more straight forward.
I’ll have a play with the form you have sent – using the tag for the form/report to be modified is a great way to work it too.
If I wanted to make this routine a sub that could be accessed by any of the forms would I set it up as a public function and call it – I presume from looking at it that this procedure would work for both forms and reports without modification – just simply having the correct tag settings
John

Reply | Quote

See the attached database (Access 2000 format. zipped). I have used the Tag property of the labels and of the form to retrieve the captions from the table.

Note: since I don’t know any Spanish, the labels only indicate the language.

Reply | Quote

A very neat idea Francois. I’ll test all the suggestions and see what works best. The simplicity of your second suggestion is great – but I can’t help but admire Hans’ database approach to it. I guess it just depends how quickly each works in the real life situation.
Thanks to both of you

Reply | Quote

A very neat idea Francois. I’ll test all the suggestions and see what works best. The simplicity of your second suggestion is great – but I can’t help but admire Hans’ database approach to it. I guess it just depends how quickly each works in the real life situation.
Thanks to both of you

Reply | Quote

I think that although the idea of a database listing the alternate spellings would be nice, I’m probably better to stick to a more simplistic approach.
I like the simplicity of the case statements suggested. I wasn’t sure how I would be able to use the loop to update a variety of differing labels.
I’ll have a play around and see what I get.

One pointer I do need….
If I have a table Language with a single field LangID which stores either GB or ES, how do I read this in as part of the on open for the first form – what commands would I use to open the database and store the value to a variable? I’ve never used a database for a table of opening constants, but can see that it would be a good idea.

John

Reply | Quote

I think that although the idea of a database listing the alternate spellings would be nice, I’m probably better to stick to a more simplistic approach.
I like the simplicity of the case statements suggested. I wasn’t sure how I would be able to use the loop to update a variety of differing labels.
I’ll have a play around and see what I get.

One pointer I do need….
If I have a table Language with a single field LangID which stores either GB or ES, how do I read this in as part of the on open for the first form – what commands would I use to open the database and store the value to a variable? I’ve never used a database for a table of opening constants, but can see that it would be a good idea.

John

Reply | Quote

A simplistic solution would be to create a copy of the database and change all the captions. But of course, that is not very flexible – if you changed something later on, you’d have to do it in two places, with the chance of discrepancies.

A table with all captions in two (or more) languages is a very good idea.

You can set the captions in the On Open event of each form/report. If you choose this method, don’t enumerate the labels explicitly, but loop through them:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acLabel Then
ctlCaption = …
End If
Next ctl

Another possibility: change all labels to text boxes that have Locked = Yes and Enabled = No, and that are formatted to look like labels. Set the Control Source of these text boxes to an expression that uses DLookup to retrieve the correct caption.

Both options might cause a noticeable delay when opening forms and reports.

Yet another option is to write a procedure that loops through all forms in the database, opens them in design view, sets the captions of the labels, and closes/saves them. Then the same for reports. This requires the person that runs this procedure to open the database exclusively, and to have permission to modify the design of forms and reports.

Reply | Quote