• Choose a category
• All
• Classic-Fiction

By Root 1931 0

or won't be able to get the information easily. For example, if you're filling the list box with the results of a query that returns a large number of rows, you won't want to perform the MoveLast method you'd need to find out how many rows the query returned—MoveLast requires Access to walk through all the rows returned from the query and would make the load time for the list box too long. Instead, respond to acLBGetRowCount with a -1. This tells Access that you'll tell it later how many rows there are. Then, in response to the acLBGetValue case, return data until you've reached the end. Once you return Null in response to the acLBGetValue case, Access understands that there's no more data.

This method has its pitfalls, too. Although it allows you to load the list box with data almost immediately, the vertical scrollbar won't be able to operate correctly until you've scrolled down to the end. If you can tolerate this side effect, returning -1 in response to acLBGetRowCount will significantly speed the loading of massive amounts of data into list and combo box controls.

To provide values for the acLBGetColumnWidth case, you can specify a different width for each column based on the lngCol parameter. To convert from inches to twips, multiply the value by 1,440. For example, to specify a 1/2-inch column, return 0.5 × 1,440.

You might wonder when you would use any of these techniques. In Access 2002 or later, your best bet is to use the AddItem method whenever possible. Under the covers, this method executes the same sort of code as if you were to modify the RowSource property value yourself. (You don't really need to ever modify the RowSource property manually, in Access 2002 or later—calling the AddItem and RemoveItem methods does the same sort of thing for you.) Remember, however, that the RowSource property value is limited in size. For large lists of values, perhaps with many columns, you may run out of space before you run out of data. In that case, you'll be required to use the list-filling callback function technique. If you're using Access 2000 or an earlier version, you'll need to use the list-filling callback technique for complex lists, or to create the RowSource property value in code yourself for simpler lists.

7.6. Pass a Variable Number of Parameters to a Procedure

Problem

You need a procedure that will work on a list of items, and you don't know ahead of time how many there will be. You know that VBA will allow you to use optional parameters, but this requires you to know exactly how many items you might ever need to pass, and in your case, it's impossible to predict that value. How can you accomplish this?

Solution

You have two choices in solving this problem: you can pass an array as a parameter, or you can pass a comma-delimited list, which Access will convert into an array for you. An array (an ordered list of items) must contain a single data type. By using the variant data type, though, you can pass a list of varying types into your procedure. This solution demonstrates both these techniques.

From 07-06.MDB, load the module basArrays in design mode and do the following:

Open the Immediate window (press Ctrl+G or choose the View → Immediate Window menu item). In these steps, you will run code from the Immediate window.

If you need a procedure that will take a list of words and convert each to uppercase, you can use the UCaseArray procedure. To test it, type the following in the Immediate window:

TestUCase 5

You can replace the 5 in the command line with any value between 1 and 26. The procedure will create as many strings as you request, place them into an array, and then call UCaseArray. This procedure will convert all the strings in the array to uppercase. The test procedure will display the original version, followed by the altered version of the array. As you can see, no matter how many items you specify for the UCaseArray procedure to work on, it'll convert them all to uppercase. Figure 7-9 shows this procedure in use.

Figure 7-9. TestUCase with five strings converted