Improving the Dynamic Tab Control - MS Access

You saw how to design a dynamic Tab control form that can load and unload pages by using an array of Types, where each item in the array corresponds to a form that is loaded into a subform control. The type structure for that is as follows:

As an alternative to using a Type, you will define these pages as objects with properties that correspond to each part of the Type structure, and then you will build a collection to hold these objects, which replaces the array that held the types.

We need the following properties for our object:

  • PageName
  • SubFormPageName
  • RelatedPageName
  • CanBeUnloaded

You might have noticed that we have renamed the CanBeLoaded property in the preceding list to CanBe Unloaded. This is because an object -oriented perspective helps you to think in terms of how an object’s state can be changed, so this is a more appropriate term to use. With the object’s basic properties determined, you can now proceed to create the object class.

Creating a Class Module

To begin, in the Project pane, you create a new class module, as shown in Figure.

Use the Project pane to create a new class module.

Use the Project pane to create a new class module.

With this file created, you then save it using an appropriate class name; for this example, use clsTabPage. Because you are now working in a class module, you do not need to explicitly define that you are creating a class (as you would need to do in Microsoft .NET). Next, you define the object’s internal variables at the top of the class module code, as illustrated in the following:

Option Compare Database

Option Explicit

' These could be declared as either Dim or Private ' as within a class their scope is restricted

Note that these variables include the prefix “p_” to indicate that they are private variables to each class object (other popular prefixes include “m” or “m_”). The next step is to provide the user with a way of reading and writing these variable values.

The Let and Get Object Properties

After you define the object’s internal variables or attributes for your class, you need to create a mechanism to read or write these values. To do this, you define properties. On the Insert menu, click Procedure to open the Add Procedure dialog box, as shown in Figure.

Use the Add Procedure dialog box to create a new private or public property.

Use the Add Procedure dialog box to create a new private or public property.

Ensure that you are not clicked inside any other property when you insert a new property; otherwise, it will fail to add the property correctly to the class. The code that is created needs appropriate data types to be specified for the return type of the property and the parameter type passed to the property.

As shown in the code that follows, you use the Get statement to read an object property from the internal private variable, and the Let statemen to assign a value to the internal private variable. An object can have a number of internal variables, but you might only need to make a few of these available to the user. The idea is to keep the object’s external interface very simple, exposing only the minimum number of essential features that a user will need. It is up to you to decide for which properties you want both a Let and Get, depending on whether the property is to be read-only (Get but no Let) or write-only (Let but no Get):

Creating an Object with New and Set

To test your new class, you create a module (not a class module) to verify that you can create an object. If you insert a breakpoint and trace through the code execution, you will learn a great deal, as you can trace through the codes execution into the class module code.

You can define the object variable and then later create an object with the New keyword, or as is also shown demonstrated in the following code, with the aTab2 object, you can both define and create the object at the same time. It is largely a matter of personal preference as to which method you choose to use.

Once you have finished with the object, set the object variable to Nothing; this destroys the object. The object would be destroyed anyhow when the code stops execution, but explicitly tidying up your objects is good practice and becomes more important when you work with more complex objects:

Initialization and Termination Events

When you are in a class module, you can select Class from the upper-left drop-down menu, which normally shows (General). Select Initialize or Terminate from the dropdown list that appears, and then generate the following procedures (in this example the type name ObjectType is not a real type but could for example be replaced with a real object type such as a DAO.RecordSet object):

Because class objects can contain other class objects or built-in class objects such as a Recordset, you might need to use the New keyword in Initialize to create an object that is assigned to a private variable, and then set the objects to Nothing to close the objects in the Terminate procedure. Externally, when your class object is created, the Initialize procedure is executed, and when it is set to Nothing or the variable goes out of scope, the Terminate procedure is executed.

Collection of Objects

A VBA collection is a set of objects that you can use in a similar manner as the built-in collections, such as the Forms collection that you worked with in earlier chapters. The example that follows defines a collection that is used to hold our Tab page objects:

Notice how the aTab variable is used several times to create objects, and how setting it to Nothing does not destroy the object. This is because once you have created an object, you add it to the collection, which is then responsible for managing the object (when the collection is set to Nothing, it will destroy the objects it contains.

When you add an object to a collection, you must also specify a collection key value (which must be unique). Doing this means that rather than referring to a collection object as TabPages, you can use the key and refer to this as Tab Pages(“Product List”). The Collection object’s Add method also allows you to specify an optional Before or After argument for positioning an object relative to other objects in the collection. The collections first element is 1 and not 0 (which is what the built-in Access collections use).

Be aware that when you refer to an object by using TabPages.PageName, you cannot take advantage of IntelliSense assistance. This is because this type of collection can hold different types of objects, so the environment cannot know exactly which properties would apply to an object.

VBA collection classes

The built-in VBA collection classes that you have been working with are different from an Access collection. The first difference is that the Access collections, such as TableDefs, can only hold one type of object; a VBA collection can hold different types of objects (this explains why the IntelliSense is limited). The second difference is that VBA collection classes are 1-based, whereas the Access collections are 0-based. In the next section, you will be creating your own collection classes that wrap around the VBA collection class. These collections will start to look more like an Access collection.

Once you have added an object to a collection and specified the key value, you will find that you cannot subsequently display the key value—it is hidden. If your procedures need to be able to refer to the key, you might find it useful to add your own property to the object class, which saves and holds the key value in each object. Looking in the class clsTabPage, you see the following (it is not essential to do this in the class):

Creating Collection Classes

A VBA Collection object supports a limited number of operations—Add, Count, and Remove. You will likely want to be able to add more operations to your collection. To do that, you need to define your own collection class, called clsTabPageCollection.

Defining a collection class follows the same steps as defining a normal class to create the class module. Your collection class will contain a VBA collection, so you define an internal variable called p_TabPages. As we previously described, classes can have two specially named methods for initializing and terminating the class. The simple clsTabPage didn’t need any special operations, but the new class needs to create a VBA collection, and then remove all the objects from the collection when it is terminated, as illustrated in the following code:

You also want to have the standard operations for counting, adding, and removing items from the class, so you need to add these methods to our collection (you also add an Item method, which is another standard feature of a class):

Once you start defining your own collection class, you will find that a number of the expected built-in collection class features no longer work. For example, you cannot use a For Each loop, or index the collection by using the friendly key name (you will see how to get around this). The following procedure can be used to test the class; the program lines that are commented out have been included to show what will not work in our collection class:

There are two techniques available to get around the problem of not being able to refer to the collection class by using the key names. The first technique involves adding an AllItems function to the collection class, and the second method involves exporting, editing, and reimporting the class.

Adding AllItems to a collection class

When you use the AllItems method, you need to add the following property to the class (you can give this property an alternative name):

In the sample testing file, modTabs_clsTabPageCollection2, you can see how to use this feature. The important code is as follows:

This is a satisfactory solution as long as you are prepared to insert the .AllItems reference when using the collection with the object’s key.

Exporting and Re-importing the Class

The reason that you cannot refer to collections by using standard syntax is because VBA classes do not allow special attributes to be set on a class, and these are required to support standard syntax. If you right-click the collection class module in the project window, export it to a text file, and then open the text file in notepad, you will see the following header information in the class:

These attributes are not exposed in the VBA environment. There is a special attribute value, which when set to 0, sets the member as the default member for the object. You want the Item method to be the default member and you need to change the method adding the following attribute definition (this will enable references such as TabPages("ProductList") to work). Also, to support enumeration in a For Each loop, you need to add the NewEnum method, as shown in the following:

After saving these changes, import the class back into your project, as shown in Figure.

Re-importing a class back into Access.

Re-importing a class back into Access.

If you look in the VBA Editor, you will not be able to see the new attribute you just added in the Item method because it remains hidden. This then means that the following references will work (note that in the sample database BuildingClassesAfterExportImport.accdb, the following code will work, because we have performed this rather complex operation; in the sample database BuildingClasses.accdb, this code has been commented out because it will not work):

This process needs to be repeated for each collection class in your project.

Using Classes with the Dynamic Tab

You are now able to modify the code in the frmTabsDynamic form to make use of your new classes. At the top of the module, where you had defined an array of types, declare your collection class as shown here:

In the following code, in the LoadTabs procedure, you create and load your class objects into the collection:

There are some other minor references in the code that used the array of types that now need to be changed to use the new collection and objects.

Simplifying the Application with Classes

In the preceding sections, you have been able to change your dynamic tab to use classes, but it has not as yet resulted in any simplification of the applications code. In fact, you now have more code to maintain than when you started. But you now have a framework in which you can start to work that will lead to simplification and improved maintenance of your code.

In examining the frmTabsDynamic form, you can see that it has a general routine LoadTabs that involves reading information and placing the information into your collection. This operation could be placed inside the collection. So we can start to enhance our collection (clsTabPageCollection2) by adding the data loading function. But the process of loading the information also involves setting values in controls on the form. This means you also want to allow the collection to reference the controls on the form.

To begin, add new private members to the class:

You can then move the appropriate routines programmed into the form into the collection class. The result of this is an impressive reduction in the code on the form, which now shrinks to the following (see frmTabsDynamic2):

Although the total amount of code remains unchanged, much of the code has moved out of the form and into the classes. There are a couple of advantages to creating classes to perform these operations:

  • The code on the form is significantly simplified; it will be easy to add it to other forms or in other applications.
  • The new classes are easy and intuitive to work with, so using them in the future should improve your applications, and you can add more features to these classes. Some might argue that rather than using classes, which involves constructing a framework, you could more simply have built a re-useable library. This line of argument nearly always holds; thus, the decision to use classes becomes a question of whether it seems more intuitive and natural than using a traditional code module.

Classes and associated terminology

Another term for creating an object is instantiating the class object. This means using the New keyword to create the class object. The term Encapsulation is often used to convey the idea of tucking away all the functionality inside the class, such that the class only exposes as small a public interface as required to fulfill its purpose. With a class, you are wrapping up all the messy code and placing that inside a box so that you don’t need to deal with it on a regular basis.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics