MS Access Multiple language support for forms only

Question

Is there any way to apply another language to Access forms. I have an issue while creating forms, as the DB values are stored in English and I have to generate two identical forms from same Table in different languages. Everything goes well till I reach a look up field like Gender. My table optional values are 'Male' and 'Female' and is good for English form but How can I change that in my non-English form without changing table value

Answer 1

One way to do it would be to store the actual values in the main table as M and F, e.g.

ID  FirstName  Gender
--  ---------  ------
 1  Gord       M     
 2  Angie      F     

and create a reference table named [Genders] like so:

TableValue  Language  Translation
----------  --------  -----------
M           fr_ca     masculin   
F           fr_ca     féminin    
M           en_us     Guy        
F           en_us     Girl       
M           en_ca     Male       
F           en_ca     Female     

On your form, create a hidden unbound text box named txtFormLanguage, and in the Form_Load event handler populate it like this:

Private Sub Form_Load()
    Me.txtFormLanguage = IIf(IsNull(Me.OpenArgs), "en_ca", Me.OpenArgs)
End Sub

Now your combo box can use the following as its Row Source...

SELECT TableValue, Translation FROM Genders WHERE (((Genders.Language)=[txtFormLanguage]));

...and have other properties similar to the following:

Bound Column: 1
Column Count: 2
Column Widths: 0";1"

When the form is opened normally (without OpenArgs)...

Docmd.OpenForm "ClientForm", acNormal, , , acFormEdit, acWindowNormal

...it defaults to en_ca (English, Canada) and the combo box displays

Male
Female

When the form is opened for fr_ca (French, Canada)...

Docmd.OpenForm "ClientForm", acNormal, , , acFormEdit, acWindowNormal, "fr_ca"

...the combo box displays

masculin
féminin