Chapter 13

Mastering Combo and List Boxes


Combo and List Box controls are perhaps the most useful in the Windows application development environment. They allow the presentation of a large quantity of information using a minimum amount of screen space. You will come to rely heavily on these controls in your Access solution development efforts. In this chapter, I show you how to squeeze the maximum benefit out of these two controls, including:

Coping with a Changing World


Having been in the business of capturing and protecting clients' data for many years, I have learned to expect almost anything, so I try to design flexible applications that permit change. Since the early days of Access, we have used a combo box on our forms to reflect the values for Sex, with Male and Female in a lookup table.

Clients often ask why we do not use an option group instead because that type of control is much friendlier when dealing with a simple, two-option selection. My response has always been: if your applications are ready for anything, then changes cost you nothing.

Recently, certain political groups started an international lobbying effort to define five legal sexes, instead of the standard two, in an effort to include sexual minorities. If these efforts succeed, tens of thousands of computer applications worldwide will be thrown into chaos as the range of data options expands.

Our applications, however, would require only an addition of values to a lookup table-a one-minute process. Whether or not you agree with the way "the times, they are a-changin'," wise use of combo and list boxes enables your applications to be ready for the changes.

Try to imagine a world without lists-the shopping list, the Christmas list, the voting ballot, and the multiple-choice exam question are all a part of daily life. Without lists, we don't have a way to easily represent choices or to organize information.

Access forms are also full of lists. Sales prospects can live in a defined list of states and provinces, inventory items can have subcomponents, and each employee works in a specific department. Even in their simplest form, the lists in Access Combo Box and List Box controls are powerful tools for easing the users' data entry burden and for providing data validation with no development overhead.

Yet many developers take for granted that-with a little extra effort and clever coding-these form controls can be made even more powerful. When building expert forms, you will rely heavily on Combo and List Box controls to restrict and validate data. In this chapter, we explore several ways to squeeze extra capabilities and performance out of these invaluable objects.

Expert Combo Box Techniques

Combo boxes are one of the most useful features in Access. Because they provide users with a limited range of options, they minimize data entry errors and confusion. Fortunately, two of the most common user complaints about combo boxes were addressed by the Access team with the Access 95 release, as follows:


Given this change in the Access response to Null values in a LimitToList combo box, you should review your converted applications for such controls. If one of the purposes of the LimitToList property in your application was to prevent Null values from being saved in a bound field, you will have to add new validation to the form.


These two enhancements reduce some of the major frustrations that developers experienced when they used combo boxes; thus, you will now be adding even more of these controls to your forms to increase their usability.

Trapping Additions to Combo Boxes

When loading a combo box with values to present to users, you usually provide list items for the control from a table or query using Table/Query as the RowSourceType value. The RowSource property usually contains a table name, query name, or SQL statement that refers to a lookup table, which is a table of values specifically defined to map short codes in the data to their longer values displayed on forms and reports.

In a data entry application, it is not always possible to anticipate the range of values that will be required by the users. Simply setting the LimitToList property of a combo box to False (No) so that users can enter data into the control that is not on the list is seldom an adequate solution because it removes the validation benefits of using the combo box.


Removing validation from combo boxes enables new lookup values to enter the form's data records without being added to the related lookup table; this destroys data integrity.

A solution to this problem is to help users select items that are not in the combo box's list and to trap such an event, recording and validating the entry. My favorite technique for this is to use a union query to place a new item marker in the control without having to add it to the underlying lookup data. Starting with Access 2, you can create SQL statements and saved queries that use the UNION operator to combine (append) values from two or more data sources. (For more information on union queries, see Chapter 10, "Creating Expert Tables and Queries.")

You begin the process of adding a new record marker to a combo box by creating a union query like the one in Listing 13.1, which produces a list of items with a new record marker at the top.

Listing 13.1 AES_Frm1.Mdb-Adding a Marker Item to a Combo Box List
by Using an Expression

SELECT FirstName As SalesAgent

FROM tblPeople

UNION SELECT "(add new)"

FROM tblPeople

ORDER BY SalesAgent;

By default, Access removes duplicate records from union query results, so only one record is returned by the expression "(add new)" as shown in the SELECT statement, and in Figure 13.1.

Fig. 13.1

The (add new) on this list is not in the source table, it was added by the UNION operator.


You can create very powerful queries using the UNION operator to combine values from multiple tables and queries into a single combo box list. For example, even if American states, Canadian provinces, and Swiss cantons were stored in separate tables, you could combine them with UNION into one list for a RowSource property. However, union queries are among the slowest operations in Jet, so for the best results review the performance rules in the "Creating Faster Queries" section in Chapter 10.


Although the technique shown works quite nicely for adding an additional virtual (non-table) item to your list, I prefer to vary this new record marker technique to eliminate the need to hard-code the marker in the query definition. Because the (add new) expression shown in Listing 13.1 is stored with the QueryDef object (in the SQL), changing the string as the needs of the users change-or as the language base of the application is modified-becomes a significant development burden. Instead, you can create a table that you configure the new item marker in for the entire application without saving it into any queries or SQL strings used as RowSource properties. Then, use UNION to add the marker table value to lists as needed.

For example, to achieve the same result as in Listing 13.1, you can create a field called AddNewMarker in the defaults table zstblDefault and place the new item string in the table field. Any combo box RowSource properties that require a new item marker can refer to the table to get it, as in Listing 13.2. From the user's perspective, the combo box looks the same as in Figure 13.1.

Listing 13.2 AES_Frm1.Mdb-Adding a Marker Item from a Table to a Combo Box List

SELECT FirstName As SalesAgent

FROM tblPeople

UNION SELECT AddNewMarker As SalesAgent

FROM zstblDefault

ORDER BY SalesAgent;


The string that you use for the new item marker should be one that is guaranteed to sort to the top of the list for any data items your list might include. You can review character sort orders in the help topic "Character Set (0-127)" in Access (search for "character sets" in the index). The sort order of characters that sort before letters of the alphabet is shown in Table 13.1.

The (add new) example used here is enclosed in parentheses so it will sort above alphabetic and numeric characters. However, it will not sort above a space or seven other special characters (see Table 13.1), so if any data items on the combo list begin with the special characters that sort above the parentheses, you must change your method.

Although our clients sometimes choose to vary the new item marker to suit their tastes (for example <Add>, *New*, or -New-), Access itself uses parentheses for nonstandard list markers in built-in lists, such as those on the Startup dialog box. My favorite method is to use (string) as well.

Table 13.1 Character Sort Orders in Access

Character

space

!

"

#

$

%

&

'

(

)

*

+

,

-

.

/

0-9

:

;

<

=

>

?

@

A-Z


When a user selects the new item marker from your combo box, your form can detect this event and run a code procedure. To trap when the user selects the new item marker, place a code snippet in the BeforeUpdate or AfterUpdate event of the Combo Box control to detect if the value selected by the user is the new value marker. Because your new value marker is designed to always sort to the first position on the list, your code knows the first item was selected when the ListIndex property of the control is zero, as in Listing 13.3.

Listing 13.3 AES_Frm1.Mdb-The First Item on a List Has a ListIndex Property of 0

Private Sub cboPeople_BeforeUpdate(Cancel As Integer)

' Purpose: Detect if the first item was selected

If Me!cboPeople.ListIndex = 0 Then

Beep

MsgBox "New option detected. Add your code here to create " _

& "a new list value.", vbInformation, "Add New - ListIndex"

End If

End Sub

Although your objective may be for the new item marker to always appear at the top of the list, it is better coding style not to assume that it's there. Because the database administrator (or perhaps even users) can change the marker default in the table, it is not guaranteed to sort to the top of every list (and thus return a ListIndex of zero). A better strategy is to get the new item marker using a Dlookup() function on the zstblDefault table and then to compare the value of the combo box to the variable in your code. Listing 13.4 shows this method.

Listing 13.4 AES_Frm1.Mdb-Detecting When the First Item on a List Is Selected by Value

Private Sub cboPeople_BeforeUpdate(Cancel As Integer)

' Purpose: Detect if the first item was selected

If Me!cboPeople.Value = DLookup("AddNewMarker", "zstblDefault") Then

Beep

MsgBox "New option detected. Add your code here to create " _

& "a new list value.", vbInformation, "Add New - Default"

End If

End Sub


Listing 13.4 uses a DLookup() function to retrieve the new item marker on demand. If the user cannot change the new item marker in the defaults table while an application is running, you can load the new item marker into a global variable instead and access the variable repeatedly in your code. This technique can save scores of DLookup() calls during a single application session and thus improves performance.

When the user selects the new item marker from a combo list, your code can respond by prompting for an item to add, and then validating the item and adding or rejecting it. Your application can collect a new item value by displaying a pop-up form for data entry, or by displaying an InputBox dialog box for the new value using Basic code. Your code adds the newly entered value to the underlying lookup table and requeries the Combo Box control to show the new value. The techniques in the following section explain how to add the new value.


This technique for adding a new item marker to a combo box works with list boxes as well.

Adding Values to a Combo Box List

In addition to-or instead of-a new item marker, your application can enable users to type an entry into a combo box that is not found in the combo's list. If this happens in a combo box whose LimitToList property is set to True (Yes), Access fires the NotInList event for the control. You can place code in the event to test the entered item for validity, and to add it to the source of the list or to reject it as required.

When the user types the new value and triggers the NotInList event, you can use code similar to that in Listing 13.5 to insert the value into the appropriate lookup table. Your code should also requery the combo box to display the new value in its sorted position.

Listing 13.5 AES_Frm1.Mdb-NotInList Event Code to Add a New Item

Private Sub cboAgent_NotInList(NewData As String, Response As Integer)

' Purpose: Add a new name to the underlying table for the list

' Pseudocode:

' 1. Create a Recordset against the list source

' 2. Add the user's item to the Recordset

' 3. Tell Access to requery the list and not alert the user

Dim dbs As Database

Dim rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblPeople", dbOpenDynaset)

' A recordset gives better control than a RunSQL action

rst.AddNew

rst!FirstName = NewData

rst.Update

' Tell the user what happened

Beep

MsgBox "Item '" & NewData & "' was added to the list and lookup table." _

, vbInformation, "New Item"

' Tell Access to ignore its built-in error message

' and to requery the list

Response = acDataErrAdded

rst.Close

dbs.Close

End Sub

In this listing, note that Access passes the value entered from the combo box to the NotInList event procedure as the NewData argument. This enables your code to test, display, or record the value. Also note that you can set the Response argument of the event in your code to select the disposition you want to trigger in Access. In this example, I set Response to an intrinsic constant (acDataErrAdded) that tells the event handler to requery the list, and not to display a warning message if the value is successfully found in the underlying table after the requery. The other constants for Response are the following:

These three Response values enable you to handle any data entry situation activated in a Combo Box control.

Listing Combo Box Values as Properties

You can provide values for a combo box directly in the control's properties by setting the RowSourceType property to Value List and by listing the values in the RowSource property. The values must be separated with semicolons, as in Figure 13.2.

Fig. 13.2

You can set combo box values directly in a control's Property dialog box.

In general, setting combo box values as saved properties of a form is not a good design strategy. Over time, the values listed in a form's design will need to be changed as the user's needs change, requiring rework and redeployment of the form or its application by the developer. Basing list values on a table is usually a more flexible strategy.

However, there are some special circumstances when using the RowSource property to list combo box values might be a good strategy. What follows is a description of two such situations.


Any strategy for loading the RowSource property with a list of values is only useful when the total length of the concatenated values is not large because the maximum length of the string in the property setting can only be 2,048 characters.

Loading a Combo Box from a File

Assume for a moment that your company has a large mainframe system for its inventory data. The inventory system maintains purchase and shipment information about the goods that the company buys from suppliers. On any given day, there are 50 to 100 open orders in transit.

Your job is to write a receiving system for the loading dock. The entry form for the system needs a combo box that shows vendor number and purchase order number for the open orders only, so that the workers on the dock can match each incoming order against a valid purchase order. The list of active open orders, however, is kept on the mainframe.

You have convinced your Information Technology group to download the order data into a file for you each night. They will even post the file to your application's directory after each download. Now, you only need to get the downloaded data to show up in your combo box.

Your first inclination might be to write a code routine to load the file into a table in the database each morning. The problem with this method is that someone must remember to run the load routine each morning. You'll need to write a scheduling routine, enforce a logout/login, add a button to a menu, and train all the users about when to press it. Or you'll need to find some other method to ensure that the new data is loaded each morning.

Your next inclination might be to have IT download the file as a comma-separated value (CSV) format file and to use the Access ISAM engine to link to the file directly. The format of the CSV file is simple, as Figure 13.3 shows.

Fig. 13.3

A file in comma-separated value (CSV) format has commas between the values and quotation marks around the strings.

To link to the file, create a link to the downloaded file in the Database window; Access then treats it like any other table. Figure 13.4 shows the Link Text Wizard in action against the file from Figure 13.3.

Fig. 13.4

The Link Text Wizard helps you establish links to non-Access data. The linked data can be used as a combo box RowSource.


You can link text files using program code with the TransferText method of the DoCmd object.

With the file linked to Access, you can base your form's combo box upon the table link. The flaw in this strategy is that the loading-dock workers keep the entry form open all of the time. In such a scenario, the following problem occurs:

To solve this dilemma, you must find a way to set the combo box's row source from the data while not locking the underlying table and file. You can use the following strategy:

  1. Add a combo box to your form, but do not bind the combo to the linked text file. Also, do not link the text file to your database at all, and do not enter any RowSource property for the combo box.
  2. Ask the IT personnel to change the format of the downloaded file to use semicolons as the item separator, instead of the commas shown in Figure 13.3. (Semicolons are the required delimiter for value lists that are used as the source for a combo box, which makes this file easier to use directly from your application.)
  3. Add code to the Enter event of the combo box to create the RowSource from the file on disk. The Enter event is used so that the list of values can be rebuilt each time the user moves to it, which guarantees that the latest data is always displayed. This ensures that the list is rebuilt from the daily download, even if the users never close the form and reopen it. In fact, even if the file on disk was changing minute-by-minute, using the Enter event would ensure that the list was rebuilt from the latest disk file every time the combo received focus.
  4. Add code to the Load event of the form to initially populate the list and to set a monitor variable to detect whether or not the file has been updated. Have your code check the file status against the monitor variable and only reload the combo box values if the file has changed.

In effect, you have set up a background process that teaches the combo box to watch its own data source and to rebuild its list from the data source only when IT copies a new file over the old file. Such a strategy would be much more difficult to implement using import routines to refresh a table because the form would have to be closed to run the routines, the database structure would be impacted as a table is deleted and rebuilt, and so forth.


When implementing any variation of this particular solution, be alert to data integrity and data sharing issues. My example in this section does not discuss issues such as accidental deletions of the file, bad data inside the file, sharing the file on a network, and so forth. Also, there are interesting philosophical issues about strategies that enable data to live outside of its database, mostly related to data ownership and centralization.

Listing 13.6 shows the code that drives this strategy.

Listing 13.6 AES_Frm1.Mdb-Loading and Refreshing a Combo Box from a Disk File

' In the Declarations section of the form module

Private mvarFileDate As Variant ' Preserve date of data file

Private mstrOrderFile As String ' Path and file name

' When the form loads, load the combo and set the monitor variable

Private Sub Form_Load()

' File must be in the same path as this database

mstrOrderFile = lci_DbsPathGet(CurrentDb) & "Order.Txt"

' Perform the initial load

Call cboOrder_Enter

' Preserve the starting stamp of the file

mvarFileDate = FileDateTime(mstrOrderFile)

End Sub

' Check the file status with each Enter event and refresh if needed

Private Sub cboOrder_Enter()

' Purpose: Reload the combo from the flat file

' Pseudocode:

' 1. If the file date/time has not changed, abort

' 2. Open the disk file

' 3. Fetch each line in the file and concatenate into one string

' 4. Set the combo source to the string

' 5. Set the monitor variable to the new file date/time

' See if the file date/time has changed

If mvarFileDate = FileDateTime(mstrOrderFile) Then

GoTo cboOrder_Enter_Exit ' Nothing to do

End If

Dim intFile As Integer ' File number

Dim strInp As String ' Line from the file

Dim strWork As String ' Build the RowSource

DoCmd.Hourglass True

' Load the file items into one string

intFile = FreeFile()

Open mstrOrderFile For Input As #intFile

Do Until EOF(intFile) ' Cycle thru all rows

Line Input #intFile, strInp

strWork = strWork & ";" & strInp ' Add the delimiter

Loop

Me!cboOrder.RowSource = Trim(Mid(strWork, 2)) ' Ignore the first ;

Me!cboOrder.Requery

mvarFileDate = FileDateTime(mstrOrderFile)

End Sub


This technique for loading a combo box from a file works with list boxes also.

Loading a Combo Box from Code

There are various data values that can be useful in an application that are difficult to maintain in a data table. One example is a list of database objects. Assume that you want to include on a form a list of all the forms in the current database. It will be challenging to keep all the current object names in a data table, and you can't expect a user or developer to maintain the list of forms accurately over time.

A more useful approach is to enable your code to query the Data Access Objects for the list of forms and to load this list into the RowSource property of the combo box. (See Chapter 12, "Understanding Form Structures and Coding," to learn how to get the names of all the forms in your database.)

Consider a second example, which was inspired by one of our clients. Assume your company has four hundred users running Access applications at various times every day. Each application has a need for the same five combo boxes on most forms, providing static information, such as state names and customer type codes. The company wants to provide the source data for these five combo boxes in one centralized database. For our purposes, I'll call this database the "lookup database."

Jet does its best to manage and minimize locking situations that can arise in an environment like this. For example, Bob opens a form in data entry mode. The form has five combo boxes, each of which is bound to a table in his database. However, each of the five tables is actually a link to a table in the lookup database. How many locks does Bob have on the lookup database?

In fact, he hasn't locked any tables yet because he hasn't opened any of the combo boxes on the form, and Access does not request the data to fill the combo boxes until it's demanded by a user event. However, after Bob opens each of the combo boxes, Access has placed a read lock on each of the tables in the lookup database, totaling five locks.

Now, imagine that each user in the company tries to open a form with combo boxes that read from the lookup database. You have two immediate problems, as follows:

Because I've already noted that the lookup information is mostly static, you have the following options to resolve this problem:

For the purposes of this example, proceed with the third option because it fulfills the goal of keeping a centralized lookup database. Because the lookup data is fairly static and the lists themselves are short, you can solve the problem by unbinding each form's combo boxes from the linked tables; then, create code in each form's Load event to populate the combo boxes from the linked tables. The code is simple, as Listing 13.7 shows.

Because the combo boxes now load from program code, any locks placed on their source tables exist only as long as the code is running rather than for the life of the form. This reduces the hypothetical server and Jet loads, described earlier, from hundreds of simultaneous locks to a handful-a significant improvement in server load and general performance.


As an added bonus, this strategy releases locks on the lookup database immediately after loading the combo box. This means that, overnight, no user workstations that still have the interface application open will hold any locks on the database, which enables any overnight processes (such as backup, updates, or repair and compact) to grab the exclusive database lock that they need.


Listing 13.7 AES_Frm1.Mdb-Code to Load a Combo Box from a Linked Table

Private Sub Form_Load()

' Purpose: Load combo box from a lookup database

Dim dbs As Database

Dim rsnp As Recordset

Dim strWork As String

Set dbs = CurrentDb

Set rsnp = dbs.OpenRecordset("tlkpStatus", dbOpenSnapshot, dbForwardOnly)

Do While Not rsnp.EOF

strWork = strWork & ";" & rsnp!Status

rsnp.MoveNext

Loop

Me!cboStatus.RowSource = Mid(strWork, 2) ' Ignore the first ;

rsnp.Close

dbs.Close

End Sub

To complete the example, remember the following few points:

Careful planning and coding of combo boxes can sometimes squeeze significant extra performance out of applications with a high user count.


The technique in this section for loading a combo box from code works with list boxes as well.

Retrieving Lookup Values with the Column Property

Sometimes when the user selects a value from a combo box, your application may require more information based on that value. For example, the user selects a person from a combo box and your form has to display the person's phone number. You might currently solve this problem by using a domain function, such as DLookup() or DFirst(), to retrieve the related value from a table. In such a scenario, assume you have a combo box, cboPeople, in which the RowSource property is equal to the following:

SELECT FirstName FROM tblPeople

When using the domain lookup approach, you retrieve the phone number by placing a function into a text box labeled Phone. The function, located in the ControlSource property, will look something like this:

=DLookUp("Phone","tblPeople","FirstName = '" & [Form]![cboPeople] & "'")

Because the domain function must go to the table to retrieve the lookup value, however, the performance of this method will not be instantaneous. Worse, if you need to grab more than one value from the target table-such as the phone number into one text box and the phone extension into another-you must run multiple domain lookups in succession, which has a negative impact on performance.

An alternative approach to domain functions-with increased performance and usability-is to bring the desired lookup data into the combo box as an additional column value when the list first loads. Then, using the Column property of the control, you can retrieve the additional value(s) as needed, even if they aren't displayed on the form.

To use the Column property, include the phone number column from tblPeople in the RowSource of the combo box. This ensures that the phone number for each person is fetched from the table when the combo box loads:

SELECT FirstName, Phone FROM tblPeople

You can suppress the display of the phone column in the combo by setting the ColumnWidths property to zero for the second column. Even though the column is not displayed, the value is still available in the Column property of the control. In this example, because Phone is the second column in the list and Access column indexing is zero-based, you can refer to the Phone value for a displayed FirstName by using an expression in the ControlSource of your Phone text box, as Figure 13.5 shows.

Fig. 13.5

Information in a combo box column is available to a text box via the .Column(n) syntax.

Compare the performance difference of the two methods. Using a domain lookup function to retrieve a value causes a query to run against a data table each time the function is executed, which occurs whenever the combo box value is changed or the form expressions are recalculated. By contrast, if the lookup data is pulled into the combo box list, all phone numbers are fetched in one pass from the table as the combo box loads. The latter approach runs much faster.


This example placed the Column reference in the ControlSource property of a text box. Access will recalculate the expression in the text box as the combo box value changes. Instead, if you want more control over when the value assignment occurs, you can use the Column property in an expression in the form's code, usually in the AfterUpdate event of the Combo Box control. When the value for the text box is fed from your code, you can design your form with the text box as an editable or non-editable control. By contrast, when the text box value is fed by an expression in its ControlSource, the control is always made read-only by Access.

The real power of this technique is due to the fact that you can pull multiple columns of data into your combo box when it loads, and reference any or all of the values in code or in expressions on the form. This technique saves multiple domain lookups when a new combo box value is selected, and it greatly enhances form performance.


The Column property is also available on List Box controls where it can be used in the same manner as described in this section.


Direct Addressing to Combo Box Values

Access now has the capability to address a specific combo list row. This makes the combo box into an array of addressable values. However, the syntax is convoluted compared to an array because the row index comes after the column index:

controlname.Column(column,row)


The indexing for the Column property is by column and row, rather than the commonly accepted row and column, because the row index was added to Access only recently (Access 95) and it was necessary to maintain backward compatibility.

This addressing scheme enables you to extract any value from a combo box once you know its location. Listing 13.8 shows a procedure that loops through the values in any combo box and dumps the value matrix to the debug window. The results of running this procedure against a simple name and phone number combo are shown in Figure 13.6.

Listing 13.8 AES_Frm1.Mdb-Looping Through a Combo Box As If it Were an Array

Public Sub CtlDump(ctl As Control)

' Purpose: Dump the contents of a combo box or list box 'array'

Dim iintCol As Integer

Dim iintRow As Integer

For iintRow = 0 To ctl.ListCount - 1

For iintCol = 0 To ctl.ColumnCount - 1

Debug.Print ctl.Column(iintCol, iintRow),

Next iintCol

Debug.Print

Next iintRow

End Sub

Fig. 13.6

The result of running the code in Listing 13.8 to loop through all the values of a Combo Box control.

Now, let's find a practical application for this feature. Assume you have a combo box that lists the name and department of all employees. When the user of your form selects a particular employee, you want a Text Box control to display the total number of employees in the same department as the selected employee. The code in Listing 13.9 will achieve the desired result by employing the looping logic previously described. (The department name is in the second column (ctl.Column(1) in the combo box.)

Listing 13.9 AES_Frm1.Mdb-Looping Through Values in a Combo Box

Private Sub cboEmpe_AfterUpdate()

' Purpose: Count the employees in the selected department

Dim cbo As ComboBox

Dim intCount As Integer

Dim iintRow As Integer

Set cbo = Me!cboEmpe

For iintRow = 0 To cbo.ListCount - 1

' Compare each row to the selected row

If cbo.Column(1, iintRow) = cbo.Column(1) Then

intCount = intCount + 1

End If

Next iintRow

Me!txtDeptCount = intCount

End Sub

As with other uses of the Column property, the big win in this listing is performance. Previously, you would have had your code run a grouping query or DCount() function to count all of the members of a specific department. The technique shown here produces the same result without running the query engine, and without accessing any index or data pages in the database.


The row offset argument described in this section is also available in the Column property of List Box controls.

Using One Combo Box to Replace Several

The following technique demonstrates how to replace several combo boxes with Text Box controls, with the text boxes sharing only a single combo box instead. As with other techniques in this chapter, the primary objective of this strategy is to add performance to forms that are not loading or running as quickly as desired.

The example uses the sample data in Figure 13.7, and is described as follows:

Fig. 13.7

These sample GL codes, GL sub-codes, and GL department codes belong to the hypothetical company in this section's examples.

A standard approach to building a form to collect accounting information around this data structure would include three combo boxes, one each for GL Code, GL Sub-Code, and GL Department Code. The performance problem arises because there is a dependency between the GL Code combo and the GL Sub-Code combo (for each GL Code there is a valid set of sub-codes), and a dependency between the GL Code/Sub-Code combination and the GL Department codes (for each GL Code/Sub-Code pair there is a valid set of GL Department codes).

With a dependency between combo boxes, you must requery a dependent control whenever the value of its parent changes. For example, the following code is required in the GL Code combo box's event model to requery the lists in its two dependents:

Private Sub cboGL_AfterUpdate()

' Purpose: Requery dependent controls

Me!cboSub.Requery

Me!cboDept.Requery

End Sub

To express the dependency from the child's side, each control that relies on cboGL must include that control in its criteria selection. For example, the RowSource property for the cboSub control looks like this:

SELECT SubCode, SubCodeDesc

FROM tlkpGLSubCode

WHERE GLCode=[Forms]![frmCboConsolidate]![cboGL]

ORDER BY SubCode;

Each requery takes time, and with multiple dependencies in place, the form makes several requeries per record.


To offset the negative performance of dependent combos, each such combo box provides the user with a carefully filtered list of codes and values, which clearly identifies the permitted data items. Thus, increased usability derives from decreased performance, and this trade-off might be acceptable in some applications.


An alternative approach to dependent combo boxes is to enable the entry of the GL data into text boxes instead. This method presumes that most users, most of the time, will know the values they want to enter, thereby reducing the need for a limited combo list. However, the user should not be asked to live without a combo box completely because sacrificing usability for performance is not usually a good strategy.

The form in Figure 13.8 contains a Text Box control for each of the required values for GL Code, Sub-Code, and Department. Next to the text boxes, the form also contains a button with a Click event that displays a combo box of compounded values on top of the text boxes (see Figure 13.9). The combo box must contain all of the valid combinations for the three types of codes because it will be used in the following two ways:

Fig. 13.8

This form contrasts the two approaches described for entering compound values-using combo boxes and using text boxes.

Fig. 13.9

This combo box feeds values to, and helps to validate, the text boxes hidden below it.

The flow of this form (specifically, the current example as shown in the lower half) is as follows:

  1. The user enters values into the text boxes for GL Code, Sub-Code, and Department. You place VBA code behind the text boxes to validate the entered data against the hidden combo box, as shown in Listing 13.10. By validating against the combo box, validation is performed in memory and no disk access to a table or query is undertaken. (The columns with index values 4, 5, and 6 in the combo box hold the GL codes, sub-codes, and department codes respectively.)

Listing 13.10 AES_Frm1.Mdb-Validating Text Box Values Using a Compound Combo Box

Private Sub txtDept_AfterUpdate()

' Purpose: To validate the combination of GL, Sub, Dept

' This validation example is not comprehensive,

' because it only validates on the update of one of the

' three related text boxes, to demonstrate the technique.

' In production you would place it in Form_BeforeUpdate.

Dim blnFound As Boolean

Dim cbo As ComboBox

Dim iintLoop As Integer

Set cbo = Me!cboCombined

For iintLoop = 0 To cbo.ListCount - 1

If cbo.Column(4, iintLoop) = Me!txtGL Then

If Me!txtSub = cbo.Column(5, iintLoop) _

And Me!txtDept = cbo.Column(6, iintLoop) Then

blnFound = True

Exit For

End If

End If

Next iintLoop

If Not blnFound Then

Beep

MsgBox "Combination of codes is not valid.", vbExclamation, "GL"

End If

End Sub

  1. When the user clicks the drop-down command button, code runs to hide the text boxes and to display the combo box in their place. Alternately, you can display the combo box above or below the text boxes.
  2. When the user makes a selection from the combo box, the individual values are parsed and used to feed the dependent Text Box controls, as Listing 13.11 shows.

Listing 13.11 AES_Frm1.Mdb-Sending Compound Combo Box Values to Dependent Controls

Private Sub cboCombined_AfterUpdate()

' Purpose: Send compound value into dependent controls

If Not IsNull(Me!cboCombined) Then

Me!txtGL = Me!cboCombined.Column(4)

Me!txtSub = Me!cboCombined.Column(5)

Me!txtDept = Me!cboCombined.Column(6)

End If

End Sub


The code to hide and show controls is not listed in this section. See the examples in the database AES_FRM1.MDB on the CD-ROM.


This technique tries to walk a very narrow line between two objectives that are often mutually exclusive: to provide power users and rapid data entry personnel with validation that doesn't slow their use of the form, and to maintain a way for unsophisticated users to get extra assistance when they need it.

Tricks like this provide increased performance to your forms. However, because they change the usability model of the form, you should be sure that users can understand and be comfortable with such nonstandard approaches to data entry.

Loading a Combo Box from a List Callback Function

If your applications require a combo box's value list to be filled with data that isn't available in a table or query, Access provides another mechanism beyond using value lists in the RowSource property. The technique involves creating a user-defined function to feed values to the combo box. The function is created as code in the form's module or in a standard module. To use the function, enter its name into the RowSourceType property of a Combo Box control.

A function used in this fashion must be coded to an exact structure, which enables Access to call it repeatedly, in order to fetch the values for the combo box. Programmers commonly use the term callback function to describe a procedure that is called repeatedly, so I will use the term list callback function to describe the technique here. (Microsoft called this type of function a list function in Access 1 and 2 documentation; other common terms for this technique used in the Access community include fill-list function and a list-fill function.)

To apply a list callback function, enter the name of the function in the RowSourceType (not the RowSource) property of a combo box, minus an equal sign and any arguments. Figure 13.10 shows a combo box with the RowSourceType property set to a callback function name.

Fig. 13.10

A user-defined list callback function is attached to a combo box by entering its name in the RowSourceType property in the property sheet.

Structuring a List Callback Function

A callback function used with a combo box must follow a very specific model, or Access can't use it. The restrictive model means that Access can call the function expecting an action to happen, and the function will trigger the action on cue. The following are the various actions that occur in the callback function:

  1. Access initializes the function, which creates the structure of the list data (in rows and columns).
  2. The function tells the combo box how wide to display each column and what the row count is.
  3. The combo box calls the function repeatedly to fetch values to load into its list. Then, the combo box may still continue to call the function to get more values as the user moves the cursor or browses the control's list.
  4. The combo box calls the function one last time to perform cleanup when the form is closed.

The function declaration for a list callback function must have these arguments:

Function functionname(ctl As Control, varID As Variant, varRow As Variant _

, varCol As Variant, varCode As Variant) As Variant


I used my own argument names in the syntax example, but you can use any valid variable names. The variable data types and their positional location, however, must not be changed.

The values for the arguments to the callback function are explained in Table 13.2.

Table 13.2 Elements of a List Callback Function

Argument

Description

functionname

The name of the function. You can use any valid name. Note that the return value must be Variant.

ctl

A variable of type Control, which points to the target combo box.

varID

Access creates a unique ID for each control calling the function, so that multiple controls can share the same function.

varRow

The row number that the combo box tries to fill.

varCol

The column number that the combo box tries to fill or format.

varCode

Access sends a code to the function with each call, specifying the type of action for the function to perform.


Access doesn't seem to notice if you declare an actual control object class as the first argument, as in the string cbo As ComboBox instead of ctl As Control, so you can safely use this more explicit syntax if you choose.

An interesting feature of this type of function is that you call it from the RowSourceType property by its name only and you aren't permitted to pass in the arguments shown. Although the arguments are not listed in the property sheet, the control actually passes them back and forth to the function itself.


The row and column numbers (varRow and varCol in the example) are zero-based numbers, like addressing values in a Combo Box control.

Because Access must initialize, fill, and then maintain the combo box list via your function, the combo box calls the function one or more times for each possible value of the varCode argument. Depending on the varCode passed in, your function executes a specified action and sets the return value of the function to describe the result of the action to the combo box.

Listing 13.12 shows a rough outline of a list callback function to illustrate how the function's structure depends on the varCode values coming from the control.

Listing 13.12 AES_Frm1.Mdb-An Outline of a List Callback Function's Structure

Private Function cbkCallbackShell(cbo As ComboBox, varID As Variant _

, varRow As Variant, varCol As Variant, varcode As Variant) _

As Variant

' Purpose: A code shell for a list callback function

Select Case varcode

Case acLBInitialize ' Initialization

' Initialize here

Case acLBOpen ' When the control is opened

' Create a control ID here

Case acLBGetRowCount ' How many rows?

' Set the number of rows here

' ...More varCode Case tests go here

Case acLBEnd ' Close down the function

' Cleanup here

End Select

End Function

The following varCode values are passed in to a list callback function. Each code value is represented by an intrinsic constant in Access, as shown parenthetically. Codes 2 and 8 are not used by the function nor documented by Microsoft, and they are not listed here.


Most programmers simply use the Timer function to create a unique ID for the control because this function does not create any duplicate numbers during one day (the 24-hour period from midnight to midnight).



Your code can simply return the column count of the control back to it by using the syntax ctl.ColumnCount.


A twip is a unit of screen measurement. There are 1,440 twips in an inch and 567 in a centimeter. If your list's width measurements are in inches, multiply the value in inches by 1,440 to get a value in twips.


If the function sets the return value to Null or any invalid value for any varCode value passed in to your function, the combo box will not call your function again with the same code value. This gives you the capability to discontinue certain action calls to the function by switching them off. However, it also affords an opportunity for confusion when the return value is set improperly through a coding error-your function may no longer work as expected.


List callback functions are too complicated and their capabilities too powerful to explain them outside the context of a real-life example. The next two sections provide such examples.


The technique for creating functions to provide data for Combo Box controls works equally well with list boxes. As with combo boxes, the name of your custom function goes in the RowSourceType property of your list boxes.

Using a List Callback Function with One Control

Your list callback function must feed data back to the calling control, as requested, using row and column coordinates. Thus, your function must have data available in an addressable row and column format. Most commonly, such a structure is provided by an array within the function. When a varCode of zero is passed to your function to trigger initialization, your code must fill the array so that the values are available for all subsequent passes through the function.

Although filling an array from a table is straightforward, your application should probably not be using a callback function in the first place if the data source for the combo box is a table. Simply use the table directly (or a query that filters it) as the RowSource of the combo box.


The exception to the preceding paragraph occurs when you want to load a combo box from a table, but for performance reasons you do not want Access to maintain a link to the table. Thus the technique you learned for tuning lookup database access, in "Loading a Combo Box From Code" earlier in this chapter, is a good candidate for a callback function. The callback would load its array from a lookup table during initialization and then disconnect from the lookup database.

The real worth of a callback function is determined by the fact that it can provide values to a combo box that can only be gathered via program code. What follows are a few situations where a callback function is quite valuable:

Before delving into a functional example, study Listing 13.13, which shows a code shell outlining the basic structure of a callback function. As you review the shell, note the following points:

Read the listing carefully and be sure that you understand the components of a callback function before you proceed. It is apparent from the listing that a callback function is very rigidly structured.

Listing 13.13 AES_Frm1.Mdb-A Code Shell for a Simple List Callback Function

Private Function cbkCallbackShell(cbo As ComboBox, varID As Variant _

, varRow As Variant, varCol As Variant, varCode As Variant) As Variant

' Purpose: A code shell for a list callback function

' Placeholders are in <brackets>, this code does not run

Static sastrCallback() As String ' Array for data values

Select Case varCode

Case acLBInitialize ' Initialization

'<Fill array sastrCallback() here>

cbkCallbackShell = True ' Okay to proceed

Case acLBOpen ' When the control is opened

cbkCallbackShell = Timer ' Create a unique control ID

Case acLBGetRowCount ' How many rows?

'cbkCallbackShell = <n> ' Number of rows

Case acLBGetColumnCount ' How many columns?

cbkCallbackShell = cbo.ColumnCount ' Echo back the property value

Case acLBGetColumnWidth ' How wide are the columns?

Select Case varCol

Case 0 ' First column

'cbkCallbackShell = <twips> ' Set the width

'<Add more cases here>

'Case <n>

End Select

' Alternately, use the defaults

'cbkCallbackShell = -1 ' Use the default widths

Case acLBGetValue ' Fetch one data item

cbkCallbackShell = sastrCallback(varRow, varCol)

Case acLBGetFormat ' Format one data item

' cbkCallbackShell = "<formatstring>"

Case acLBEnd ' Close down the function

End Select

End Function


This code shell is included in AES_FRM1.MDB on the CD-ROM as VBA code and can be used as a starting point for creating your own callback functions. A working list callback function is also included in the database.

Now, with the groundwork established, you can review an actual callback function that runs. The example in Listing 13.14 shows a function that fills a combo box with the property names and values for the SummaryInfo properties stored by Access in the database.

Listing 13.14 AES_Frm1.Mdb-A List Callback Function that Fills a Combo Box With Database Properties

Private Function cbkSumInfo(cbo As ComboBox, varID As Variant _

, varRow As Variant, varCol As Variant, varCode As Variant) As Variant

' Purpose: Callback to fill a combo box with database summary info

' Arguments: cbo:=Control handle

' varID:=Unique control ID

' varRow:=Row number

' varCol:=Column number

' varCode:=Index to action item

Dim dbs As Database

Dim doc As Document

Static sintRow As Integer

Static sastrPrp() As String ' Array for data values

Select Case varCode

Case acLBInitialize ' Initialization

' Fill the array

Set dbs = CurrentDb

Set doc = dbs.Containers!Databases.Documents!SummaryInfo

ReDim sastrPrp(doc.Properties.Count, 2) ' Set the array size

For sintRow = 0 To doc.Properties.Count - 1 ' Get each property

sastrPrp(sintRow, 0) = doc.Properties(sintRow).Name

sastrPrp(sintRow, 1) = doc.Properties(sintRow).Value

Next sintRow

cbkSumInfo = True ' Okay to proceed

Case acLBOpen ' When the control is opened

cbkSumInfo = Timer ' Create a unique control ID

Case acLBGetRowCount ' How many rows?

cbkSumInfo = sintRow ' Number of rows

Case acLBGetColumnCount ' How many columns?

cbkSumInfo = cbo.ColumnCount ' Echo back the property value

Case acLBGetColumnWidth ' How wide are the columns?

Select Case varCol

Case 0 ' First column

cbkSumInfo = 1500 ' Set the width

Case 1 ' Second column

cbkSumInfo = 3500 ' Set the width

End Select

Case acLBGetValue ' Fetch one data item

cbkSumInfo = sastrPrp(varRow, varCol)

Case acLBGetFormat ' Format one data item

cbkSumInfo = cbo.Format ' Echo back the property value

Case acLBEnd ' Close down the function

End Select

dbs.Close

End Function

Figure 13.11 shows the results of running the listed code.

Fig. 13.11

A user-defined list callback function loads database summary information into this combo box.

It is instructive to trace exactly how a callback function is called by Access. To illustrate the flow of a callback function in use, I placed Debug.Print statements throughout the function in Listing 13.14. It shows the sequence of varCode values passed to the function as its form opens and the combo box loads. Table 13.3 shows the results of the trace.


I limited the trace function to retrieve only the first two database properties in order to keep the example simple. Thus, the resulting array was two rows by two columns (indexed as 0,0 to 1,1). The trace function code is not shown here, but is in AES_FRM1.MDB on the CD-ROM.

Table 13.3 Tracing a Callback Function

User Action

varCode

varRow

varCol

Form open

acLBInitialize



Form open

acLBOpen



Form open

acLBGetColumnCount



Form open

acLBGetColumnWidth


0

Form open

acLBGetColumnWidth


0

Form open

acLBGetColumnWidth


0

Form open

acLBGetColumnWidth


0

Form open

acLBGetColumnWidth


0

Open combo

acLBGetColumnWidth


0

Open combo

acLBGetColumnWidth


1

Open combo

acLBGetColumnWidth


0

Open combo

acLBGetRowCount



Open combo

acLBGetValue

1

0

Open combo

acLBGetValue

0

0

Open combo

acLBGetFormat


0

Open combo

acLBGetValue

0

1

Open combo

acLBGetFormat


1

Open combo

acLBGetValue

1

0

Open combo

acLBGetFormat


0

Open combo

acLBGetValue

1

1

Open combo

acLBGetFormat


1

Form close

acLBEnd



The values from the trace in the table should clarify exactly how Access uses your function code. Note the "better safe than sorry" approach Access takes in calling your function more than is really required to fetch and format each row.

Once you grasp the essential structure and flow of callback functions, you will find myriad ways to use them in your Combo and List Box controls.

Using a List Callback Function with Multiple Controls

If a list callback function can be used to replace the execution of a record fetch from a table or via a query, the performance of your form is almost always improved. On a larger scale, imagine the benefits of replacing several combo box queries with a single function. The performance benefits can be exemplary.


The following example is what we call "double-latté code" in Seattle. (A latté is a double-strength coffee espresso drink.) In other words, don't try to follow this code unless you're wide awake! Also, if you have not already done so, you should read the preceding two sections on callback functions and review the callback examples on the CD-ROM before you proceed.


You may recall that the second block in a callback function (where varID = acLBOpen) asks your function to provide an ID value for the calling control. This enables the function to uniquely identify a caller by its ID and to pass back the appropriate values. Such a structure enables your function to service more than one Combo Box control, as long as your code is set up to recognize the different callers (by ID values).


The following example is from an application we created for a client. When we took a form with a dozen Combo Box controls, each fed by a query, and implemented the callback strategy described here on all of the combo boxes, the average load time of the form decreased by seventy percent.

For this example, assume that you are creating an application for a medical clinic, and that the application contains a form to log test results. The form has one combo box for each of the following six values:

Normally, the values for combo boxes such as these would come from six lookup tables, such as tlkpArea, tlkpDiagnosis, and so forth. In addition, you would use the lookup table names in the RowSource properties of the six Combo Box controls. However, such a structure requires the form to create multiple recordsets against the back-end database, one for each lookup table/combo box combination. A better strategy is for the form to access the lookup data once, saving five "hits" on the back end.

To employ such a solution, structure the application as follows:

  1. Combine the lookup values into one table so that they can be retrieved with one fetch to the back end.
  2. Create an array behind the form to contain all of the lookup values.
  3. Load the six combo boxes from the array using a callback function.

Figure 13.12 shows the single table structure that supports this method. Each lookup value (the Code field) must have a Type designation in the combined table so your function can tell the six value types apart.

Fig. 13.12

This lookup table combines and codifies multiple types of values, as opposed to creating a separate lookup table for each type.

Next, you must declare the necessary array structures in the form's module, as shown in Listing 13.15.

Listing 13.15 AES_Frm1.Mdb-Declaring a Single Array for Multiple
Boxes

Option Base 1 ' This code must be 1-based

Option Compare Database

Option Explicit

' mastrCode holds the lookup values Type and Code

' The array looks like this:

' TYPE CODE

' Area Lab

' Diagnosis Myocard

Dim mastrCode() As String

' mastrControl holds indexes for the lookup array

' The columns are: Type, count of type, starting array index

' for type, ending array index for type

' The array looks like this:

' TYPE ELEMENTS START END

' Area 4 1 4

' Diagnosis 6 5 10

Dim mastrControl() As String

Dim mblnArrayLoaded As Boolean

Note the following items of interest in the listing:

(x,1)

(x,2)

Area

Lab

Area

MRI

Diagnosis

Myocard

Diagnosis

Normal Heart

Fig. 13.13

This control array includes one item to index each unique Type code in the related lookup table.

The next task is to load the data array. This will make the lookup values available to the callback function because the array has module-level scope. The code for the cbfArrayLoad() function that loads the array is shown in Listings 13.16 through 13.18; the three pieces in the listings combine into the single function.

Listing 13.16 shows the startup portion of the array load function. Notice that the size of the data (mastrCode) array is set to equal the number of records returned from the lookup table.

Listing 13.16 AES_Frm1.Mdb-Sizing a Data Array Based on its
Source Recordset

Private Function cbfArrayLoad() As Boolean

' Purpose: Load the lookup code and control arrays

Dim dbs As Database

Dim intCRow As Integer ' Current control array row

Dim intDRow As Integer ' Current data array row

Dim rst As Recordset

Dim strType As String ' Current Type value

' Load the array of lookups

Set dbs = CurrentDb

' The query sorts the data by Type, then Code

Set rst = dbs.OpenRecordset("qlkpVisitCode", dbOpenDynaset)

rst.MoveLast

ReDim mastrCode(rst.RecordCount, 2) ' Size the array

rst.MoveFirst

intCRow = 0

intDRow = 1

strType = ""

In Listing 13.17, each record in the lookup table is inserted into the data array. Each time a new value for the Type field is detected in the lookup table data, a counter (intCRow) is incremented. The counter is used to determine the size for the control array later in the code (see Listing 13.18).


Although the count of unique Type values in the lookup table can also be retrieved with another Recordset object running against a grouping query, operations on small arrays usually run faster than accessing a data table in the back-end database does.


Listing 13.17 AES_Frm1.Mdb-Loading a Data Array From a Recordset

Do While Not rst.EOF

' Load the array items from the table

mastrCode(intDRow, 1) = rst!Type

mastrCode(intDRow, 2) = rst!Code

' Count the number of unique Type items

If rst!Type <> strType Then

strType = rst!Type ' Reset the marker

intCRow = intCRow + 1 ' Increment the count

End If

intDRow = intDRow + 1

rst.MoveNext

Loop

The size of the control array is set in Listing 13.18, and the four array columns are loaded. While making a pass through the entire data array, the code captures unique instances of the Type value, stores the beginning and ending array index for each Type, and computes the count of items for the Type. These values are stored in the control array (refer back to Figure 13.13).

Listing 13.18 AES_Frm1.Mdb-Populating a Control Array That Indexes
a Different Array

' Size the array based on the count of unique Types

ReDim mastrControl(intCRow, 4)

' Review the code array and place the first occurrence of each

' Type value in the index array, along with starting and

' ending indexes

intCRow = 0

strType = ""

For intDRow = 1 To UBound(mastrCode)

If mastrCode(intDRow, 1) <> strType Then

If intCRow > 0 Then ' Skip on the first pass

' Plug the values in the control array

mastrControl(intCRow, 4) = (intDRow - 1) ' Ending index

' Set the number of elements

mastrControl(intCRow, 2) = (intDRow - CInt(mastrControl(intCRow, 3)))

End If

intCRow = intCRow + 1

strType = mastrCode(intDRow, 1) ' Reset the marker

mastrControl(intCRow, 1) = strType ' New Type code

mastrControl(intCRow, 3) = intDRow ' Starting index

End If

Next intDRow

' Complete the last index

mastrControl(intCRow, 4) = (intDRow - 1) ' Ending index

' Set the number of elements

mastrControl(intCRow, 2) = (intDRow - CInt(mastrControl(intCRow, 3)))

cbfArrayLoad = True

rst.Close

dbs.Close

End Function

After the procedure in these listings has run, the form module has loaded all of the lookup values into a single data array in memory, and it has created a control array with all of the index information that's needed to retrieve specific lookup values from the data array.

The next task is to create the common callback function that will feed the six combo boxes on the form. The name of the function (cbkCode) is placed in the RowSourceType property of each of the six combo boxes because they share the routine. In addition, I place a marker value in the Tag property of each of the combo boxes that describes the Type code value that will feed the combo's list. For example, in the combo box that will load the area values, place Area in the Tag. This value will be used by the callback function.

The shared callback function cbkCode is structured using the standard callback layout described previously. The following descriptions illustrate the blocks of code from the function for the most important varCode values:

Case acLBInitialize ' Initialization

' Load the array (module-level) if not loaded

If Not mblnArrayLoaded Then

cbkCode = cbfArrayLoad()

mblnArrayLoaded = True

Else

cbkCode = True

End If

Case acLBOpen ' When the control is opened

' The control's ID will be set to the index into the control array

' For example, if the code Diagnosis is element 2 in the control

' array, the ID of cboDiagnosis is set to 2

For iintLoop = 1 To UBound(mastrControl)

If mastrControl(iintLoop, 1) = Trim(cbo.Tag) Then

cbkCode = iintLoop

Exit For

End If

Next iintLoop

Case acLBGetRowCount ' How many rows?

' Number of rows for a Type are in the control index

cbkCode = CInt(mastrControl(varID, 2))

Case acLBGetValue ' Fetch one data item

' Compute the offset for the location of the data item

' in the code array, note that varRow is 0-based

intRow = mastrControl(varID, 3) + varRow

cbkCode = mastrCode(intRow, 2)

A complete, working version of the callback function cbkCode is on the CD-ROM.

Figure 13.14 shows the destination combo boxes on a form, and the single lookup table that provides the values for all of the combo boxes via the code described in this section.

Fig. 13.14

A single lookup table provides values for the six combo boxes on this form via a shared list callback function.

On forms with many Combo Box controls, this technique can save a lot of time when loading the form. In addition, passing table data through a function as it loads into a combo box enables you to perform auditing, formatting, or other code processes on the lookup values before the user sees them.


When using my example code to feed List Box controls instead of Combo Box controls, change the callback function argument cbo As ComboBox to lst As ListBox, and change other code that uses cbo accordingly.

Creating Expert List Boxes

List boxes are as useful as combo boxes, and in many cases the decision to use one over the other is a philosophical issue. Because the difference in the use of these controls is less pronounced than the difference in form space consumed by them, combo boxes are often used simply because insufficient space exists for a list box.

Combo Box and List Box controls share an almost identical property set. You probably noticed in earlier sections of this chapter that most of the expert forms techniques that were described for combo boxes will also work with list boxes.

The following sections provide two techniques for using list boxes more effectively. The techniques are unique to list boxes and do not apply to combos.

Using Multi-Select List Boxes

Starting with Access 95, you had the ability to select more than one item in a list box on a form, which is very useful in your applications. Using familiar keyboard and mouse techniques, your users select any combination of items on a list; then, your program code can determine the values in their selection.


You can't multi-select within a Combo Box control.

The following listing briefly summarizes the new properties and methods added to List Box controls to support multiple selection.

lst.Column(lst.BoundColumn - 1, 2)

lst.ItemData(2)

The remainder of this section provides two code listings that you will find useful in the following ways:

Fig. 13.15

The multi-select list box on this form provides delimited strings that are saved to a related data table.

Saving Multi-Selected Values to a Table

For the following routines, assume that customer profiles are created when you enter a customer record into your application. The customer profile includes a field holding one or more values that reflect the customer's delivery preferences. You select these values from a list box, and the code stores them in the database field Delivery with commas between them-for example: "UPS Blue,USPS."

Listing 13.19 shows how to grab multiple selections from a list box and create a delimited string of those values. Note the following important elements in the listed routines:

Listing 13.19 AES_Frm1.Mdb-Creating a Delimited String of Selected
List Box Values

' A procedure in the data entry form

Private Sub lstDelivery_AfterUpdate()

' Purpose: Create list for table field from selected list box items

Me!txtDelivery = lci_LstItemSelDelim(Me!lstDelivery, ",", 0)

End Sub

' A library function in a standard module

Public Function lci_LstItemSelDelim(rctl As Control _

, rvarDelim As Variant, rintCol As Integer) As Variant

' Purpose: Return a delimited string with all selected items

' from a list column

' Arguments: rctl:=List box control

' rvarDelim:=Delimiter to place between items

' rintCol:=Column number

' Returns: Delimited list or Null

' Example: lci_LstItemSelDelim(Me!lstPlace, ";", 1)

Dim varRet As Variant ' Build return value

Dim evarRow As Variant ' Element in ItemsSelected

varRet = Null

For Each evarRow In rctl.ItemsSelected ' Cycle thru all selected items

If IsNull(varRet) Then ' Trap first pass

' Start return string

varRet = rctl.Column(rintCol, evarRow)

Else

' Build return string

varRet = varRet & rvarDelim & rctl.Column(rintCol, evarRow)

End If

Next evarRow

lci_LstItemSelDelim = varRet

End Function


The routine lci_LstItemSelDelim() returns a delimited list of values, such as "UPS Blue,UPS Red,USPS." Storing the list in a table field is one way to turn multi-select data into table data. Another way is to take each item in the selected list and write a record for the item in a table that is a child of the form's primary data table.

Selecting Multiple List Values from Table Data

For this section, assume that the delimited list of user shipping preferences created in the previous section is saved in the Delivery table field. The next routine reverses the process of saving the data by fetching the delimited data into a list box.

Assume that you want your form to display in a list box the multi-selected values that are saved in the Delivery field. Your code must dissect the table field when moving to a different record (as reflected in the Form_Current event) and select the appropriate list box items. The routines in Listing 13.20 show how to select multiple items on a form list box based on passed arguments. In this example, the arguments are items from the comma delimited list stored in the table field using the format described in the previous section: "UPS Blue,UPS Red,USPS."

Listing 13.20 also demonstrates the following list box and VBA techniques:

Listing 13.20 AES_Frm1.Mdb-Selecting Specific Values in a List Box

' An event in the form

Private Sub Form_Current()

' Purpose: Select items in the list box based on table data

Dim blnRet As Boolean

' Clear the list by setting all selections to False

blnRet = lci_LstItemSetAll(Me!lstDelivery, False)

' Select specific items in the list by their bound value

' The table data item has up to four delimited values

blnRet = lci_LstItemSetByValue(Me!lstDelivery, True, _

lci_DelimItemGet(Me!txtDelivery, ",", 1), _

lci_DelimItemGet(Me!txtDelivery, ",", 2), _

lci_DelimItemGet(Me!txtDelivery, ",", 3), _

lci_DelimItemGet(Me!txtDelivery, ",", 4))

End Sub

' A library function in a standard module

Public Function lci_LstItemSetAll(rctl As Control _

, rblnSet As Boolean) As Boolean

' Purpose: (Un)select all items in a list

' Arguments: rctl:=List box control

' rblnSet:=Setting: True/False

' Returns: True/False, True=success

' Example: lci_LstItemSetAll(Me!lstPlace, True)

Dim iintLoop As Integer ' Index into list box

For iintLoop = 0 To rctl.ListCount - 1 ' Cycle thru the list

rctl.Selected(iintLoop) = rblnSet ' Set item selection

Next iintLoop

End Function

' A library function in a standard module

Public Function lci_LstItemSetByValue(rctl As Control _

, rblnSet As Boolean, ParamArray ravarItems()) As Boolean

' Purpose: (Un)select specific list items by ItemData value

' Arguments: rctl:=List box control

' rblnSet:=Setting: True/False

' ravarItems:=Array of values to select

' Returns: True/False, True=success

' Example: lci_LstItemSetByValue(Me!lstPlace, True, "ID", "KS")

Dim iintRow As Integer ' Index into list box

Dim evarItem As Variant ' Element in ParamArray

For iintRow = 0 To rctl.ListCount - 1 ' Cycle thru the list

For Each evarItem In ravarItems ' Cycle thru the ParamArray

If evarItem = rctl.ItemData(iintRow) Then ' Found a match

rctl.Selected(iintRow) = rblnSet ' Set item selection

Exit For

End If

Next evarItem

Next iintRow

lci_LstItemSetByValue = True

End Function


The library routine lci_DelimItemGet() is called by code in the listing to parse a passed list and return the nth item. The code is not listed here, but is available in AES_FRM1.MDB on the CD-ROM.

Note that the list box routines in this and the preceding section are very generic: they can be called with a variable number of arguments, they can be passed to any List Box control object, and they can be used to work with either selected items or unselected items. Such routines follow the strategies for good reusable components as described in Chapter 11, "Expert Approaches to VBA," and Chapter 20, "Applying Development Shortcuts."

Reordering Values in a List Box

When your form must give users the ability to view items in a list and to rearrange the order of the items, the most common method in the past was to show users a subform of listed items and to require the users to place a unique sequence number into each record to sequence the values. However, List Box controls now provide a good mechanism for enabling the user to reorder a displayed list of values. Compared to the subform method, a list box provides both better performance and better usability.

Enabling a user to reorder items in a list box involves the use of the list callback function technique described earlier in this chapter. Review the section, "Loading a Combo Box from a List Callback Function," if you have not already done so.

As an example, we'll start with the list of shipping methods shown in the previous section, and we'll permit our user to view the various delivery options for a customer. Unlike the previous example, that selects a specific set of options for each customer, in this example all options are considered valid for each customer. The user's only job is to order the options on a per-customer basis, to reflect the customer's stated preferences for shipping priorities.

The form for this example is shown in Figure 13.16. The basic structure of this form is as follows:

Fig. 13.16

This list box provides values that can be reordered by using the related command buttons. The ordered items are stored in delimited strings in a data table.

There are several code routines that are essential to this technique. The first are shown in Listing 13.21, which presents the following steps:

Listing 13.21 AES_Frm1.Mdb-Supporting Routines for the Delivery List
Array

' A declaration in the Declarations section of the form module

Private mavarDelivery() As Variant ' Delivery options

' The Load event for the form

Private Sub Form_Load()

' Purpose: Initialize the array

Call cbfListLoad(Me!lstDelivery, "FedEx", "UPS Blue", "UPS Red" _

, "USPS")

End Sub

' Routine in the form module to populate the array

Private Sub cbfListLoad(ParamArray ravarList() As Variant)

' Purpose: Load the items into the delivery list

' Arguments: ravarList:=One or more items to load

Dim iintLoop As Integer

ReDim mavarDelivery(UBound(ravarList) - 1)

For iintLoop = 1 To UBound(ravarList)

mavarDelivery(iintLoop - 1) = ravarList(iintLoop)

Next iintLoop

End Sub

When the array is initially populated, it is used as the basis of the list callback function shown in Listing 13.22. This function feeds the array values to the list box.

Listing 13.22 AES_Frm1.Mdb-List Callback Function to Manage the
Delivery List

Private Function cbkDelivery(lst As ListBox, varID As Variant, _

varRow As Variant, varCol As Variant, varcode As Variant) As Variant

' Purpose: Load the delivery list

Select Case varcode

Case acLBInitialize ' Initialization

' Array is initialized at form load

cbkDelivery = True ' Okay to proceed

Case acLBOpen ' When the control is opened

cbkDelivery = Timer ' Create a unique control ID

Case acLBGetRowCount ' How many rows?

cbkDelivery = 4 ' Number of rows

Case acLBGetColumnCount ' How many columns?

cbkDelivery = lst.ColumnCount ' Echo back the property value

Case acLBGetColumnWidth ' How wide are the columns?

cbkDelivery = -1 ' Use the default widths

Case acLBGetValue ' Fetch one data item

cbkDelivery = mavarDelivery(varRow)

Case acLBGetFormat ' Format one data item

' Unused

Case acLBEnd ' Close down the function

End Select

End Function

Finally, when the user clicks the appropriate button to move an item up or down in the list, the code in Listing 13.23 handles the rearrangement of the array. When an array item is moved, sending the Requery method to the list box causes the control to rebuild its list from the new array order by using the callback function once again.

Listing 13.23 AES_Frm1.Mdb-Routines to Move Items within a List Array

' A routine behind the form to move an item up the list by one

Private Sub cmdUp_Click()

' Purpose: Move an item up one position

Call cbfListMove(-1)

End Sub

' A routine behind the form to move an item down the list by one

Private Sub cmdDown_Click()

' Purpose: Move an item down one position

Call cbfListMove(1)

End Sub

' A routine behind the form to perform the reorder and requery

Private Sub cbfListMove(rintMove As Integer)

' Purpose: Move an item in the list box

' Arguments: rintMove:=Positive/negative offset for move

' Example: cbfListMove(-1)

Dim avarHold As Variant

Dim iint As Integer

Dim intNewPos As Integer

Dim intOldPos As Integer

If Me!lstDelivery.ListIndex = -1 Then

Exit Sub

End If

avarHold = mavarDelivery() ' Preserve the original values

intOldPos = Me!lstDelivery.ListIndex

intNewPos = intOldPos + rintMove

' Make sure the move is to a valid location

If intNewPos < 0 Or intNewPos > UBound(mavarDelivery) Then

Exit Sub

End If

' Swap the relocated items

mavarDelivery(intOldPos) = avarHold(intNewPos)

mavarDelivery(intNewPos) = avarHold(intOldPos)

Me!lstDelivery.Requery

Call lstDelivery_AfterUpdate ' Reset the text box

End Sub

The supporting routines for this technique that are not shown here are available in AES_FRM1.MDB on the CD-ROM. They include a Form_Current and a lstDelivery_AfterUpdate event on the form that synchronize the list box values to the string version of those values stored in the table. This code is very similar to the example in the "Using Multi-Select List Boxes" section in this chapter. The work of creating, saving, and interpreting the delimited string versions of the list box items is handled by library functions, also included in the sample database.

This technique has myriad uses and, once again, highlights the utility of callback functions.

From Here...

In this chapter, you saw a variety of techniques for maximizing the value and performance of Combo Box and List Box controls used on forms.


© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.