Updating Label Captions

[ayfour]

Hi All

** How do I replace a label caption with value from a field in my database? **

I am putting together an app using forms, and each form has lots of text boxes with label boxes.

In our database, we have a table which gives us a summary list of all field names (as used in the text boxes mentioned above), and a corresponding list of "plain english" phrases that we would like to have appear in the label box. I want to replace the default label with the phrase.

Any advice on how to achieve this programmatically would be appreciated.

To give you an idea of the size, I need to create about 80 different forms from scratch, and I will probably use over 1,000 fields in these forms. I don't really want to do this manually if i can help it.

(I have only been working with Access and VBA for a few weeks, and am using this to prototype our app.)

Thanks in advance.

Cheers
K

 

[wazz]

welcome to awf.

http://msdn.microsoft.com/en-us/library/aa221170(office.11).aspx
http://msdn.microsoft.com/en-us/library/aa221167(office.11).aspx

hth.

 

[gemma-the-husky]

but basically


cotrols("labelname").caption = "whatever"

or

varlabel = "label1"
controls(varlabel).caption = "whatever"

 

[ayfour]

Hi guys
Thanks for your answers.

Gemma's answer helps me out a bit, as it will be easier than going into each field to change the name manually.

I like Wazz's tip, but I can't quite get my head around it, as it is a little advanced for my skills at this stage.
The CreateControl is what I think I am after.
Does this mean that I need to ditch the existing labels that are created by the Form Wizard? And that this CreateControl Method will create new labels that I will need to position on the form?

Cheers
K

 

[Galaxiom]

The advanced way is to create the labels as textboxes. Set their background and border to transparent and disable them. In effect they become indistinguishable from labels except they display a value from a table.

It is an awesome technique where many different captions are required for a label such as multi language support. No code required. Their control source simply includes the current language key from the caption table.

 

[wazz]

ayfour, i wasn't sure how far you wanted to go with this so i threw in those links. if you're building the forms with a wizard or by hand, i think you can disregard those links for now.

 

[gemma-the-husky]

personally i wouldnt try creating controls

this is a pretty advanced idea - if you are new to access you will have your hands full managing normal controls

its not only creating a control thats tricky - once created, you need to be able to do something useful with it - and this is hard at runtime.

 

[ayfour]

Thanks guys - your help and advice is most appreciated.

cheers
K

 

[pem406]

Hi GalaxiomAtHome,

"It is an awesome technique where many different captions are required for a label such as multi language support. No code required. Their control source simply includes the current language key from the caption table. "

This looks like exactly what I am trying to do but I'm stuck.

I have a simple logon box that asks the user to select their name when they open the database. Ideally, I'd like the database to display the headings and command button captions in the relevant language as soon as they login.

The list of users is stored in a table and I thought of storing a language reference against their username. I can also put together a caption table of all the terms and the translations.

The problem is I don't know how to match the two. How do you set the control source of the text box so it looks up the right language?
Does this also work with Command buttons?

 

[gemma-the-husky]

Hi GalaxiomAtHome,

"It is an awesome technique where many different captions are required for a label such as multi language support. No code required. Their control source simply includes the current language key from the caption table. "

This looks like exactly what I am trying to do but I'm stuck.

I have a simple logon box that asks the user to select their name when they open the database. Ideally, I'd like the database to display the headings and command button captions in the relevant language as soon as they login.

The list of users is stored in a table and I thought of storing a language reference against their username. I can also put together a caption table of all the terms and the translations.

The problem is I don't know how to match the two. How do you set the control source of the text box so it looks up the right language?
Does this also work with Command buttons?

effectively the logic of what you are trying to do is this

in the forms open event

for each control on the form
if its a label
then the label caption becomes the appropriate label caption in the language
next control

so how do you decide on the caption - heres a couple of ways

a) have a table that stores alternative captions, and look them up
b) store the alternative captions in the labels tag property, and set them from that - you just need a layout convention say english, french, german, spanish, and if you are in french, you extract the value after the first comma

i suspect b) might be a lot easier, but will still involve a lot of typing!

i dare say you could find a way of entering all these alternatives in a spreadsheet, and automating it from the spreadsheet - you need to solve the problem of manipulating forms in design mode programmatically, but you only need to do that once.

 

[Galaxiom]

effectively the logic of what you are trying to do is this

in the forms open event

for each control on the form
if its a label
then the label caption becomes the appropriate label caption in the language
next control

No. Don't use label controls. These have to have their caption set with VBA. By using a bound textbox instead, the control source can connect the text displayed in the "label" directly to a table. Delete the original labels from the associated control.

The LabelCaptions table would have LanguageID, LabelName and CaptionText.

The control source of the "labels" includes a relationship between the table of alternative "captions", a languageID and the Language field of the logged in user's profile. It is brilliant.

This thread is where I learned about the technique. There is some good discussion of the finer features and a sample database.
http://www.access-programmers.co.uk/forums/showthread.php?t=177033

My original contribution to that thread shows the code for the technique Dave refers to but Endre's is a first class solution that invites one to consider the potential for a range of dynamic form design possibilities.

BTW Here is another interesting example of of Endre's creative contributions that I stumbled across while searching for that page.

http://www.access-programmers.co.uk/forums/showthread.php?t=177012

 

[gemma-the-husky]

No. Don't use label controls. These have to have their caption set with VBA. By using a bound textbox instead, the control source can connect the text displayed in the "label" directly to a table. Delete the original labels from the associated control.

The LabelCaptions table would have LanguageID, LabelName and CaptionText.

The control source of the "labels" includes a relationship between the table of alternative "captions", a languageID and the Language field of the logged in user's profile. It is brilliant.

This thread is where I learned about the technique. There is some good discussion of the finer features and a sample database.
http://www.access-programmers.co.uk/forums/showthread.php?t=177033

My original contribution to that thread shows the code for the technique Dave refers to but Endre's is a first class solution that invites one to consider the potential for a range of dynamic form design possibilities.

BTW Here is another interesting example of of Endre's creative contributions that I stumbled across while searching for that page.

http://www.access-programmers.co.uk/forums/showthread.php?t=177012

i will have a look at these threads - its amazing how many difficult issues have already been solved in elegant ways - plenty of ways to skin cats, obviously

 

[pem406]

Wow!
I saw your original post using the Dlookup. Coming from using excel a lot, this was the sort of solution I was thinking of but I see what you mean about Endre's way of doing it.

I like the idea of a source table because I may have to add new languages in the future and this will let me do it.

Nice one, thanks all for your help.