Dynamically changing captions

[ryetee]

I have a system that has a growing number of forms. On each form they are a variable number of labels/text boxes/command buttons etc. The captions for all of these are in english.

The database is to be deployed to a non english speaking country so I want to be able to dynamically change all the captions on the forms (not n the data) to the local language.

I know how to do this. For each control on load I decide the local language and change the caption accordingly
ie (and code is for illustration only and not necessarily syntactically correct)
if locallanguage = 1
me.control1.caption = "save in english"
me.control2.caption = "heading1 in english"
else
me.control1.caption = "save in local language"
me.control2.caption = "heading1 in local language"
end if
What I want to know is there and easy way to do this.
Rather than wade through each control on each form and find out what the name is can I generate anything that will give me all the control names and captions for each form (and report)?

 

[vbaInet]

Rather than wade through each control on each form and find out what the name is can I generate anything that will give me all the control names and captions for each form (and report)?

You'll still need to loop through each control and determine if it has a Caption property.

If you want to do things properly, I suggest that you generate an inventory of captions saved in a table and in this table it will map to the different variations of captions per control. So before deploying, create a function that will run through each form, use a recordset to open the table, and looping through each record you will accordingly rename the caption. Now this will require that you have an up-to-date inventory of controls.

 

[ryetee]

You'll still need to loop through each control and determine if it has a Caption property.

If you want to do things properly, I suggest that you generate an inventory of captions saved in a table and in this table it will map to the different variations of captions per control. So before deploying, create a function that will run through each form, use a recordset to open the table, and looping through each record you will accordingly rename the caption. Now this will require that you have an up-to-date inventory of controls.

I think my question should have been how do I produce an inventory of captions? Do I basically have to go into design mode and go through each control and manually build this inventory or is there some report I can get out of access that will break the back of it?

 

[vbaInet]

Bear with me as I've not done any VBA or touched Access in a long while.

There are two stages to this process. One is the tables and the other is the code.

For the tables, you'll need three: Form Name, Controls and Form-Control (junction table).
You can get Form Names from the MSysObjects system table so you don't need a table there, just link to it. As for the Controls table you'll need a couple of fields; ControlID, CaptionEn, CaptionFr, CaptionEs etc... just keeping it simple otherwise you'll need a separate junction table for the Captions. The third table will hold two fields; FormName and ControlID/Name.

As for the code, perform a search on the forum for looping through controls but I think you already know this. You'll need recordsets that will insert into the tables accordingly.

 

[ryetee]

Bear with me as I've not done any VBA or touched Access in a long while.

There are two stages to this process. One is the tables and the other is the code.

For the tables, you'll need three: Form Name, Controls and Form-Control (junction table).
You can get Form Names from the MSysObjects system table so you don't need a table there, just link to it. As for the Controls table you'll need a couple of fields; ControlID, CaptionEn, CaptionFr, CaptionEs etc... just keeping it simple otherwise you'll need a separate junction table for the Captions. The third table will hold two fields; FormName and ControlID/Name.

As for the code, perform a search on the forum for looping through controls but I think you already know this. You'll need recordsets that will insert into the tables accordingly.

I (think) know what you're saying vbaInet (for some reason I want to call you Ian!) but it's getting the data for the tables that I'm struggling with.

I can get a list of controls easily enough running the database documenter and just returning the names gives me
Combo Box: cboComponent
Command Button: cmdCancel
Command Button: cmdSaveAndExit
Label: Combo31_Label
etc etc
Be good if I could get
Combo Box: cboComponent
Caption "A" (or nothing if there is no label)
Command Button: cmdCancel
Caption "B"
Command Button: cmdSaveAndExit
Caption "C"
Label: Combo31_Label

Now I can run said same documenter for names and properties and get the following which is just as much of a pain as manually going through the forms.

Command Button: cmdSaveAndExit
Alignment: 2 AutoRepeat: False
BackColor: 16777215 BackShade: 100
BackStyle: 0 BackThemeColorInd -1
ex:
BackTint: 100 Bevel: 0
BorderColor: 0 BorderLineStyle: 0
BorderShade: 100 BorderStyle: 1
BorderThemeColorI -1 BorderTint: 100
ndex:
BorderWidth: 0 BottomPadding: 30
Cancel: False Caption: Save and Exit
ColumnEnd: 0 ColumnStart: 0
ControlType: 104 CursorOnHover: 0
Default: False DisplayWhen: 0
Enabled: True EventProcPrefix: cmdSaveAndExit
FontBold: No FontItalic: False
FontName: Trebuchet MS FontSize: 8
FontUnderline: False FontWeight: 400
ForeColor: 16777215 ForeShade: 100
ForeThemeColorInd -1 ForeTint: 100
ex:
Glow: 0 Gradient: 0
GridlineColor: 0 GridlineShade: 100
GridlineStyleBottom 0 GridlineStyleLeft: 0
:
GridlineStyleRight: 0 GridlineStyleTop: 0
GridlineThemeColor -1 GridlineTint: 100
Index:
GridlineWidthBotto 1 GridlineWidthLeft: 1
m:
GridlineWidthRight: 1 GridlineWidthTop: 1
Height: 390 HelpContextId: 0
HorizontalAnchor: 0 HoverColor: 16777215
HoverForeColor: 16777215 HoverForeShade: 100
HoverForeThemeCol -1 HoverForeTint: 100
orIndex:
HoverShade: 100 HoverThemeColorIn -1
dex:
HoverTint: 100 ImageData: Long binary data
Layout: 0 LayoutID: 0
Left: 9014 LeftPadding: 30
Name: cmdSaveAndExit OldBorderStyle: 1
OnClick: [Event Procedure] Picture: (none)
PictureCaptionArran 0 PictureType: 0
gement:
PressedColor: 16777215 PressedForeColor: 16777215
PressedForeShade: 100 PressedForeTheme -1
ColorIndex:
PressedForeTint: 100 PressedShade: 100
PressedThemeColor -1 PressedTint: 100
Index:
QuickStyle: 0 QuickStyleMask: 0
ReadingOrder: 0 RightPadding: 30
RowEnd: 0 RowStart: 0
Section: 1 Shadow: 0
Shape: 0 SoftEdges: 0
TabIndex: 0 TabStop: True
TextFontCharSet: 0 ThemeFontIndex: -1
Top: 737 TopPadding: 30
Transparent: False UseTheme: False
VerticalAnchor: 0 Visible: True
Width: 1365


Am I making sense as to what I want?

 

[vbaInet]

That's why I mentioned that you should search the forum for code that loops through each control on a form. This one has been answered many times, even by myself. Once you get that working you can add code that will loop through all the forms. Finally you can then add code that will insert the captions into the relevant tables.

 

[ryetee]

That's why I mentioned that you should search the forum for code that loops through each control on a form. This one has been answered many times, even by myself. Once you get that working you can add code that will loop through all the forms. Finally you can then add code that will insert the captions into the relevant tables.

OK got it!! Misinterpreted that.

 

[vbaInet]

Let me know what you find and we can work with that.

 

[ryetee]

Let me know what you find and we can work with that.

OK found some code and looks like i'm getting what I want ie

For every form a list of all control together with their caption if they have one. So I think from now on I can more or less handle this!

The wy I was going to do this btw is for each form I would put code in the Form Load event to read from table and update each control. Reading you post #2 not sure if you had the same idea?

 

[BlueIshDan]

Might be able to create a public function in a module and simply pass Me as a parameter in the load event of each form.

 

[vbaInet]

The wy I was going to do this btw is for each form I would put code in the Form Load event to read from table and update each control. Reading you post #2 not sure if you had the same idea?

A different approach. What I was proposing is to change the captions and save the db before shipping, hence the tables mentioned; instead of changing the caption each time the form loads.

 

[gemma-the-husky]

sort of this code if you like

 for each form in container.forms (' the correct syntax for that is tricky)
      open form in design mode
      for each control in form         
           if control.controltype = aclabel then
                 process the label   'ie store the existing caption
            end if
      next
      close the form
      doevents ' tidies the display
 next

you will need somewhat similar code in each form open event to set the label captions when you open the form.

 

[ryetee]

Thanks to everyone for the help so far.
I now have a text file with name of form, control name, caption etc etc - all i need to build up the tables I need to store this inventory (and also have caption in multiple languages) which is good!

Once I've done that it is my intention that on load of form I call a function to change all the captions for the form passing over form name and say language.

In the function I will then get all the data I need (not worked out how to do that yet!).

Before that I thought I'd play around a bit!! I have a simple form with a command button on. On loading the form I call a function fLoadForm as follows.

Call Module2.fLoadForm(Me, "GB", Me.Command379, "new caption")

My latest incarnation of the function is as follows but I'm getting errors the latest being 2465 application-defined or user-defined error

Public Function fLoadForm(frmANy As Form, fCountry As String, fcontrol As CommandButton, fcaption As String)

frmANy.fcontrol.Caption = fcaption


End Function


Incidentally if i change fcontrol in the above function to the name of the control it works.
ie change
frmANy.fcontrol.Caption = fcaption
to
frmANy.Command379.Caption = fcaption

Obviously the original isn't being "converted" to the right thing
Can anyone help at all?

 

[vbaInet]

I now have a text file with name of form, control name, caption etc etc - all i need to build up the tables I need to store this inventory (and also have caption in multiple languages) which is good!

What I'm trying to get you to do is to set everything up before you ship it out the countries, that's why I mentioned the three tables in your master application. Think of it as a setup routine.

This is how it would work:
1. Open the pre-populated tables (containing the caption, form names and control names) in respective recordsets
2. For each form in the recordset, open the form in design view, change the caption for each control and close and save the form.
3. Ship this database to your foreign users.

On the other hand, if you're happy to keep changing the caption on the fly that's absolutely fine, but it wouldn't be my first choice. As your application grows there are more important things that you would like to run on the fly.

 

[vbaInet]

One more thing ryetee, you see that nice little Code box that gemma-the-husky used to wrap his code in, could you possibly use that in the future. It helps to make code readable.

 

[ryetee]

OK worked this out
call should be
Call Module2.fLoadForm(Me, "GB", "Command379", "caption name")
and function should be
Public Function fLoadForm(frmAny As Form, fCountry As String, fcontrol As String, fcaption As String)

frmAny.Controls(fcontrol).Caption = fcaption

End Function

Just need towork out how toget that info from a table into a function call now!

 

[ryetee]

What I'm trying to get you to do is to set everything up before you ship it out the countries, that's why I mentioned the three tables in your master application. Think of it as a setup routine.

This is how it would work:
1. Open the pre-populated tables (containing the caption, form names and control names) in respective recordsets
2. For each form in the recordset, open the form in design view, change the caption for each control and close and save the form.
3. Ship this database to your foreign users.

On the other hand, if you're happy to keep changing the caption on the fly that's absolutely fine, but it wouldn't be my first choice. As your application grows there are more important things that you would like to run on the fly.

I missed this before. I understand the 3 tables and I think I need these if I'm changing the captions on form load.
What I don't how to do (at the moment and I'm just researching it now) is working with recordsets. I presume that 2 above is done within VBA in which case I don't know how to process forms (eg open in design mode).

One of the reasons I want to do this dynamically at form load time for each form used (or for all forms when 1st form is loaded) is that I want to give control to the user over his headings and text on each form. He has recently decided that the name of a field is wrong and it should be changed. I know he's not 100% sure what it should be and I know for a fact he'll change his mind again.

 

[vbaInet]

If every user has the ability to change their captions then you might need a couple more tables. Think about the design and let us know.

There are many threads on this forum which cover recordsets but here are some other links:

http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners
http://allenbrowne.com/ser-29.html

Any problems, give us a shout.

 

[ryetee]

If every user has the ability to change their captions then you might need a couple more tables. Think about the design and let us know.

There are many threads on this forum which cover recordsets but here are some other links:

http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners
http://allenbrowne.com/ser-29.html

Any problems, give us a shout.

Cheers, I'll take a look!

There will only be 1 user (probably at each location) that can access and update the captions.

 

[gemma-the-husky]

given a table of captions, this sort of logic - but it might be slowish for a lot of captions because of the number of dlookups

 form open or load event
  
dim ctrl as control
  
  for each ctrl in me.controls
    if ctrl.controltype = aclabel then
  
        'languagecodes, maybe English=1, german=2 French = 3
        newcaption = dlookup("caption" & languagecode,"captiontable","form = " & formname & " label = " & labelname)
         
        ctrl.caption =newcaption
     end if
 next