Multiple label caption in reports (depending on customer language)

[stephanie77]

I've search a lot on the web and didn't find anything near what I would like to get/do in my DB so again, I'm begging for your help and creativity!!

I would like Access to manage automatically the caption of my labels (and fields ultimately via a lexique or something like that but let's stick to the darn labels first) in a report, based on the language of for example, the customer to whom this report is dedicated (report=invoice).

I only have two choices to deal with : french or english



Just so you know, each company in my customers and suppliers tables have a field that specifies which language they are doing business in.

I don't need the translation for users, but really on pdf/printed/extract reports (I specify cause all I have found on the web was translation for the DB users!!!)

I ask for multiple label captions cause that's the only way I could think of doing this but if you have better idea, please inspire me!

Thanks in advance!

[Micron]

I think this would be easier with textboxes that look like labels as opposed to actual labels. No idea where your two sets of data comes from, but if you have a 'language' flag as you say, why not have a query return all the values to the report controls being used as labels? One query could be for French, the other English. The proper query could be opened based on the language flag.

[CJ_London]

Sounds like you need a translation table - something like

English.................French
Customer..............Client
Invoice Number.....Numéro de Facture

Although I would suggest something more normalised if you have many languages

In your customer table, have a field to indicate language

then in your report, if language=French then loop through each label and swap the text

something like

if language=French then
for each ctrl in me.controls
    if ctrl.controltype=aclabel then ctrl.caption=nz(dlookup("French","tblTranslations","English='" & ctrl.caption "'"),ctrl.caption)
next ctrl

or you can have a table to store name of report, name of control, language and phrase

reportname...controlname...language...phrase
Invoice.........lblInvNo.........1..............In voice Number
Invoice.........lblInvNo.........2..............Nu méro de Facture

etc

and a similar loop

set rst.currentdb.opnrecordset("SELECT * FROM tblCaptions WHERE reportname='" & me.name & "'")
while not rst.eof
    me(rst!controlname).caption=rst!phrase
    rst.movenext
wend

[NTC]

So both M and A have provided advanced examples with a table driven design. If a less complex solution meets your need you can make a duplicate of each label by language, arrange them on top of each other - and then in the On Format of the report section manage which labels are visible.

For instance have the user select which language report they seek - and use that flag as the criteria.

Toggling visibility of labels is typically more advantageous than making an entire duplicate set of reports by language. Although if the report count is low and it is certain they will not be redesigned - duplicate reports could be considered. However it can become a real trap as each redesign must be done twice.

Managing visibility of labels is simpler, in the programming sense, than a table driven approach. However if you have lots of reports and lots of languages then the table driven design will be far more efficient in the long run. So your implementation method depends on your requirements.

[stephanie77]

Wow, that's creativity!! Thanks a lot everyone!

I will have many reports to produce so I guess I'd better go with the translation table

Will do some tests with your propositions Ajax and see how it goes... stay tuned!