Combo box to select report language

[Gilberto]

Hello,

I need the user to be able to select the language of some reports. Right now every product has already its current french and english name. And I was about to simply create another whole set of reports with the modified queries.

I think there must be a way to create a combobox or user input so that the user can select the language...How can i do this?

Currently the record source for the FRENCH reports is:

 SELECT Engineering.Project, Engineering.[Product Name French],  Engineering.Commodity, Engineering.Seating, Engineering.Variant1, Engineering.Description, Engineering.Material, Engineering.Weight, Engineering.Thickness, Engineering.Density, Engineering.Variant2, Engineering.Variant3, Engineering.[Level 1 Driver], Engineering.[Level 1 Passenger], Engineering.[Level 1 Rear], Engineering.[Level 2 Driver], Engineering.[Level 2 Passenger], Engineering.[Level 2 Rear], Engineering.[Level 3 Driver], Engineering.[Level 3 Passenger], Engineering.[Level 3 Rear], Engineering.[Level 3 Rear], Engineering.Level4Driver, Engineering.Level4Passenger, Engineering.Level4Rear, Engineering.Level5Driver, Engineering.Level5Passenger, Engineering.Level5Rear, Engineering.Level6Driver, Engineering.Level6Passenger, Engineering.Level6Rear, Engineering.Level7Driver, Engineering.Level7Passenger, Engineering.Level7Rear FROM Engineering WHERE (((Engineering.Variant1)=-1)); 

and the one for the ENGLISH reports needs to be:

SELECT Engineering.Project, [Product Names].[Product Name English],  Engineering.Commodity, Engineering.Seating, Engineering.Variant1, Engineering.Description, Engineering.Material, Engineering.Weight, Engineering.Thickness, Engineering.Density, Engineering.Variant2, Engineering.Variant3, Engineering.[Level 1 Driver], Engineering.[Level 1 Passenger], Engineering.[Level 1 Rear], Engineering.[Level 2 Driver], Engineering.[Level 2 Passenger], Engineering.[Level 2 Rear], Engineering.[Level 3 Driver], Engineering.[Level 3 Passenger], Engineering.[Level 3 Rear], Engineering.[Level 3 Rear], Engineering.Level4Driver, Engineering.Level4Passenger, Engineering.Level4Rear, Engineering.Level5Driver, Engineering.Level5Passenger, Engineering.Level5Rear, Engineering.Level6Driver, Engineering.Level6Passenger, Engineering.Level6Rear, Engineering.Level7Driver, Engineering.Level7Passenger, Engineering.Level7Rear FROM [Product Names] INNER JOIN Engineering ON [Product Names].[Product Name]=Engineering.[Product Name French]  WHERE (((Engineering.Variant1)=-1));   

Thanks,
Gilberto

[nico5038]

Hello,

I need the user to be able to select the language of some reports. Right now every product has already its current french and english name. And I was about to simply create another whole set of reports with the modified queries.

I think there must be a way to create a combobox or user input so that the user can select the language...How can i do this?

Currently the record source for the FRENCH reports is:

 SELECT Engineering.Project, Engineering.[Product Name French],  Engineering.Commodity, Engineering.Seating, Engineering.Variant1, Engineering.Description, Engineering.Material, Engineering.Weight, Engineering.Thickness, Engineering.Density, Engineering.Variant2, Engineering.Variant3, Engineering.[Level 1 Driver], Engineering.[Level 1 Passenger], Engineering.[Level 1 Rear], Engineering.[Level 2 Driver], Engineering.[Level 2 Passenger], Engineering.[Level 2 Rear], Engineering.[Level 3 Driver], Engineering.[Level 3 Passenger], Engineering.[Level 3 Rear], Engineering.[Level 3 Rear], Engineering.Level4Driver, Engineering.Level4Passenger, Engineering.Level4Rear, Engineering.Level5Driver, Engineering.Level5Passenger, Engineering.Level5Rear, Engineering.Level6Driver, Engineering.Level6Passenger, Engineering.Level6Rear, Engineering.Level7Driver, Engineering.Level7Passenger, Engineering.Level7Rear FROM Engineering WHERE (((Engineering.Variant1)=-1)); 

and the one for the ENGLISH reports needs to be:

SELECT Engineering.Project, [Product Names].[Product Name English],  Engineering.Commodity, Engineering.Seating, Engineering.Variant1, Engineering.Description, Engineering.Material, Engineering.Weight, Engineering.Thickness, Engineering.Density, Engineering.Variant2, Engineering.Variant3, Engineering.[Level 1 Driver], Engineering.[Level 1 Passenger], Engineering.[Level 1 Rear], Engineering.[Level 2 Driver], Engineering.[Level 2 Passenger], Engineering.[Level 2 Rear], Engineering.[Level 3 Driver], Engineering.[Level 3 Passenger], Engineering.[Level 3 Rear], Engineering.[Level 3 Rear], Engineering.Level4Driver, Engineering.Level4Passenger, Engineering.Level4Rear, Engineering.Level5Driver, Engineering.Level5Passenger, Engineering.Level5Rear, Engineering.Level6Driver, Engineering.Level6Passenger, Engineering.Level6Rear, Engineering.Level7Driver, Engineering.Level7Passenger, Engineering.Level7Rear FROM [Product Names] INNER JOIN Engineering ON [Product Names].[Product Name]=Engineering.[Product Name French]  WHERE (((Engineering.Variant1)=-1));   

Thanks,
Gilberto

The "normalized" way would have been to create a tblProduct with atleast three fields: ID (autonumber or unique number used in stock), ProductNameEnglish, ProductNameFrench
When the Productname is needed you link the tblEngineering to tblProduct using the ID.
When you have a frmReport form you can add a combo or Optionbox to determine the language and in the query you can refer to thet field to determine the language to use like:

select IIF(Forms("frmReport")!Option=1,ProductNameEnglish,ProductNameFrench) as Productname, ...
 

Now use on the form the field [ProductName] form the report query and it will be filled with the right language.

Nic;o)

[Gilberto]

The "normalized" way would have been to create a tblProduct with atleast three fields: ID (autonumber or unique number used in stock), ProductNameEnglish, ProductNameFrench
When the Productname is needed you link the tblEngineering to tblProduct using the ID.
When you have a frmReport form you can add a combo or Optionbox to determine the language and in the query you can refer to thet field to determine the language to use like:

select IIF(Forms("frmReport")!Option=1,ProductNameEnglish,ProductNameFrench) as Productname, ...
 

Now use on the form the field [ProductName] form the report query and it will be filled with the right language.

Nic;o)

Thanks again Nico. I get the idea, but i have problems with the query...where exaclty should i use this code?

Thanks
Gilberto

[nico5038]

Basically it's no code, but part of the query.
In the SQL mode yuo can enter instead of a fieldname from the table:
IIF(Forms("frmReport")!Option=1,ProductNameEnglish ,ProductNameFrench) as Productname,

And in the graphical query editor you enter in the cell that holds normally the name of a tablefield:
Productname:IIF(Forms("frmReport")!Option=1,Produc tNameEnglish,ProductNameFrench)

Clearer?

Nic;o)