Beginning Access 2002 VBA (Programmer to Programmer)

2017-07-07 02:10:07

Add-Ins are another form of library, and although they contain objects such as code, tables, and forms, they are generally complete tools, rather than collections of code. Library databases are generally for the developer to use, but Add-Ins are usually for the end- user . They generally provide features, which while being useful to some users, are not really required as a central part of Access. By building these features into an Add-In you get the benefit of extra functionality combined with the ability to load it at will.

There are different types of Add-Ins, and which you use depends upon the task you need to accomplish. If you are creating objects, the Add-In will probably be a wizard or a builder, such as the controls wizards, form creation wizard, or the color builder. If you need to do more than this, or something that is completely separate from other objects, then creating an Add-In from scratch is probably the way to go. From here on, we're going to take an in-depth look at an example Add-In. We'll see how we can put it together and then use it in any database we want.

The Language Converter Add-In

Let's imagine that you've just spent six months building your new application, and it's finally finished. The users have done their own testing and are really happy with it, and so is your manager. In fact, with a surprising amount of common sense, your manager has agreed that this application will be used in all of the company's offices around the world. "Great," you think, "at last, recognition for all of that hard work." So you start writing up installation instructions, only to be told that the application has to work in the native language of the company office. Oh boy. How do you manage that? All of the forms and reports will have to be translated, and your foreign language ability is well, shall we say, slightly lacking. Also, how do you manage the various copies of the application? Will you have to keep a copy for each language? But then what happens if you need to make a change? It's not looking good, is it?

Fear not, because this can all be done automatically (apart from the translation, of course). We can write an Add-In that allows the users to add new languages, edit the language details, and change all of the application's details. But how?

Remember how we looped through the controls on a form and changed the Font property? Well, the Caption is just another property, so this can be changed too. Here's how it's going to work:

We create a language table in the database, with the following columns : FormName , ControlName , ControlType , DateUpdated . These are the basic columns that hold the details of each control on each form.

Then, for each language, we add another column.

We loop through all of the forms and controls, and add a row into our table or each control that has a Caption property, setting the language field to the actual caption.

At this stage we have a table that looks something like this:

We can now add text for the other language columns. To change the language, we can open the form in design mode, loop through the controls, and set the Caption property of the control to the value in the table.

This may sound rather complex, but it's quite easy. Let's give it a go.

If you don't feel like typing all of these procedures in, then have a look in the Lang.mda database on the CD they are all in there.

Try It OutThe Language Converter Add-In

Create a new database called LangXP.mda . Notice that this is an mda file and not an mdb . There is no physical difference between an mdb and an mda file, but Add-Ins are generally given the mda suffix to differentiate them from standard Access databases.

While we are on the subject of extensions, it is worth knowing that Access uses the extensions MDA and MDE to apply a filter to its Find dialog when setting references to files in the Addin manager and other places within Access. Generally speaking, addins and libraries both use MDA for the uncompiled version, and MDE for the compiled version. However, you can rename any file from MDB to MDA or MDE, MDE to MDB, and so on. Be careful, however. Just because you rename a file to MDE, it doesn't make it one. This is a case where the extension is supposed to mean something specific, that is, that it is compiled. It is also useful to know that developers have been known to rename their MDB databases (particularly a data "back end") to some custom extension like ROC or BIZ. This has the effect of "hiding" the fact that it is an Access database from prying eyes. However, it can be opened by Access just like any other database using Open with from the right-click menu of Explorer.

Back to the subject at hand, you can make sure you create the correct type by selecting All Files (*.*) in the Save as type field on the new database dialog. If you don't do this, you'll get a database called LangXP.mda.mdb .

Create a new table with the following fields:

Field Name	Type	Length
FormName	Text	30
ControlName	Text	30
ControlType	Text	20
DateUpdated	Date/Time
English	Text	255
French	Text	255

Set the primary key to be both FormName and ControlName . You can do this by highlighting these two fields and pressing the Primary Key button:

Once set, the fields show the key symbol against them:

Set the Allow Zero Length property for the English and French columns to Yes :

Save this table as tblLanguage , and close it.

Create a new module, and from the Tools menu pick References . Clear the box against Microsoft ActiveX Data Objects 2.1 Library , scroll down, and tick the box against Microsoft DAO 3.6 Object Library . Click OK to close the References dialog.

In the empty module, add the following constant:

Private Const wcs_LANGUAGE_TABLE As String = "tblLanguage"

Add the following procedure, which will loop through all of the forms:

Public Sub LangEnumerateForms(bolExtract As Boolean, strLang As String) Dim db As Database Dim recLang As Recordset Dim objAO As AccessObject Dim objCP As Object ' open the database and language recordset Set db = CurrentDb() Set recLang = db.OpenRecordset (wcs_LANGUAGE_TABLE) recLang.Index = "PrimaryKey" ' enumerate the forms Set objCP = Application.CurrentProject If bolExtract Then For Each objAO In objCP.AllForms LangExtractControls recLang, strLang, objAO.Name Next objAO Else For Each objAO In objCP.AllForms LangSetControls recLang, strLang, objAO.Name Next objAO End If ' close up recLang.Close End Sub

Now another procedure, this time to loop through the controls on a form, extracting the caption into our language table:

Private Sub LangExtractControls(recLang As Recordset, strLang As String, strFormName As String) Dim frmF As Form Dim ctlC As Control Dim strControlName As String Dim datNow As Date Dim intControlType As Integer ' open the form, hidden, in design view DoCmd.OpenForm strFormName, acDesign, , , , acHidden datNow = Now() ' add the form caption Set frmF = Forms(strFormName) With recLang .Seek "=", strFormName, strFormName ' Add or update the form in the language table If .NoMatch Then .AddNew Else .Edit End If ' set the details !FormName = strFormName !ControlName = strFormName !ControlType = "Form" !DateUpdated = datNow .Fields(strLang) = frmF.Caption .Update ' now loop through the controls For Each ctlC In frmF.Controls ' we are only interested in the controls ' with a Caption property intControlType = ctlC.ControlType If ControlHasCaption(intControlType) Then ' find the control in the language table strControlName = ctlC.Name .Seek "=", strFormName, strControlName ' Add or update the control in the language table If .NoMatch Then .AddNew Else .Edit End If ' set the details !FormName = strFormName ! ControlType = ControlTypeName(intControlType) !DateUpdated ControlName = strControlName ! = datNow .Fields(strLang) = ctlC.Caption .Update End If Next End With ' close the form and save it DoCmd.Close acForm, strFormName, acSaveYes End Sub

Now the opposite function, to loop through the controls setting the Caption property with the text in our languages table:

Private Sub LangSetControls(recLang As Recordset, _ strLang As String, strFormName As String) Dim frmF As Form Dim ctlC As Control Dim strControlName As String Dim intControlType As Integer ' open the form, hidden, in design view DoCmd.OpenForm strFormName, acDesign, , , , acHidden ' add the form caption Set frmF = Forms(strFormName) With recLang .Seek "=", strFormName, strFormName ' Add or update the form in the language table If .NoMatch Or IsNull(.Fields(strLang)) Then frmF.Caption = "" Else frmF.Caption = .Fields(strLang) End If ' now loop through the controls For Each ctlC In frmF.Controls ' we are only interested in the controls ' with a Caption property intControlType = ctlC.ControlType If ControlHasCaption(intControlType) = True Then ' find the control in the language table strControlName = ctlC.Name .Seek "=", strFormName, strControlName ' Add or update the control in the language table If .NoMatch Or IsNull(.Fields(strLang)) Then ctlC.Caption = "" Else ctlC.Caption = .Fields(strLang) End If End If Next End With ' close the form and save it DoCmd.Close acForm, strFormName, acSaveYes End Sub

Now a function to determine if the control has a Caption property or not:

Private Function ControlHasCaption(intCtlType As Integer) As Boolean Select Case intCtlType Case acCommandButton, acLabel, acToggleButton ControlHasCaption = True Case Else ControlHasCaption = False End Select End Function

Finally, a function to return the type name of controls with a Caption property.

Private Function ControlTypeName(intCtlType As Integer) As String Select Case intCtlType Case acLabel ControlTypeName = "Label" Case acCommandButton ControlTypeName = "Command button" Case acToggleButton ControlTypeName = "Toggle button" End Select End Function

At this stage, the Add-In is functionally complete, so compile the code and make sure you save it call it Language Handling . There are some things we need to do, though, before we can use it as an Add-In, so let's give it a test before turning it loose on another database.

Create a new form in this database, adding the following controls:
- A textbox
- A listbox
- A combo box
- A label
- A command button
- A toggle button
It doesn't matter what's in these controls. I made mine look like this:

Save the form as Form1 . Then switch back to the VBA IDE, and from the Immediate window run your new code, by typing:

LangEnumerateForms True, "English"

The code will run quite quickly, and when it's done, switch back to Access and open the languages table:

Add some values into the French column. If your language ability is like mine (I once got 7% in a French exam!) then you can do something like this:

And please . No feedback from linguistic experts (or French people, come to think of it). This is just for testing. If you can speak French (and as you can see, I can't!) you might like to add some proper translations.

Close the language table and switch back to the Immediate window. Now run the language converter in its other mode setting the language for the forms:

LangEnumerateForms False, "French"

When this has run, switch back to Access and view the form:

There you have it easy conversion. You can add languages to the language table, and all you have to do is add a new language column, then add the new details in the new language column. The great beauty of this method is that you can create multi-lingual applications even if you can't speak the language the application needs translating into. All you need is someone who can speak the language to add the correct translations.

Let's have a quick look at how this works before we convert it into an Add-In.

How It Works

Although it looks a little complex, it's actually quite simple. All it does is this:

Extract Language Mode extracting the existing captions for the form and controls Pull out the caption for the form, and store it in the appropriate column in the languages table. Set the other details for the form (control name, type, and modification date). If no previous record exists for the form, a new one is created. Similarly, loop through all the controls on the form, checking for any that have captions (by calling another function). A record is created (or edited) for each control that has a caption. Set the details as above (calling a function to determine the control type).

Set Language Mode changing the language of the captions on the form and controls Change the caption of the form to that specified in the form's record, in the relevant language column.Loop through the controls, checking for any that have captions (as above). For each of these, find the right control in the language table, and change its caption as specified in the correct language column.

Some of the techniques we've used have been explained before, so they shouldn't need too much detail.

Let's start with the LangEnumerateForms procedure. Depending on the argument passed to this procedure, we're either in extract language mode (mode 1) or set language mode (mode 2). The first thing we do here is to open both the database and the language table. Then we set the current index to be the primary index that's because we will be using the Seek method later on.

Set db = CurrentDb() Set recLang = db.OpenRecordset("tblLanguage") recLang.Index = "PrimaryKey"

Now we need to loop through all of the forms using the AllForms collection, calling the correct procedure for each one, depending on our mode. If the bolExtract argument is True , we're in mode 1, so we call LangExtractControls; but if bolExtract is False , then we're in mode 2, so we call LangSetControls .

Set objCP = Application.CurrentProject If bolExtract Then For Each objAO In objCP.AllForms LangExtractControls recLang, strLang, objAO.Name Next objAO Else For Each objAO In objCP.AllForms LangSetControls recLang, strLang, objAO.Name Next objAO End If ' close up recLang.Close

Mode 1

Let's now look at LangExtractControls . Opening the form in Design view, and being exposed to the user's view, is very distracting, if not downright disconcerting, so we open the form, hidden, in Design view. We also set a variable to hold the current date and time this will allow us to see when the form details were last extracted.

DoCmd.OpenForm strFormName, acDesign, , , , acHidden datNow = Now()

Next we set a form variable to point to the newly opened form. We then use the With statement on the language recordset. Both of these are for speed and clarity, as they are not only quicker, but they also make the code easier to read. Remember that the With statement allows us to just use the property or method name, in this case of the recordset, without repeating the recordset name.

Set frmF = Forms(strFormName) With recLang

Now we look for the form name in the languages table. Forms have a Caption property, so we want to extract this:

.Seek "=", strFormName, strFormName

The Seek method sets the NoMatch property to True if the item sought wasn't found. So, if we don't find an existing record, we'll want to add a new one. Otherwise , we just want to edit the existing record.

If .NoMatch Then .AddNew Else .Edit End If

We are now on the correct record (either a new one or an existing one), so we want to set the details. We set the FormName and ControlName to the name of the form (remember this is the form's Caption property), the ControlType, and the DateUpdated .

!FormName = strFormName !ControlName = strFormName !ControlType = "Form" !DateUpdated = datNow

We then need to set the value for the correct language, so we use the Fields collection of the recordset, using strLang as the index to this collection strLang contains the name of the language, and was passed into this procedure as an argument.

.Fields(strLang) = frmF.Caption

Finally, for the form, we update the record.

.Update

At this stage all we have done is added (or updated) a single record in the languages table (the one for the form), so we now need to loop through the controls:

For Each ctlC In frmF.Controls

We are only interested in controls that have a caption, so we call the ControlHasCaption function this returns True if the control has a Caption property. We'll look at this function in a little while.

intControlType = ctlC.ControlType If ControlHasCaption(intControlType) = True Then

Now, in a similar way to the form, we use Seek to find the record in the languages table for this control, and we either add a new record, or update an existing record, depending upon whether it was found or not:

strControlName = ctlC.Name .Seek "=", strFormName, strControlName If .NoMatch Then .AddNew Else .Edit End If

Once on the correct record, the details need updating. The FormName and ControlName are pretty obvious, as is the date. For the ControlType we call a separate function, ControlTypeName , to identify the type of control again, we'll look at that function in a while.

!FormName = strFormName !ControlName = strControlName !ControlType = ControlTypeName(intControlType) !DateUpdated = datNow

Then, as with the form, we set the field for the correct language:

.Fields(strLang) = ctlC.Caption

Then we update this record, and move on to the next control.

.Update End If Next End With

Finally, we close the form, saving the changes.

DoCmd.Close acForm, strFormName, acSaveYes

It's really only a few steps. Let's see how the opposite function, LangSetControls , works.

Mode 2

As with the previous function, we open the form in Design mode.

DoCmd.OpenForm strFormName, acDesign, , , , acHidden

The next thing to do is get the text from the language table and set the Caption property of the form. As before, we use Seek to find the correct record.

Set frmF = Forms(strFormName) With recLang .Seek "=", strFormName, strFormName

If no record was found ( NoMatch is True ), or if the entry for this language is empty, then we set the Caption to an empty string, otherwise we set the Caption to the value of the language field.

If .NoMatch Or IsNull(.Fields(strLang)) Then frmF.Caption = "" Else frmF.Caption = .Fields(strLang) End If

Now we need to loop through the controls, again only checking the ones that have a caption.

For Each ctlC In frmF.Controls intControlType = ctlC.ControlType If ControlHasCaption(intControlType) = True Then

Like the form, we use Seek to find the record, and set the caption to the value of the field in the language table, or to a blank string if the control wasn't found in the language table:

strControlName = ctlC.Name .Seek "=", strFormName, strControlName If .NoMatch Or IsNull(.Fields(strLang)) Then ctlC.Caption = "" Else ctlC.Caption = .Fields(strLang) End If End If Next End With

Finally, we close and save the form.

DoCmd.Close acForm, strFormName, acSaveYes End Sub

The Supporting Functions

The only things left now are the two supporting functions. The first just identifies those controls that have a Caption property. Note that we define these ourselves .

Private Function ControlHasCaption(intCtlType As Integer) As Boolean Select Case intCtlType Case acCommandButton, acLabel, acToggleButton ControlHasCaption = True Case Else ControlHasCaption = False End Select End Function

The second returns a string for the control type. This isn't really required, but it makes it easier to see in the language table what type a control is.

Private Function ControlTypeName(intCtlType As Integer) Select Case intCtlType Case acLabel ControlTypeName = "Label" Case acCommandButton ControlTypeName = "Command button" Case acToggleButton ControlTypeName = "Toggle button" End Select End Function

That's all there is to this basic Add-In. There are still two things to do, however. The first is to give our Add-In a form of its own, so that other people can use it without resorting to the code windows . The second is to add some Access bits and pieces that will allow this database to be used as an Add-In.

Creating the Add-In

Delete the test form and any records in the languages table.

Create a new form, making it look like this:

Name the listbox lstLang , set its Row Source Type to Value List and its Row Source to English;French :

Name the three command buttons cmdExtract , cmdSet , and cmdExit , in order of top to bottom.

In the form Design view, press the Code button to create a code module for this form, and switch to the VBE.

In the Click event for cmdExit , add the following line of code:

DoCmd.Close

In the Click event for cmdExtract , add the following code:

If lstLang.ListIndex = -1 Then MsgBox "Please select a language" Else LangEnumerateForms True, lstLang End If

In the Click event for cmdSet , add the following code:

If lstLang.ListIndex = -1 Then MsgBox "Please select a language" Else LangEnumerateForms False, lstLang End If

Save the form as frmLang .

That's all we need for the user form. It allows the user to pick a language and then either extract or set the caption details. Now we need a public function to open this form, so create a new module, and add the following code:

Public Function wrox_Lang() DoCmd.OpenForm "frmLang" End Function

Save this module as Language Entry Point .

Finally, let's set the database properties, so that the Add-In shows up nicely in the Add-In Manager. So, back in Access, select Database Properties from the File menu, and set them like this:

You can change the author and company if you like.

Now on to the final piece of the Add-In.

Try It OutCreating the USysRegInfo Table

To use a database as an Add-In you need to create a special table, called USysRegInfo . This is a system table, so is not normally seen, and it needs to contain four columns and three rows. Rather than create this table yourself, it's a lot easier to just import it from the supplied language converter, Lang.mda , from the CD. You don't really need to know much about this table, except it's this table that Access uses to store some of the details for the Add-In.

If you are interested in knowing more, do a web search on USysRegInfo and you should find an article on the Microsoft site called Creating the USysRegInfo Table for a Microsoft Access 2000 Add-In with lots of interesting details.

In Access, from the Tools menu, select Options .

From the View tab, make sure the System Objects checkbox is selected. This allows us to see system objects, which are normally invisible.

From the File menu, select Get External Data , and then Import .

From the File dialog, find and select Lang.mda on the CD, and press the Import button.

From the Import Objects dialog, on the Tables tab, select USysRegInfo , and press the OK button.

Open up the newly imported table:

This table must contain these details for an Add-In to work, as the Add-In Manager uses these values to update the registry. The Subkey column shows the name of the Add-In in this case we've called it Language Converter the ampersand identifies the hot key, so the C will be underlined .

You can ignore the Type and ValNames columns, as long as the values you enter are the same as shown.

The Value column, for the bottom two rows, contains the important details. The first, for the Expression , identifies the entry point of the Add-In. This is the function that Access will call when the Add-In is run. It must be a Public Function . The second Value , for the Library , identifies the directory and database name of the Add-In. The name is Lang.mda , and ACCDIR\ means that the Add-In will be copied into the Access Add-Ins directory. We'll see this happening a little later.

The Language Converter Add-In is now complete, so let's give it a go.

Try It OutUsing the Language Converter

Close the Add-In, and open up a database. Any one with forms will do. You might like to copy a database you've already got; that way your changes won't be permanent.

From the Tools menu, select Add-Ins , and then Add-In Manager . The list of installed Add-Ins will probably be blank, unless you've already used some.

Press the Add New button, and from the file dialog select the Lang.mda you've just created, and press the Open button.

The Add-In is now installed. Notice the details at the bottom of this dialog these come from the database properties that we set in the Add-In.

Press the Close button.

Before the Add-In can be used, you'll need a language table in this database, so from the File menu, select Get External Data , and then Import .

From the File dialog, find Lang.mda , and press the Open button.

From the Import Objects dialog, select tblLanguage from the Tables tab, and press the OK button. This imports the language table into the local database.

From the Tools menu, select Add-Ins , and then Mini Language Converter . The main screen pops up just as it did when testing.

Select English and press the Extract button. This will extract all of the captions from the forms.

Open the language table to have a look at what it's done.

Add some text to the French column, and then try setting the captions to French.

So, there you have it. Some code which is fairly simple, in a standalone database, which can be shipped to any office, in any country, and works with any database. Your boss will be very impressed, so go for that pay rise!

The Complete Language Converter Add-In

There are a number of small faults with the language converter as it stands:

You have to manually create, or import, the language table into any database in which you wish to use the converter. The table should really be created automatically for you.

To add new languages you have to edit the table. This should also be automatic, removing what could be a complex task for a user.

To edit the details you have to open the table manually. A nice form to allow editing would be better.

Although not major faults, these could be considered bad programming, since they give the user tasks to perform which could either be eliminated or smoothed. There are two ways around this problem:

As an exercise, add these facilities to the converter you've just created.

Use the Language.mda converter supplied on the CD. This is a fully completed version, which looks like this:

The code is only slightly different from the converter you've just created, and it has a few extra facilities. But you're well on the way to creating this yourself, so why not give it a try?

The Color Schemes Add-In

Another Add-In that's quite useful is the Color Schemes one. Do you like the way Windows allows you to select the appearance of various screen items? So do I, so I thought a similar facility for Access forms might be a good idea.

This uses a very similar technique to the language converter, looping through the controls on a form and setting the various properties. In fact, the above form is more complex than the code that applies the scheme.

We're not going to look at the code here, but you might like to browse through it. You can find it on the CD, called Schemes.mda .

Creating Add-Ins Summary

Although creating Add-Ins is quite simple, it's worth reiterating a few points.

You must have a USysRegInfo file in the Add-In. The Add-In Manager requires this to be able to load your Add-In. You can copy this table from any other Add-In and just modify the values.

The entry function pointed to in USysRegInfo should be a Public Function .

Make all of your procedures Private , unless you want them exposed to calling databases.

If your Add-In takes several steps, then display your forms as dialog boxes (set the WindowMode argument to acDialog ). This prevents the user from continuing until your form has finished.

Bound objects are bound to the data in the Add-In. So a form based on a table shows the records in the table in the Add-In. If you need to use bound forms you can link user tables into the Add-In database for the duration of processing. The Color Schemes Add-In does this.

If you are creating recordsets, then remember that CurrentDB() points to the user database, and CodeDB() points to the Add-In database. You can also use the new Access objects CurrentData and CurrentProject to point to the user database, and CodeData and CodeProject to point to the Add-In database.

Don't let your Add-In change the state of the user database. This doesn't apply to Add-Ins that change forms, but to Access options. If you need to change any global options, keep a copy of what they are so they can be reset when your Add-In finishes.

Create error-handling code. Your Add-In is meant to make the user's life easier, so don't dump them out to horrible error messages.

If you stick to these few basic rules, you should be OK.

Updating your Add-In

There's one think to watch during the development of Add-Ins, and that's the location of the Add-In that's running. Office XP is a very user-based application, so when you install an Add-In, it copies it into its own directory. Under Windows 98 this is:

\Windows\Application Data\Microsoft\Addins

Under Windows NT, this is:

\WinNT\Profiles\username\Application Data\Microsoft\AddIns\

Under Windows 2000/XP, this is:

\Documents and Settings\username\Application Data\Microsoft\AddIns\

This means that once installed, there are two copies of the Add-In. One in the original directory, and one in the AddIns directory, so any changes you do must be done in the right place.

There's no problem with leaving the Add-In installed and editing the mda file directly in the AddIns directory, but you must close down any instances of Access that have been using the Add-In. That's right close Access. Just closing the database doesn't free the lock. However, when you have finished making your changes, don't forget to update the "master" copy of the Add-In in your original development environment. Most of us forget that the copy being used is in our username path and ship out the copy in the development directory, so that one had better have all your changes.

These aren't major problems, but can be a bit of a pain. Just bear them in mind while you are developing your Add-Ins.