Access 97, 2003 and 2007 has support for standard class modules lets you greatly extend the functionality of standard controls. In this article, Jim shows you how to make Access unbound list and combo boxes behave more like those in Visual Basic by adding a few new methods and properties via a standard class module.
VISUAL Basic 4.0 added a new module type known as a class module. Class modules are distinct from standard modules in that they can be used as templates to create, or instantiate, objects. These objects can have their own data, and have their own functions, or methods. They allow for a style of programming known as Object-Oriented Programming, or OOP.Access 95 added limited support for class modules. The module code associated with each form or report is a class module. Using these class modules you could create public properties and methods for your forms. This allowed you to create multi-instance forms and reports for the first time. But you couldn’t create standalone classes that weren’t associated with a form or a report, as you could with VB 4.0.
Access 97 and upwards removes this limitation on class modules. You can now create classes which you can use in your application to create a variety of useful structures. You can create non-visual objects such as linked lists, or model real-world objects in your code.
You can also use class modules to hide complex procedures by creating an object “wrapper”that exposes a simplified interface to an object. This article discusses a way to use Access 97 classes to provide new functionality to Access’ standard list box and combo box controls, while simultaneously making your code easier to read and maintain.
Pictures from the sample opened in Access 2003
Access list and combo box limitations
Access’ bound combo and list boxes are both flexible and easy to use. Simply assign a query or table as the RowSource of the list box, and Access automatically fills the list based on the contents of the query.
Compared to VB, however, Access’ unbound list boxes are surprisingly limited. It’s more difficult than you would think to add an item to a list box programmatically. With VB you can append a value to the end of a list like this:
Me!lisURLs.AddItem "http://www.microsoft.com"
In Access you have two choices when working with unbound list boxes. The most flexible method is to create a complex list filling callback function, which Access itself calls to populate the list. This function must conform to a rigid format of argument types, and must support several specific actions, such as providing the number or rows and columns, initialization and termination, and so forth. For information on using list filling callback functions see the Access 97 help topic titled RowSourceType Property (User-Defined Function) or Ken Getz’s article in the October 1993 issue of Smart Access.
A simpler method is to use a RowSourceType of Value List. The value list is a text string of values delimited by a semicolon. For example, to add an item to a value list list box at runtime, you might use the following code:
Me!lisURLs.RowSource = Me!lisURLs.RowSource & _ ";" & Me!txtURLs
This example takes the current value of the RowSource property of the list box, appends a semicolon (;) character, and then appends the new value, which in this case is contained in a text box called txtURLs.
This is easier, but still limited. There’s no simple way to remove an item from the list, or to sort it. In order to modify the list you must completely replace it, being sure to keep all your semicolons in the right place.
Enhancing functionality with a class module
My first goal in creating the list box class module is to add new functionality to the standard Access list box. Modeling the interface after that of VB, which many Access programmers may be familiar with, I want to create the following methods: AddItem, RemoveItem, and Clear. These will be the supported “methods” of the class.
Because of the drawbacks of storing the list information in the RowSource property of the list box itself, the class should maintain its own list of values. In my example I use the Access collection object to store the values. This is the class’ instance data.
Finally, I want to create a wrapper around a standard list box, keeping all of its native functionality while adding new capabilities. I create a pointer to an actual list box on a form, and perform all work through that pointer. The class thus encapsulates a standard list box.
To create an Access 97 class module you can’t simply press the New button on the Modules tab of the database container. This creates a standard module. To create a class module you must explicitly use the menu option Insert, Class Module. By default, Access gives it the name Class1.
As with other Access objects, it’s important to think carefully about the name of your class module. Unlike standard modules, where the name of the module itself is relatively unimportant, you actually refer to the name of the class module when you create instances of objects based on that module. I’ll call the class “clsUnboundList” because its function is to provide a wrapper around unbound list and combo boxes.
The first thing the class needs is a place to store its instance data, or the actual list of items that will go into the list box or combo box. In the class module’s Declarations section I create a variable that holds the collection:
Private mcolItems As Collection
The variable is declared Private, because I want users to modify the data in the collection only by means of the Additem, RemoveItem, and Clear methods, or by internal helper functions within the class itself, which I’ll discuss later.
Notice the difference between module-level variables declared in a standard module vs. those in a class module. The variables in a standard module occur only once in the entire application. Module-level variables in a class module, on the other hand, are maintained separately for each instance of the class that is created in code. This allows each instance of the clsUnboundList class to maintain a separate list of data for its list box or combo box.
The next module-level variable needed is a pointer to an actual list box or combo box on a form:
Private mControl As Control
In order to use the clsUnboundList class in an application, the user first must create a variable of the clsUnboundList type, and then instantiate it. The variable could be created anywhere, such as in a standard module or in the code behind a form, but the instance variable must remain in scope for the entire time that the form is open. A convenient place to declare an instance variable for a class object is in the Declarations section of a form:
Dim moURLs As clsUnboundList
The module-level variable named moURLs will remain valid until the form closes. When the form closes the variable goes out of scope, and the objects created from the variable are automatically destroyed.
Creating the module-level variable in this way doesn’t actually instantiate the variable. It’s simply a pointer that has the potential to hold an object of the clsUnboundList type. You must instantiate the variable at some time after the form opens. In addition, you must tell the class which list box or combo box to wrap or encapsulate. In the case of this class, a convenient place to do this is in the OnOpen event of the form:
Private Sub Form_Open(Cancel As Integer) Set moURLs = New clsUnboundList Set moURLs.Control = Me!lisURLs End Sub
The first Set statement uses the New keyword to create a new instance of the clsUnboundList class and assign it to the module-level moURLs variable in the form. When you instantiate a variable from a class, any code in that class’ Initialize event is fired. In the case of the clsUnboundList class, I need to prepare the mcolItems collection variable in the class:
Private Sub Class_Initialize() Set mcolItems = New Collection End Sub
An alternative way to instantiate a variable is to use the following syntax:
Dim moURLs As New clsUnboundList
When you use this method, you don’t need to use an explicit Set statement to create a new instance. Any reference to the class’ methods or properties automatically instantiates the variable. The drawback to this method is that Access must include additional overhead checking throughout your code to determine whether or not the variable has already been instantiated whenever you refer to the variable. [Plus, it makes it more difficult later during the debugging cycle to determine when the object was instantiated.—Ed.] Using the first method makes your intentions clear, and is Microsoft’s recommended style.
The second Set statement shown in the form’s OnOpen event passes a pointer to an actual list box on the form, called lisURLs, into the class. It does this by setting the Control property of the class to the list box.
Property procedures in classes
Access classes include three kinds of specialized procedures known as property procedures. In addition to standard Function and Sub-type procedures, your classes can include Property Set, Get, and Let procedures. These procedures are used to assign or retrieve values to the properties of the class.
Property Get procedures are used to retrieve values from a class. Property Let procedures are used to set the value of a class’ property. You can use the Get and Let procedures to validate input, or to provide additional processing as a result of setting or retrieving a value.
The clsUnboundList example doesn’t require any Property Let or Get procedures, but these are important and useful constructs that you should be familiar with.
The clsUnboundList class does use a Property Set procedure. The Property Set procedure is used to assign an object to a variable in a class. In the sample code shown earlier, in the OnOpen event of the form, the user assigns a particular list box to the Control property of the moURLs object:
Set moURLs.Control = Me!lisURLs
When this code executes, Access triggers the following Property Set procedure in the clsUnboundList class:
Public Property Set Control(Control As Control) If (Not TypeOf Control Is ListBox) And _ (Not TypeOf Control Is ComboBox) Then MsgBox "Please assign only list boxes or " & _ "combo boxes to this property" Else Set mControl = Control mControl.RowSourceType = "Value List" End If End Property
Because I want the user to be able to use either a list box or a combo box with the clsUnboundList class, I declare the parameter to the Property Set Control procedure “As Control” rather than “As Listbox” or “As ComboBox”. This makes the property procedure more flexible, but I must then use some additional error trapping to be sure that the user doesn’t supply the wrong type of control, such as a text box or a command button, to the property. The Property Set procedure checks to ensure that the passed argument is a list box or combo box, and then assigns the passed argument to the class’ private module-level mControl variable.
Users outside the class aren’t allowed to refer to the mControl object directly. They can use only the property procedures and other procedures that I provide through the class. Code within the class is able to refer to mControl directly, however, as in this example where the passed control’s RowSourceType is explicitly set to Value List.
Because mControl just contains a pointer to an actual list box or combo box, changing the RowSourceType of the mControl variable automatically has the effect of changing the same property on the real list box or combo box.
Class procedures provide its methods
Most of the user interface to the clsUnboundList class isn’t provided through property procedures. Instead the class contains public procedures that can be accessed via an instance variable created from the class. This is how the AddItem, RemoveItem, and Clear methods of the class work.
The declaration for the AddItem method in the clsUnboundList class looks like this:
Public Sub AddItem(varItem As Variant)
The sub is made public so that code outside of the class itself can call the procedure. The argument varItem is a variant so that the user can pass strings, or numbers, to the procedure without having to do an explicit type conversion.
The calling procedure uses the AddItem method to add items to the list by referring to the object variable it instantiated earlier:
moURLs.AddItem "http://www.vb123.com/kb"
This syntax fulfills the goal I originally established of making the Access list box behave more like a VB unbound list box. You can repeatedly add items to the list box using this simple syntax instead of using a list filling callback function or maintaining your own value list.
How do the items actually get added? The items are added to the list by the public class’ AddItem subroutine, which is shown in Listing 1.
The AddItem method begins by checking its private mcolItems collection to see if the item is already in the collection. You can provide any behavior you want for your list box AddItem routine. This procedure simply ignores duplicate values, but you could generate an error, or even allow duplicates if you like.
If the value passed doesn’t already exist, then the following statement adds the value to the internal collection:
mcolItems.Add varItem
The Collection type isn’t new to Access 97. It first appeared in Access 95. I’m using a collection to store the class’ instance data, but this could have been done with an array or some other method.
Adding the item to the collection is fine, but how does that get the updated list of items into the list box or combo box?
The AddItem procedure, and several other methods of the clsUnboundList class, make use of a simple helper function called ConcatRowSourceStrings (see Listing 2).
This function loops through the items in the mcolItems collection, building up a new RowSource string by separating the values with a semicolon, and returns the entire string as the function’s return value. The AddItem method uses the string returned from the function as the new RowSource of the encapsulated list box or combo box:
mControl.RowSource = ConcatRowSourceStrings()
Once again, because the private mControl variable has been set to the value of a real combo or list box on the form, changing one of its properties actually changes the same property on the original control. So setting the RowSource property of the private mControl variable also changes the RowSource property of the original list box. Access automatically displays the updated list.
The RemoveItem method works similarly to the AddItem method. The basic idea is to remove the item from the class’ private mColItems collection, and then re-create the RowSource string from the remaining values using the ConcatRowSourceStrings function.
The only wrinkle here is that I’m trying to emulate Visual Basic’s RemoveItem function, which takes a numeric index as its argument. Both VB’s and Access’ list box items are indexed starting with zero, while collections are indexed starting with one. Therefore, in order to make my RemoveItem method work like the VB method I must assume that the user is passing a zero-based index, and offset the value by one when referring to the one-based collection:
Public Sub RemoveItem(intItem As Integer) Dim intRemoveItem As Integer ‘to emulate VB’s RemoveItem method, ‘assume a zero-based index ‘rather than a 1-based index intRemoveItem = intItem + 1 If intRemoveItem >= 1 And intRemoveItem <= mcolItems.Count Then mcolItems.Remove intRemoveItem mControl.RowSource = ConcatRowSourceStrings() End If End Sub
I perform a small amount of validation here to ensure that the index item that is passed is actually a valid item number.
The user can remove an item from the list box with the following code:
moURLs.RemoveItem 2 The Clear method is even simpler: Public Sub Clear() Set mcolItems = Nothing Set mcolItems = New Collection mControl.RowSource = "" End Sub
Rather than looping through the mcolItems collections and removing the items one at a time,
this code simply re-creates the collection from scratch and explicitly sets the mControl’s RowSource property to a zero-length string. As with the AddItem and RemoveItem methods, using the Clear method in code is quite straightforward:
moURLs.Clear
Enhancing functionality with classes
So far I’ve simply been taking functionality from VB and adding it to Access’ unbound list and combo boxes. What if you want to add completely new features? The beauty of using class modules to encapsulate standard Access objects is that you can add as much or as little new behavior as you like. You can customize the control to meet your particular needs. To get you started on customizing the clsUnboundList class I added two completely new methods, RemoveSelected and Sort.
The RemoveSelected method lets you easily remove the item that is currently selected in a list box. If you’re using a simple or extended multi-select list box, the RemoveSelected method automatically removes all selected items. Using the RemoveSelected method is easy:
moURLs.RemoveSelected
The code for the RemoveSelected method is shown in Listing 3.
This method’s behavior will vary based on the type of control. Combo boxes don’t have a MultiSelect property; if you try to refer to this property in code you’ll receive a runtime error. If the control is a list box, the behavior must differ depending on if the MultiSelect property is set to zero (standard list box) or one or two (simple or extended multi-select styles.)
The code for list boxes uses another helper function:
Private Sub RemoveFromCollection( _ varItem As Variant) Dim intCounter As Integer For intCounter = 1 To mcolItems.Count If mcolItems.Item(intCounter) = _ varItem Then mcolItems.Remove intCounter Exit For End If Next intCounter End Sub
The RemoveFromCollection helper function is declared as a private procedure. Unlike the public methods of the clsUnboundList class, this procedure is only intended to be called from other procedures within the class itself. The code simply loops through the mcolItems collection and removes the value matching the varItem argument.
If the list box doesn’t use one of the MultiSelect styles, then the RemoveSelected method simply removes the currently highlighted item in the list box:
RemoveFromCollection mControl.Value
If this is a MultiSelect list box, then the code must delete each of the selected items in turn:
For Each varCurItem In mControl.ItemsSelected RemoveFromCollection _ mControl.ItemData(varCurItem) Next varCurItem
The code for combo boxes is slightly different, and it illustrates an interesting point about using class methods. The class itself can use the same public methods that the external callers use:
Me.RemoveItem mControl.ListIndex
I’ve already discussed the public RemoveItem method of the class, which takes a numeric zero-based index for a list box or combo box and removes the corresponding one-based item from the private mcolItems collection. Here the RemoveSelected method calls the RemoveItem method on itself (using the Me syntax familiar to Access Basic programmers when used in code behind forms.) The ListIndex property of a combo box refers to the item that is currently selected in the list (the one we want to remove). Because the ListIndex property is already zero-based, it fits in perfectly with the existing RemoveItem method. And because the method is available for others to use, we might as well use it ourselves!
Finally, the Sort method takes an existing list box or combo box and sorts its contents alphabetically. Using the Sort method in your code is as simple as can be:
moURLs.Sort
The Sort method (see Listing 4) makes use of a standard QuickSort function, QSArray (see Listing 5), which is designed to work on arrays rather than collections. The Sort method writes the values in the mColItems collection into a temporary array, sorts the array, and then writes the sorted values back into the collection. After sorting the collection, the RowSource of the control is re-created with the ConcatRowSourceStrings function.
One thing to consider is which of these types of helper functions belong in the class itself, and which might be better off in a separate standalone module. The advantage of including the helper functions such as QSArray directly in the class is that this makes the class totally self-sufficient. You can copy the class into any Access database and use it without having to worry about any required functions that might be found in other modules. The disadvantage is that the code is essentially duplicated for each instance of the class.
Using the class in your forms
You’ve already seen simple examples of using AddItem, RemoveItem, Clear, and Sort in code. Except for the housekeeping business of declaring the instance variable and assigning a control to it in the form’s OnOpen event, using the class methods is quite transparent in your code. You can use the fairly complex additional behavior encapsulated in these simple-sounding methods to do a number of other interesting things.
For example, the sample database LBCLASS.MDB includes several sample forms illustrating some interesting techniques. One shows you how to create parallel list boxes on a form and transfer items from one to another the way the built-in Access wizard forms do (see Figure 1). Another example shows how to simplify the NotInList event code of an unbound combo box:
Private Sub cboAddItem_NotInList _
(NewData As String, Response As Integer)
If MsgBox("The value " & Chr(34) & _ NewData & Chr(34) & _
" is not on the list. " & _
"Would you like to add it?", _ vbQuestion + vbYesNo, "Combo Test") _ = vbYes Then
moUnboundCombo.AddItem NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Shortcomings and enhancements
Although the clsUnboundList class works well for relatively small amounts of data, there are limitations in its current design. Because I use the control’s standard RowSource property, the number of items that can fit in a list is limited to about 2,000 bytes. I attempted to make a version of the clsUnboundList class that would provide its values using the Access list filling callback function method I discussed earlier, but Microsoft has confirmed that the RowSourceType property of a combo box or list box can’t refer to a property of a class. It must be a function in a standard module or in the code behind a form.
One drawback of encapsulating a control into an object variable is that it can be confusing to look at code that sometimes refers to the list box or combo box itself, and sometimes refers to a method or property of the object variable:
Dim strValue As String strValue = Me!lisURLs.ItemData(0) moURLs.AddItem "new item"
Me!lisURLs refers to the actual list box, while moURLs is the object variable of the clsUnboundList type that has encapsulated that list box. One solution is to create shadow properties and methods in the class that correspond to the standard properties in the list box. For example, you could create a Visible, Left, or BackColor property for your class. Setting the property for the class object would simply pass the value on to the encapsulated control. Whether this is useful behavior for your class—or just overkill—is something only you can decide.
There are other ways in which the clsUnboundList class can be enhanced. For example, you could add a method to retrieve values from a table or query, or to write the values out to a table. You could allow for multi-column list boxes or provide a search function.
Conclusion
I’ve barely touched on some of the new capabilities that Access’ new class modules provide. For instance, you can create class objects that contain collections of other objects. This lets you create essentially your own object hierarchy. I’ve been discussing the use of classes with unbound list and combo boxes, but there’s no reason you couldn’t encapsulate bound combo boxes, text boxes, or even forms and reports! You could add custom validation rules or specialized keystroke handling.
Once you start seeing the possibilities of using classes in your application, everything seems like a candidate for the class treatment.
Option Compare Database
Option Explicit
‘Listing 1. Create an AddItem method to mimic Visual Basic’s list box.
Public Sub AddItem(varItem As Variant) Dim varTest As Variant Dim bFound As Boolean Dim intCounter As Integer If Not IsNull(varItem) Then If InStr(varItem, ";") Then MsgBox "Item may not contain " & "; characters" Else For Each varTest In mcolItems If varTest = varItem Then bFound = True End If Next varTest If Not bFound Then mcolItems.Add varItem mControl.RowSource = ConcatRowSourceStrings() End If End If End If End Sub
‘Listing 2. Helper function to build RowSource string from items in the private collection.
Private Function ConcatRowSourceStrings() As Variant Dim varItem As Variant Dim varResult As Variant For Each varItem In mcolItems varResult = varResult & varItem & ";" Next varItem If Len(varResult) Then varResult = Left(varResult, Len(varResult) - 1) End If ConcatRowSourceStrings = varResult End Function
‘Listing 3. This procedure automatically removes all selected items from the list.
Public Sub RemoveSelected() Dim varCurItem As Variant Dim intCounter As Integer If TypeOf mControl Is ListBox Then If mControl.MultiSelect = 0 Then If mControl.ListIndex <> -1 Then RemoveFromCollection mControl.Value End If Else '1=Simple, 2=Extended For Each varCurItem In mControl.ItemsSelected RemoveFromCollection _ mControl.ItemData(varCurItem) Next varCurItem End If mControl.RowSource = ConcatRowSourceStrings() ElseIf TypeOf mControl Is ComboBox Then Me.RemoveItem mControl.ListIndex End If End Sub
‘Listing 4. The Sort procedure sorts the items in the list using the popular quick sort algorithm.
Public Sub Sort() Dim intCounter As Integer ReDim avaritems(1 To mcolItems.Count) _ As Variant For intCounter = 1 To mcolItems.Count avaritems(intCounter) = mcolItems.Item(intCounter) Next intCounter QSArray avaritems, 1, mcolItems.Count Set mcolItems = Nothing Set mcolItems = New Collection For intCounter = 1 To UBound(avaritems) mcolItems.Add avaritems(intCounter) Next intCounter mControl.RowSource = ConcatRowSourceStrings() End Sub
‘Listing 5. A complex utility routine such as this sort procedure
‘can be hidden by encapsulating it within a class.
Private Sub QSArray(arrIn() As Variant, _ ByVal intLowBound As Integer, _ ByVal intHighBound As Integer) Dim intX As Integer Dim intY As Integer Dim varMidBound As Variant Dim varTmp As Variant If intHighBound > intLowBound Then varMidBound = arrIn((intLowBound + _ intHighBound) \ 2) intX = intLowBound intY = intHighBound Do While intX <= intY If arrIn(intX) >= varMidBound And arrIn(intY) <= varMidBound Then varTmp = arrIn(intX) arrIn(intX) = arrIn(intY) arrIn(intY) = varTmp intX = intX + 1 intY = intY - 1 Else If arrIn(intX) < varMidBound Then intX = intX + 1 End If If arrIn(intY) > varMidBound Then intY = intY - 1 End If End If Loop Call QSArray(arrIn(), intLowBound, intY) Call QSArray(arrIn(), intX, intHighBound) End If End Sub
Other Pages That May Interest
Access Interfaces: Creating Paired Listbox Controls, Part 2
Your Listbox-Filling Options
