Let me check my list …

Doug tries to address commonly asked questions from Access developers. This month, he looks at various ways of using the List Box control including Multi-Select, moving items between lists, using non table record sources and showing all tables in a database in a list box .

I’ve got a list box that I’m using as a means of limiting what’s reported. It works fine when the list box doesn’t allow Multi Select. However, I’d like to be able to select more than one object at a time from the list box.

Unlike most of the other controls, referring to a multi select list box doesn’t return its value in any way that can be used in a query. Instead, you must use VBA code to determine which items have been selected.

To help in this, the List Box object has an ItemsSelected collection which provides a means to access data in the selected rows. The ItemsSelected collection is a collection of Variants, each one representing an integer index referring to a specific selected row in the list box. The collection has a single property associated with it, Count, which indicates how many items have been selected.

To list which items have been selected in a list box named lstItems, you can use code like the following:

It’s probably worth commenting on the use of Me.lstItems.Column(1, varItem) in the code above. This example assumes that the second column of the list box is the one that contains the important indicative information. This is because typically you’ll have the Id of each item as the (hidden) first column. Using Me.lstItems.Column(1, varItem) retrieves the contents of that second column for the particular row. If you wanted the bound column instead, you could use Me.lstItems.ItemData(varItem).

Now, when you use the OpenReport method to open your report, you have the ability to pass a Where clause to the report to limit what’s actually reported. Thus, to have the report limited to those items you’ve selected from your list box, you need to build a Where clause using essentially the same code as above.

In the accompanying database, I’ve copied several tables from the Northwinds database that comes with Access, as well as the Employee Sales By Country report (apologies for not creating my own report!). On the form that demonstrates how to use a multiselect list box as the basis for limiting what’s reported on the report, I have the following code in the Click event of a command button. Note that since I do want the value of Id field from the (hidden) first column, I’m using the ItemData property as I discussed above, but I could have just as easily used

If at least one row has been selected in the list box, I loop through the ItemsSelected collection, creating a comma-separated list of the Ids corresponding to the selected rows.

Since I’m automatically appending a comma and space after each entry, the string is going to have an unnecessary comma and space at the end, so I use the Left function to trim those last two characters from the string. I then use the comma-delimited string I created with the IN operator to form the WHERE clause I’ll use when opening the report. strWhere would look something like [EmployeeId] IN (1, 3, 4). (Just in case you’re wondering, you can use an IN operator even if you only have a single value.)

Now that I have the WHERE clause I want to use, I pass it as a parameter to the OpenReport method. I chose to use named parameters below: I could just as easily used

There’s always a dilemma of what to do if nothing’s been selected in the list box. I decided to tell the user that he/she hasn’t selected anything, and give the option of opening the report with no limitation (i.e.: open the report showing all the employees):

If the field I wanted to use in the WHERE clause was text, rather than numeric, it would be necessary to put quotes around the values, so the line

would need to be

or

I’ve got two list boxes on my form. I’d like one to indicate those records that have been selected, and the other those records that have not been selected, and have the ability to move items from one list to the other. How can I do this?

In the accompanying sample database, I’ve modified the Products and Categories tables from the Northwinds database slightly to make it easier to illustrate how to do this. Rather than having a one-to-many relationship between Categories and Products (i.e.: each Product can belong in only one Category), I’ve introduced an intersection entity (which I named Catalog) which allows a Product to belong to more than one Category.

In the sample form that illustrates this technique, I’ve got a combo box named cboCategories that lists all of the possible Category values. Once the Category of interest is known, it’s possible to return a list of those Products which are linked with that Category using a query like:

Knowing which Products aren’t linked to that Category is a little trickier, but can still be done in SQL:

Those of you who are still using Access 97 may question the SQL above. It’s a little known fact that it is possible to include a SELECT statement rather than a table in a Join as I’ve done above in Access 97. The secret is to use square brackets around the subselect, and to put a period after the closing square bracket.

It turns out that getting the two list boxes populated is the hardest part of the exercise! In addition to the two list boxes, one showing those that have been selected, and one showing those that haven’t been selected, add two command buttons: one to transfer select additional products (i.e.: move from the Not Selected list to the Selected list), and one to remove selected products (i.e.: move from the Selected list to the Not Selected list).

Associating a Product with the selected Category involves inserting a new row into the Catalog table representing the Product/Category combination. Once the insertions have been completed, the data in the underlying tables has been changed, it’s necessary to refresh the two list boxes:

Disassociating a Product from the selected Category involves deleting the row representing the Product/Category combination from the Catalog table. Again, once the deletions have been completed, the data in the underlying tables has been changed, it’s necessary to refresh the two list boxes:

Sometimes it’s not convenient to have the data for a list box stored in a table. What other options are there?

Assuming you’re using Access 2002 or Access 2003, you can take advantage of the AddItem method of the list box, the same as VB programmers have been able to do for years.

Set the RowSourceType property of the list box (or combo box, for that matter) to “Value List”, and then you can use code like Me.MyListBox.AddItem “1;New Item” to add a new entry (with two columns) to the list box, or Me.MyListBox.RemoveItem(1) to remove the second item from the list box (remember that, unless you’ve got headers showing, the first row of the list box is row 0)

However, AddItem may not be the best approach. For one thing, it doesn’t work prior to Access 2002. While the accompanying database has an example of using AddItem (and RemoveItem), if you were to open the database using Access 2000, the sample form will fail (despite the fact that the database is in Access 2000 format).

Another way to load a list box (or combo box) is to write a custom function that will populate the control. Access will end up calling the function multiple times, passing different parameters each time, so it’s important that the function conform to what Access is expecting.

The Visual Basic function must accept five arguments. The first argument must be declared as a control and the remaining arguments as Variants. The function itself must return a Variant:

Each time Access calls the function, it will provide values for the 5 parameters as follows:

The defined values for code are:

as the code parameter, it’s useful to create a framework like the following:

The acLBInitialize section is the section in the code where whatever needs to be done to ensure that you’ve got the correct information available to populate the combo box or list box is carried out. The function returns True (or any Nonzero value) if the function can fill the list, or returns False (or Null) otherwise.

The acLBOpen section must return a nonzero ID value if the function can fill the list, or False (or Null) otherwise. Whatever value is returned by this section is what Access uses for all subsequent calls to the function for that particular control at that particular instance. In other words, if you’re using the same function to populate two different list boxes, you need to ensure that a different ID value is returned for the two controls so that Access can keep them straight. If you issue a Requery on the control, you can either use

the same value for ID, or simply ensure that a random number is issued. I typically use the value of the Timer function (which returns the number of seconds elapsed since midnight).

The acLBGetRowCount section returns the number of rows to be displayed. Remember that if the ColumnsHeads property is set to True, it’s necessary to return one more than the actual count.

The acLBGBetColumnCount section returns the number of columns to be displayed. This can’t be zero, and must match the property sheet value.

The acLBGetColumnWidth section returns the width (in twips) of the column specified by the col argument. Returning –1 indicates to use the default width.

The acLBGetFormat section returns a Format string to be used to format the list entry displayed in the row and column specified by the row and col arguments. Again, –1 indicates to use the default format.

The acLBGetValue section returns the value of the list entry to be displayed in the row and column specified by the row and col arguments passed to the function. If the ColumnsHead property is set to True, then row 0 is intended to return the column headers.

The acLBEnd section doesn’t actually return anything, but Access will always make a last call to the function passing this value. This allows you to have a section to do any cleanup activities that may be necessary.

Enough description, though. Let’s look at a sample function. One example Microsoft often presents lets you have a list box that contains 4 rows representing next Monday, followed by the next four Mondays:

You can see that acLBInitialize returns True, as necessary, acLBOpen uses the Timer to return a random number, acLBGetRowCount returns that there are 4 rows, acLBGetColumnCount returns that there’s 1 column, acLBGetColumnWidth says to use the default width, acLBGetValue uses the row number to calculate different dates, and acLBGetFormat and acLBEnd return Null. (in actual fact, I could have left them out of the Select Case construct). Note that I’m passing a formatted value whenever acLBGetValue is passed as an argument. I could just have easily used acLBGetFormat to do that:

To use this function, you set the list box’s RowSourceType property to the name of the function, and leave the RowSource property blank, as illustrated in Figure 1.


Figure 1: Configuring a list box to use a custom function to provide its values

List all of the non-system tables

The other sample function I have in the accompanying database lists all of the non-system tables in the database, as well as the number of rows in each. In this example, the acLBInitialize section actually does something. It creates a Static array that contains the names of the tables and their sizes.

As mentioned above, I’m going to create a static array to contain the details for each of the non-system tables in the current database’s TableDefs collection. Since I don’t know in advance exactly how many nonsystem tables there are (TableDefs.Count returns system tables), I’ll initialize the array to the largest it could possibly be, and then resize it at the end once I know how many rows there actually are. This is because it’s relatively “expensive” to use the ReDim command.

Now that we’ve looped through all of the TableDef objects in the TableDefs collection, we know how many represent non-system tables. Assuming that at least 1 non-system table was found, redimension typTables to its proper size. If not, erase the array.

As illustrated before, the function will return Timer (to get a random number) when acLBOpen is passed as an argument:

Remember that if the ColumnsHead property has been set to True for the list box, it’s necessary to return one more than the number of tables found when acLBGetRowCount is passed. (As explained earlier, the fld parameter is actually a reference to the list box, so it’s possible to check its ColumnsHead property, rather than having to hard-code it here):

Since it’s necessary that acLBGetColumnCount be correct, I retrieve the list box’s ColumnCount property. (Note that this assumes I’ve defined the list box correct on the form.)

I want the number of rows to be comma-separated, with no decimal point, so I set the format when the value passed for col is 1. (I’ll just use the default for col 0)

Finally! Here’s where the function returns a specific value, depending on what was passed for row and col. Remember that if the ColumnHeads property has been set, the first row (row = 0) needs to be the column headings. When that’s the case, the row count in the list box and the row in the array won’t correspond.

That’s the function pretty much completed, other than doing whatever cleanup’s required when acLBEnd is passed.

The error trapping is relevant. Since the array is erased if there’s nothing in it, the call to the function when code is acLBGetRowCount can cause an error 9. In that case, I want to return 1 if the ColumnHeads property is set, or 0 otherwise:

You can see that acLBInitialize returns True, as necessary, acLBOpen uses the Timer to return a random number, acLBGetRowCount returns the number of rows (4), acLBGetColumnCount returns the column count (1), acLBGetColumnWidth says to use the default width, acLBGetValue uses the row number to calculate different dates, and acLBGetFormat and acLBEnd return Null.

In this example, I’m passing a formatted value whenever acLBGetValue is passed as an argument. I could have just as easily used acLBGetFormat to return a format string to be used with my data:

To use this function, you just need to set the ListBox’s RowSourceType property to the name of the function, and leave the RowSource property blank, as shown in Figure 1.

With that simple function out of the way, I’ve included a more interesting function in the sample database. My function lists all of the non-system tables in the database, as well as the number of rows in each.

 Your download is called   Steele_LetMeCheckMyList.accdb

 

This entry was posted in Access Controls. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.