Multi Language
By Richard Rost
4 years ago
Multi Language Support in Microsoft Access
In this Microsoft Access tutorial, I will show you how to change your form labels and button captions to a different language. We'll switch between English and French, but you can use any language you want.
Elaine from Montreal, Quebec (a Gold Member) asks: I have workers in my office who don't all speak English. Some speak French. Is there any way to have my form labels and buttons switch from one to the other?
Members
Members will learn how to create a completely table-based solution so even end-users can modify the translation data without needing to make changes to the VBA code. We will loop through all of the controls on a form, find the ones that need to be translated, and look up the translation from the table. Fun stuff!
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Pre-Requisites
Links
Recommended Course
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, multi language support, forms in different languages, spanish, french, german, change the language on buttons and labels, bilingual, localizing, Creating Multi-Lingual Databases, multilingual
Subscribe to Multi Language
Get notifications when this page is updated
Intro In this video, we will talk about how to make your Microsoft Access database multilingual by allowing users to switch form button and label captions between English and French (or other languages) using a combo box and some basic VBA code. I will show you how to set up the combo box for language selection, how to name your controls properly, and how to update captions in the After Update or On Load events. If you want to make your Access forms more user-friendly for multilingual environments, this video walks you through the essential steps.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today we are going to talk about making your database multilingual. We are going to set up your database so that your buttons, your labels, and whatever other controls you want can flip from English to something else, like French.
Today's question comes from Elaine in Montreal, Quebec, one of my Gold Members. Elaine says, "I have workers in my office who do not all speak English. Some speak French. Is there any way to have my form labels and buttons switch from one to the other?"
Well yes, Elaine, it requires a little bit of coding but we can do it. I lived most of my life in Buffalo, New York, right across the border from Canada, so I know that whenever you go over there everything is in both English and French. I could see this being very useful for you up there.
Same thing on the other border. If you are in Texas, you have a lot of Spanish speaking people. There are a lot of Spanish speaking folks here in Florida. So yes, I can definitely see this would be useful.
Before we get started, some prerequisites: First, we are going to make a value list combo box that is going to have a list of the languages in it like English, French, Spanish, etc. If you do not know how to make a value list combo box, go watch this video now.
Also, you will need to know how to get a value from an open form like forms!MainMenuF!FieldName. If you do not know how to do that, go watch this video. That is how you can get a value from a different form.
We are going to need a little bit of VBA coding. There is no way around it to do something like this, to change labels and captions and properties on the fly. Yes, you have to use a little bit of VBA but it is not scary. Do not worry about it. I am going to show you everything you need to know.
If you have never done VBA programming before, go watch this. It is about 20 minutes long. It is on my website. It is on my YouTube channel. These are all free videos, by the way. I will put links down below that you can click on to go watch this stuff.
You are going to need to know what an After Update event is - basically when you change some value, like that combo box with a language in it, it will fire an event and do something else. So if you do not know what an After Update event is, go watch this video.
And finally, if you do not know what an If Then statement is, go watch this video to see what I did there. If you do not know what this is, then go watch this video. That is basically what an If Then statement is, else you get no.
Go watch any of those videos if you do not know what those topics are, but I am going to show you pretty much everything you need to know in this video. There is some background for you if you need it first. Let's get started.
Here I am in my TechHelp free template. This is a free database. You can download a copy from my website if you want to. You will find instructions down below.
First, add a combo box on our main menu so when the user logs on, if they want to switch from English to French, they can just do that here. If you have user logons set up in your database, you could tie their language to a setting in their user table, and that way every time they log on, the database will just go into that language.
If you want to learn how to do that and set up permissions and control your database with user logons and who has access to what, I do have a security seminar where I cover all that.
Let's go ahead and create a combo box. I will go to design view. I will open up the toolbox up here, find a combo box, and just drop it right on the form somewhere. The little wizard pops up. This is a good wizard and we are going to make a value list - a real simple list.
I am going to type in the values that I want. I am going to put in just one column: English and French. If you want to add Spanish or whatever, you can put as many as you want in here.
Next, what label would you like? "Language." And then finish. There is the combo box. Slide it over here. I will make it match that one, make it the same size. Put the label over here. If you are going to do it, you have to take the time to do it right.
Let's make that white and align it right. It is looking pretty good.
Let's open this guy's properties. I will call this Language. Normally, if I have a relational combo box where there is an ID hidden in there, I call it LanguageCombo. But since a value list combo box is treated just like a text box for the purposes of data, if that box says "English," the value in that box is "English." So I am just going to leave that as "Language" for now. I am not going to call it LanguageCombo like I usually do. The reason why I use LanguageCombo if it has an ID in there is because then the "combo" just reminds me that there is a hidden ID that you cannot see.
A couple settings I am going to change under Data: Limit to List, set to Yes. That way the user cannot just type in whatever they want. I am going to set Allow Value List Edits to No, so they cannot change the list of items. If you want to learn more about how that works, go watch this video.
I am also going to set the Default Value so this opens to English. When we start it up, it is defaulted to English. You can switch it to French.
Save it, Ctrl+S, close it, open it back up again, and there is my little box. That is all we have done so far. We do not have anything in it yet.
The next step is to make sure the stuff that we are going to change the caption properties of has good names. If you have been a good little Access developer, all your buttons have names. If not, Alex will yell at you. But your labels might not all have names.
If you look at the Customer form, whenever you create a field in here, "First Name" is the field, but the label is just whatever was the thing here, like Label1 or Label23. Normally that is okay. I do not usually mind that because we do not normally do stuff with labels, we do stuff with their text boxes. They just come along for the ride.
But if you are going to be changing the captions and stuff, you have to give those things good names. I used to do that when I was a lazy young programmer. It bites me in the behind to this day, because I still have code in my database like Command46_Click, and I have to figure out what Command46 is. Just, if you are going to use it in code, give it a good name.
That is just my rule. If you are going to learn from me, you have to follow Ricky's rules. I am going to make a list of these one of these days. I have a list of evil rules - things not to do. I am going to make a good list of things to do - best practices.
Let's start with the main menu and just check the things that we want to change the value of. So these buttons should all have good names. That is CustomerListButton, CustomerFormButton, CustomerContactButton. I think we are going to leave you out for today, so I am just going to delete you. Goodbye. The HelloWorldButton is good. This is the MainMenuLabel. That has a name.
How about the TodayIsLabel? That is Label15. Let's change that to TodayIsLabel. Technically this is CurrentDateLabel, but "TodayIsLabel" is fine so you know what it is. You do not have to worry about it too much.
This is the LanguageLabel. I am going to make this "Language." I do not like underscores. I know they are legal. I do not like them. Yes, I am a C programmer from way back and you use underscores for everything, but in Access, I like what is called camel case - the little humps meaning the capital letters in the middle.
So we have the LanguageLabel, the TodayIsLabel, and then we have a bunch of other buttons.
What we need to do is say, in the After Update event for this box, when the person changes this language, you need to go through and change all these guys' Caption property. The Caption is "Customer List." The label has a Caption. That is all we are doing - we are changing captions.
So we are going to click on this, and after Update... brings up a code window right down here. I am going to put in here:
If Language = "English" Then
'fill in all the captions in English
Else
'French
End If
If you have multiple, you could do this with as many languages as you want. You have Spanish, German, Klingon, whatever. You just have to put some ElseIf statements in here. But we are just going to do two for class today.
Put them all in. So we have MainMenuLabel.Caption = "Main Menu"
CustomerListButton.Caption = "Customer List"
and the rest of them. I will copy and paste them. I am not going to make you watch me sit here and paste all this stuff in.
Now, full disclosure, I do not speak French. I do not speak German. I do not speak Spanish. I speak English. That is it. I took Latin in high school, so I know little bits and pieces of different languages because everything is kind of based on Latin. And I have the Klingon dictionary. I tried to learn a little Klingon, but I just know the things they say in Star Trek like "Qapla'," but anyways. So I am just going to rely on Google Translate to give me my translations. So there is that. Excuse my translations. If they are horrible, I just basically copied this into Google Translate.
I did not do language labels, so let me copy that, copy and then paste. Go over to Google Translate. We have "language" and French and it is whatever that is: "languages." Okay, so let's copy that and put it there.
So there is my English and my French.
Now, this is going to fire in the After Update event for that language combo box. So you are ready? Save that. Let's go back over here. We are going to close the menu, open it up. You ready? Switch to French. Boom. Look at that. The label has changed. You have to make sure your labels are big enough, too. All your buttons must be wide enough to fit these, because I have noticed that a lot of French phrases are longer than the related English phrase.
So there is English and French, and then back to English. Look at that. It is really pretty straightforward to do.
Let's do something similar with the Formulaire Client - I am sorry, I am murdering the pronunciation - the Customer form. I will switch over to here. Let's just change a couple things here. I am not going to do the whole form.
Design view. The first thing is to make sure your labels are wide enough. So I am going to take the widest label, select them all, then go down to Size to Widest and now they are all that wide. Now, just the ones you are going to do: let's just do First Name and Last Name.
So we will make sure these have good names: FirstNameLabel and LastNameLabel.
We are going to do the same thing, but we are not going to have a combo box on this form. So this code has to run when this form opens up or loads - either one. Go to the form's properties, go to the Events tab and find either On Load or On Open. For this particular instance, it does not matter which one you pick. There is a difference - I will be covering that in a future video. I do cover it in my developer lessons.
For you guys, we are going to cover it a little bit. So hit that dot dot. We are going to use the On Load event. Load and Open are pretty much the same thing. We are going to do something very similar to what we did before, but this time it is going to run when this form opens.
If you want to switch the language, you have to close whatever other forms are open, go back to the main menu, switch the language, then as you open up those forms again, they will have the proper language in them because it will run in the On Load event.
Now we have to get the language setting off the main menu. The Main Menu has to stay open. If you close it, then these forms will not open.
If Forms!MainMenuF!Language = "English" Then
'English
Else
'French
End If
Do the same thing in here:
FirstNameLabel.Caption = "First Name"
LastNameLabel.Caption = "Last Name"
Save it, close it.
Open up the Customer form. Everything looks good. Close it. Switch to French. Open it up again, and there you go. Now the Customer form, at least the fields we did, are in French. That is all you have to do.
Obviously, you can do the same thing with the form caption if you want to. You can change the form captions. You can change whatever you want.
Now, the data in here is up to you. Data is just data. If you have notes that you want to keep in both English and French, that is a whole other complexity. This video is all about just changing the forms themselves so the database structure can be changed. As far as storing the data in multiple languages, you can use multiple fields. If you are only doing French and English, you can do something like "FrenchNotes" and "EnglishNotes" if you want to. You can use Google Translate as an API to translate on the fly. We can click a button and it will actually load up Google Translate. That is a whole different video. If you really want to see how to do that, let me know. I will show you how to do it. Post a comment down below.
If you have any message boxes, like error messages and stuff that come up when you click buttons, you will have to go through your code and change that too. If the language equals English, then give them this message box. Otherwise, give them that message box.
This is the hard part, changing all these captions. That is what my goal was to show you today.
Obviously, there is more to it. What I just showed you is actually the easier way to set it up, but it is the harder way to maintain it long term, because every time you add a field, every time you want to make a change, every time you want to add another language, you have to go in and modify all the code in all your forms.
Wouldn't it be easier if you could have that stuff stored in tables? If you could have an English set of data, a French set of data, and you did not have to go in and modify all your forms and all your VBA code every time you wanted to make a change? We could do that with what I call an external strings manager. It is basically going to be a set of tables where you can put translations in. You can have your users change the translations too.
Let's say you have Joe in shipping who knows Spanish. You could sit Joe down and have him type in all the Spanish translations for you and he does not have to go into your VBA code.
In the extended cut for the members, I am going to show you how to set that up. We are going to use the member database. First of all, we will set up a language table, so you have your different languages in here. We will set up an English phrase table, so this is all the phrases in English. So, phrase 3 is "Main Menu." We will set up a translation table, so for each phrase and for each language, you can have a translation.
For English, phrase 3, there are the two versions of "Main Menu." One language ID is English, here you have French, here you have Spanish. I did not do any Klingon. Sorry, guys.
One thing I have noticed is that so many French phrases and Spanish phrases are nearly identical, like "Customer List." Look at that. But anyway, you can put this all together and then you end up with basically the same thing, but it is something you can change and modify without having to go into your VBA code. Once you build it, that is it. Your users can actually change the translation information using just the tables.
Furthermore, we are not going to have to go through and write code for each field. I am going to show you how to tag each one of these. So you could say, okay, this is field 1, this is field 2, this is field 3, and go get a translation. A whole lot better code than what we just wrote.
The code that I just showed you is fine. It works great. It is very basic. The code we are going to do in the extended cut for the members is the creme de la creme of code. We are going to loop through all the objects on the form - fields. We are going to find every different one with the Tag property. All kinds of cool stuff. Unlimited languages. We are going to have a lookup table. Lots of stuff.
Join - hit that blue join button, become a member, Silver Membership is like six bucks a month. That is not expensive and you get access to all of the extended code videos, not just this one - all of them. Gold members can download these databases too, so you get all the code and you get the code vault. You get free classes every month.
So what are you waiting for? Join. Get in the hot tub - the water is warm.
I hope you learned something. This has been your TechHelp video for today. My name is Richard Rost, and I will see you next time.Quiz Q1. What is the main goal of the video tutorial?
A. To explain how to create a user logon system in Access
B. To set up a multilingual database where form labels and buttons can change languages
C. To teach how to make relational combo boxes
D. To download and use the TechHelp free template
Q2. Which control is used on the main menu to let users pick their desired language?
A. A command button
B. An option group
C. A value list combo box
D. A text box
Q3. What property is changed to switch the language of form labels and buttons?
A. ControlSource property
B. Tag property
C. Caption property
D. Value property
Q4. What event is used to trigger the language switch when the user selects a new language?
A. On Click event
B. On Load event
C. After Update event
D. On Open event
Q5. Why does Richard recommend giving good names to labels and buttons?
A. It is required for Access to work properly
B. It helps identify and reference them in VBA code
C. It improves the database's speed
D. It makes forms more secure
Q6. If you wanted the default language to be English when the database starts, what should you set?
A. The Visible property to English
B. The Default Value property of the combo box to English
C. The Caption property to English
D. The Row Source property to English
Q7. How do you prevent users from editing or adding to the list of languages in the combo box?
A. Set Limit to List to No and Allow Value List Edits to Yes
B. Set Limit to List to Yes and Allow Value List Edits to No
C. Hide the combo box
D. Lock the form
Q8. On the Customer form, when should the code to change label captions run?
A. On every button click
B. In the combo box's After Update event
C. In the form's On Load or On Open event
D. Only when closing the form
Q9. For maintaining translations in a more scalable way, what advanced technique does Richard mention for his members?
A. Storing translations in VBA code
B. Using hardcoded If Then blocks for every form
C. Using external tables to manage translations and looping through form controls
D. Creating separate forms for each language
Q10. What is a potential challenge when switching between languages like English and French on form controls?
A. French phrases are always shorter than English
B. Access cannot change control captions dynamically
C. Labels and buttons may need to be resized to fit longer translations
D. Changing the language will delete existing data
Q11. What is recommended if you want to store customer data, such as notes, in multiple languages?
A. Use a single field for notes and manually translate
B. Use multiple fields (e.g., EnglishNotes, FrenchNotes) for each language
C. Use only English for consistency
D. Prevent entry of non-English data
Q12. Why is the approach given in the standard video considered harder to maintain as your database grows?
A. It relies on too many combo boxes
B. Every new language or caption change requires code updates on every form
C. It does not support more than two languages
D. It cannot be used on main menu forms
Q13. What does the Tag property allow you to do in the more advanced multilingual version?
A. Mark which controls should be translated, enabling automated translation lookup
B. Store default values for controls
C. Lock specific fields from being edited
D. Set the font style of labels
Q14. What is Richard's advice regarding database security and user logons in relation to language selection?
A. You cannot use language selection with user logons
B. You can tie a user's language preference to their user table setting for automatic switching
C. Language selection is only available to administrators
D. Security settings override language options
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-C; 10-C; 11-B; 12-B; 13-A; 14-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on creating a multilingual Microsoft Access database. I will show you how to configure your database so that elements like buttons and labels can automatically switch between English and another language, such as French.
This question came from a user in Montreal, who wanted to know if it is possible to have form labels and buttons change language depending on the preference of the user, since not everyone in their office is comfortable speaking English. This is a common issue in many locations, including areas near the Canadian border, Texas, and Florida, where multiple languages are frequently used. The method I am sharing today is useful for any environment with a multilingual workforce.
Before starting, there are a few things you should be familiar with. You will need to know how to create a value list combo box that lists available languages, such as English, French, and Spanish. If you are unfamiliar with value list combo boxes, I recommend checking out my dedicated tutorial on that topic.
You will also need to understand how to retrieve values from open forms using standard syntax like forms!MainMenuF!FieldName. If that is new to you, I have another video that explains how to access values from different forms and controls.
A basic understanding of VBA is necessary for this process, since we will need to write a little bit of code to change properties like captions for buttons and labels on the fly. If you are new to programming with VBA, I suggest watching my introductory video, which runs about 20 minutes and covers all the foundation you need.
You should know how the After Update event works, which is triggered when a user changes the value in something like a combo box. If you need an explanation of After Update events, you can find a video on my site explaining that as well.
Finally, familiarity with If Then statements will help, since we will use conditional logic to set different languages. If you need more information on this, I have another tutorial you can refer to.
Once you are comfortable with those concepts, you are ready to begin. I start off with a free Access template available from my website. First, add a combo box to your main menu so users can choose their language preference when logging in. If you already have a user logon system, you could also store each user's language preference in their record, and load the correct language automatically upon login. This method is explained in my security seminar, where I cover user permissions and access control in more depth.
To set up the language combo box, add it to your main form, use the value list wizard to enter your available languages, and label it as "Language". Adjust its appearance and position for a professional look, and ensure the control is named clearly, such as "Language". With value lists, you can treat these controls as text boxes since the displayed value and the stored value are identical.
For better control, set properties like Limit to List to Yes, so users cannot enter values not on your list, and disable Allow Value List Edits to prevent them from editing your language list. Set English as the default value to match the usual starting language for most users.
Next, make sure that any buttons and labels you want to change based on language have appropriate and descriptive names. While many developers assign systematic names to buttons, labels are sometimes assigned generic names like Label1 or Label23. For this project, give meaningful names to all controls whose captions you intend to change, such as "MainMenuLabel" or "TodayIsLabel". This will make your code easier to read and maintain and is a good programming habit for future projects.
For the main menu form, the idea is to use the After Update event of the language combo box to determine which language is selected. When the value changes, the event code should update the Caption property of each button and label to the correct language. If you plan to support several languages, multiple ElseIf statements can handle options like Spanish or German. For this example, I use English and French.
Since I do not personally speak French, I generated translations using Google Translate. While these may not be perfect, they serve as placeholders, and you can update them with your own translations as needed.
Once coded, this event fires when the user selects a different language, updating all necessary captions right away. It is important to make sure that your controls, especially buttons, are wide enough to accommodate longer phrases, as translations often use more space in other languages.
The same process applies to other forms, like a Customer form. Here, instead of a combo box, the code to update captions runs in the form's On Load (or On Open) event. In this approach, when a form loads, it checks the current language selection from the main menu form and updates its captions accordingly. Note that for this method to work, the main menu form must remain open in the background, as the setting is referenced from there.
You can expand this system to update anything from form and report captions to messages and prompts. For the data itself, handling multilingual content would require additional planning. You might store translations in separate fields or translate text using an API such as Google Translate, but that adds complexity and is outside the scope of today's lesson.
Message boxes triggered by code should also be translated in the same way, using conditional statements to display the correct language dynamically.
This approach is quick to set up and effective for smaller projects, but as your application grows, it becomes harder to maintain. Each time you add new fields, controls, or languages, you will need to update your VBA code throughout all your forms. There is a better long-term solution: storing your translations in tables. This way, you set up a language table, a phrase list in the source language, and a translation table mapping each phrase to its translation in every supported language. Users can enter and update translations directly in the database, without opening or editing VBA code.
In the Extended Cut for members, I provide a step-by-step guide on building this table-driven approach. We will use member-only databases to design language, phrase, and translation tables, and we will show you how to connect your controls using the Tag property and more advanced techniques. This method supports unlimited languages and users can update translation data themselves without touching your forms or code. It is much easier to maintain and expand over time.
With a Silver Membership, you get access to all extended cut videos, not just this one. Gold Members can also download example databases and source code. If you are interested in taking your Access solutions further, joining as a member is a great value.
I hope you learned something from today's TechHelp video. For a complete, step-by-step video tutorial on everything we covered, visit my website using the link below.
Live long and prosper, my friends.Topic List Creating a value list combo box for language selection
Setting the Default Value for the combo box
Configuring Limit to List and Allow Value List Edits
Renaming controls and labels for clarity
Changing control captions based on selected language
Using the After Update event to trigger language switching
VBA code to update captions dynamically
Handling label sizing for different language lengths
Updating captions on button and label controls
Using the On Load event to update form captions
Retrieving values from another open form
Applying multilingual settings to multiple forms
Limitations of hardcoding translations in VBA
Brief mention of improving maintainability with translation tables (details in extended cut) |
