Powerful list and combo boxes have always been an important part of Access as Andy Baron shows in this article. This article uses SQL Server as a back-end.
Combo and list boxes have always been very full-featured in Access. Access developers take for granted capabilities like multiple columns and having control over combo box list width, which are unavailable in other development environments (unless you buy third-party controls or perform fancy API tricks). Not much has changed in the Access versions of these controls over the years, other than the addition of the Dropdown method for combo boxes—until now, that is! Access 2002 offers important new ways to populate your list controls.
The list-filling techniques discussed in this article are all alternatives to using queries based on persistent connections to your data. By reducing persistent connections, you improve your application’s scalability. However, increasing scalability, as in these examples, is only one reason to use these techniques. They’re also handy if you want to list items, like files or printer names, that aren’t stored in a database. Without these techniques, you’d need to dump the data into a table and use a query based on that table as the row source of your list control, or use complex callback functions.
The sample application
The sample application that accompanies this article contains a form with several examples of combo and list boxes that demonstrate the list-filling capabilities of Access 2002 (see Figure 1).
Figure 1
The sample application gets its data by opening ADO recordsets against the SQL Server Northwind database. To establish a connection to the database, the code uses the accompanying Data Link file, Northwind.UDL. You can adjust the connection properties by double-clicking on this file and using the dialog box shown in Figure 2. If necessary, you can even change the connection to use the Jet provider and to point to Northwind.mdb, instead of using SQL Server.
Figure 2
Here’s the code that opens a connection to the database when one is needed:
Public Function ConnectToNorthwind( _ cnn As ADODB.Connection) As Boolean Dim blnState As Boolean If cnn Is Nothing Then Set cnn = New ADODB.Connection End If On Error Resume Next If cnn.State = adStateOpen Then blnState = True Else cnn.ConnectionString = _ "File Name=" & CurrentProject.Path & _ "\Northwind.UDL" cnn.Open If cnn.State = adStateOpen Then blnState = True Else blnState = False End If End If ConnectToNorthwind = blnState End Function
The recordsets in the sample code use client-side cursors, and their connections to the database are closed immediately after the data is retrieved. The sample application doesn’t maintain any persistent connections to the database, increasing scalability.
These “disconnected” list-filling techniques are useful in n-tier applications where data is supplied by middle-tier objects. If that data is packaged as ADO recordsets, then you can use code very similar to the sample code in this application. You’ll also see an example of using XML data to create an ADO recordset. You’re not required to rely on recordsets—you could use the MSXML parser, for example, to fill lists from XML data without using ADO recordsets at all. To do that, you’d use a value list as your row source type. Value lists are much improved in Access 2002.
Expanded value list capacity
As in prior versions of Access, you can populate combo and list boxes programmatically or in design view by setting the RowSourceType to “Value List” and assigning a delimited list of string values to the RowSource property. For example, you might assign “Male;Female” to the row source of a combo box used for entering a person’s gender.
However, in previous versions of Access, this technique could only be used for very short lists, because the RowSource property was limited to strings containing a maximum of 2,048 characters. In Access 2002, that maximum has been increased to 32,768 (32K). This makes value lists much more practical, and, as a side benefit, it also enables you to use more complex SQL strings as row sources.
To create multicolumn controls based on value lists, set the number of columns in the control’s Column Count property, and add a semicolon or comma delimiter between data for each column. For example, if you want to use a code of 1 for Male and 2 for Female, enter the values shown in Table 1 for the control’s properties.
Table 1. Sample values for a value list.
| Property name | Value |
| Column Count | 2 |
| Row Source Type | Value List |
| Row Source | 1;Male;2;Female |
You can use either semicolons or commas to delimit the list, or a combination of the two. If you use commas in the property window, Access will convert them to semicolons. However, when assigning a value list row source in code, you can use either or both characters.
So, what do you do if the values in your list contain commas or semicolons? For example, suppose you want a list of employees to appear as LastName, FirstName. You can accomplish this by enclosing your values in quotes. For example, the row source of a two-column value list containing employee IDs and names might look like this:
1,"Baron, Andy",2,"Chipman, Mary"
The sample application uses the ADO GetString method to transform a recordset into a semicolon-delimited list, with quotes surrounding each of the values. The Employees combo box shown in Figure 1 has a row source type of Value List, and it has this code in its Enter event procedure:
Private Sub cboEmployeeVL_Enter() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strEmployees As String On Error GoTo HandleErr If ConnectToNorthwind(cnn) Then Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.Open _ Source:="SELECT EmployeeID," _ & " LastName + ', ' + FirstName" _ & " FROM Employees" _ & " Order By LastName, FirstName", _ ActiveConnection:=cnn, _ CursorType:=adOpenStatic, _ Options:=adCmdText Set rst.ActiveConnection = Nothing CloseAndReleaseConnection cnn strEmployees = rst.GetString(adClipString, _ ColumnDelimeter:=""";""", RowDelimeter:=""";""") strEmployees = """" & _ Left(strEmployees, Len(strEmployees) - 2) Me!cboEmployeeVL.RowSource = strEmployees rst.Close Else MsgBox "Couldn't connect to Northwind" End If ExitHere: CloseAndReleaseConnection cnn Set rst = Nothing Exit Sub HandleErr: MsgBox Err & ": " & Err.Description, , _ "Error in cboEmployeeVL" Resume ExitHere End Sub
The only tricky part of the code is the doubling up of quotes inside of quotes. The value list string that this code produces begins like this:
"5";"Buchanan, Steven";"8";"Callahan, Laura";
The first column of the combo box, containing employee IDs, has a width of zero, so it’s hidden. When you select an employee, the Orders list box, which is also shown in Figure 1, gets populated with a list of the orders taken by that employee, showing the order ID and order date. The code for this is a little simpler because there’s no need to worry about the order IDs or dates including commas or semicolons. Without that worry, there’s no need to enclose the values in quotes:
Private Sub cboEmployeeVL_AfterUpdate() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String Dim strOrders As String On Error GoTo HandleErr If ConnectToNorthwind(cnn) Then strSQL = "SELECT OrderID, OrderDate " & _ "FROM Orders " & _ "WHERE EmployeeID = " & Me!cboEmployeeVL & _ " ORDER BY OrderDate DESC" Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.Open _ Source:=strSQL, _ ActiveConnection:=cnn, _ CursorType:=adOpenStatic, _ Options:=adCmdText Set rst.ActiveConnection = Nothing CloseAndReleaseConnection cnn If rst.EOF Then rst.Close GoTo ExitHere End If strOrders = rst.GetString(adClipString, _ ColumnDelimeter:=";", RowDelimeter:=";") Me!lboOrdersVL.RowSource = strOrders rst.Close Else MsgBox "Couldn't connect to Northwind" End If ExitHere: CloseAndReleaseConnection cnn Set rst = Nothing Exit Sub HandleErr: MsgBox Err & ": " & Err.Description, , _ "Error in cboEmployeeVL_AfterUpdate" Resume ExitHere Resume End Sub
Here’s the beginning of the string that this code produces:
11075;5/6/1998;11068;5/4/1998;
Both of these event procedures, as well as all of the others in this application, call a public sub to close and release the ADO connection:
Public Sub CloseAndReleaseConnection( _ cnn As ADODB.Connection) If Not cnn Is Nothing Then If cnn.State = adStateOpen Then cnn.Close Set cnn = Nothing Else Set cnn = Nothing End If End If End Sub
Using GetString is, of course, only one of many possible ways to construct a value list. If you’re working with XML data, you could even create an XSL stylesheet that would transform an XML document into a delimited string suitable for use as a value list. In addition, Access 2002 provides two new methods for adding and removing items from value lists.
Editing value lists
If you’ve used Visual Basic forms or the forms package that’s used with VBA in Word and Excel, then you’ve probably used the AddItem method to populate combo and list boxes. Access 2002 introduces AddItem and RemoveItem methods that are implemented as wrappers around value lists. To use these methods in Access, your combo or list box must have a Row Source Type of Value List, and these new methods simply append or remove items from the value list string.
The sample application includes two controls very similar to the ones shown in Figure 1, except that their code uses AddItem rather than GetString to construct the value lists. Here’s the section of code that populates the employees combo box using the new AddItem method:
With Me.cboEmployeesAI Do Until rst.EOF .AddItem """" & rst!EmployeeID & """;""" _ & rst!FullName & """" rst.MoveNext Loop End With
You still need to add extra quotes if your values might contain commas or semicolons, and you need to add a delimited set of values for each item if you need multiple columns. In other words, all AddItem does is append a string to the existing value list string.
The RemoveItem method isn’t used in the sample application. RemoveItem enables you to delete a section of your value list by specifying the zero-based row index or the value in the bound column of that row. This is a bit handier than AddItem, because the code to do this manually would be a bit trickier to write.
There’s no Clear method to go along with AddItem and RemoveItem. If you want to clear the contents of a value list, you must assign an empty string to the row source property. The code for the AddItem version of the Orders list box does this:
With Me.lboOrdersAI .RowSource = "" Do Until rst.EOF .AddItem rst!OrderID & ";" _ & rst!OrderDate rst.MoveNext Loop End With
If you’ve worked with the VB or VBA list controls, you may also have used their Column and List properties, which enable you to fill a control by assigning an array of values to the property. These properties weren’t added to the Access controls, although the familiar Access Column property still enables you to read values stored in your combo box. The sample application includes examples of using the MS Forms combo and list boxes (the ones used in Word and Excel) on the Access form. In the examples, the ADO GetRows method transforms a recordset into an array, and the code assigns this array to the Column property of the control. This is a very efficient technique that isn’t available in Access controls:
varList = rst.GetRows cboMSF.Column = varList
Unfortunately, the missing Clear method, List property, and read/write Column property aren’t the only shortcomings to the implementation of AddItem and RemoveItem in Access 2002. Performance is seriously flawed. Perhaps this will be fixed in a service pack, but using the original release of Access 2002 to run the sample application results in very noticeable delays when the controls are being filled by using AddItem. For this reason, be sure to test your application carefully before relying on these new methods in production code.
Despite the poor performance of AddItem for populating lists, however, the expansion of the size of value lists in Access 2002 is a very welcome feature. With just a little effort, you can easily write your own list-filling code for value lists, using GetString, XML techniques, or simple string concatenation, and you now have a very viable alternative to relying on database objects as row sources. Although the controls in the sample application are unbound, you can use value lists with bound controls (ones that have control sources) just as effectively.
Assigning recordsets to controls
In Access 2000, Microsoft made it possible for you to assign recordsets to forms, instead of setting the form’s record source. Now in Access 2002, you can assign a recordset to a combo or list box, instead of setting the control’s row source (you can also now assign properly shaped recordsets to Access reports).
For the types of list-filling needs illustrated in the sample application, this is clearly the most suitable new technique to use, and you’ll find that it performs very well. Why bother with value lists when you can assign your recordset directly to the control? With this new capability, the only time you need to consider using a value list is if your data doesn’t lend itself easily to being gathered in a recordset.
The sample application contains a generic procedure, LoadControlRst, that accepts a control, a source string, and a command type as parameters. The code opens a recordset against Northwind using the source string and the command type. In the sample form, the source strings are SQL statements, and the command type is adCmdText; but you could also use the name of a stored procedure with adcmdStoredProc as the command type. Once the recordset is created, it’s assigned to the control
The LoadControlRst procedure was written to work with lookup data that doesn’t change often—for example, lists of products or employees. Instead of going to the database each time the procedure needs to fill a list control, it first looks for a locally saved XML file. If it finds the file, it loads a recordset from the file. If it doesn’t find the file, it connects to the database, opens a client-side recordset, disconnects, saves the recordset as XML, and assigns the recordset to the control. To refresh the XML files, you call a procedure that deletes the local XML file and reloads the control. Here’s the opening of the LoadControlRst procedure:
Public Sub LoadControlRst( _ ByRef ctl As Control, _ strSource As String, _ lngRowSourceCommandType As ADODB.CommandTypeEnum) On Error GoTo Handle_Err Const conProcName As String = _ "LoadControlRst" Dim strFile As String Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset This code generates the name that will be used for the XML file: strFile = CurrentProject.Path & "\" _ & CurrentProject.Name & "_" & ctl.Name & ".XML"
With the preliminary work done, the code then creates a client-side recordset and either opens the XML or creates it:
Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.CursorType = adOpenStatic rst.LockType = adLockReadOnly If Len(Dir(strFile)) > 0 Then rst.Open Source:=strFile, Options:=adCmdFile Else Set rst.ActiveConnection = cnn rst.Open Source:=strRowSource, _ Options:=lngRowSourceCommandType rst.Save strFile, adPersistXML Set rst.ActiveConnection = Nothing CloseAndReleaseConnection cnn End If
In the previous code, if the XML file is found, the recordset is loaded from the XML file. If the file isn’t found, the data is retrieved, and the resulting recordset is saved as an XML file. With the recordset now created, it’s assigned to the control passed to the routine:
Set ctl.Recordset = rst
The error handler for this routine has to handle finding the file already present when it saves the recordset (this could happen if someone saves it between the time we checked for it and when we saved it):
Handle_Err: Select Case Err.Number Case 58 ' File already exists. Kill strFile Resume Case Else Err.Raise Err.Number, _ conProcName, Err.Description End Select
Figure 3 shows the part of the sample form that works with recordsets and XML. When you click the Refresh button next to the employees combo box, it runs this code:
Call RefreshControlRst( _ Me.cboEmployeesXML, _ "SELECT EmployeeID, " _ & "LastName + ', ' + FirstName" _ & " AS FullName" _ & " FROM Employees" _ & " Order By LastName, FirstName", _ adCmdText)
Figure 3
Here’s the RefreshConrolRst procedure that’s called by the Refresh button click event procedure:
Public Sub RefreshControlRst(ctl As Control, _ strRowSource As String, _ lngRowSourceCommandType As ADODB.CommandTypeEnum) Const conProcName = "RefreshControlRst" On Error Resume Next Kill CurrentProject.Path & "\" _ & CurrentProject.Name & "_" & ctl.Name & ".xml" Err.Clear On Error GoTo Handle_Err Call LoadControlRst(ctl, strRowSource, _ lngRowSourceCommandType)
This example is somewhat complex, because of the use of XML files to store lookup data locally without making repeated calls to the database. But the part of the code that works with the new Recordset property of list controls is very straightforward—you simply assign your recordset to the property:
Set ctl.Recordset = rst
You can use disconnected recordsets, as the sample code does. However, you can’t use recordsets that you create from scratch yourself. ADO enables you to create a new recordset, add field definitions to it, and then add rows of data, without ever connecting to a data source. This type of recordset can’t successfully be assigned to an Access list control. If you’re composing list data in code without ever making a connection to a data source, that would be a good place to use a value list.
List controls have always been among the most useful features in Access, and in Access 2002 they get even better. Value lists now can hold up to 32K characters, not the measly 2K that was supported previously. New AddItem and RemoveItem methods make it easier to edit value lists, but test their performance in your application carefully before you rely on them. You can also now fill a list control simply by assigning a recordset to the control’s new Recordset property. One application of these new techniques is to build forms that don’t require persistent connections to a database in order to display lookup data.
