Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 7.5 Programmatically Add Items to a List or Combo Box

7.5.1 Problem

Getting
items into a list or combo box from a data source is elementary in
Access. Sometimes, though, you need to put things into a list box
that you don't have stored in a table. In Visual
Basic and other implementations of VBA-hosted environments, and in
Access 2002 and later, this is simple: you just use the AddItem
method. But Access list boxes in versions prior to 2002
don't support this method. How can you add to a list
box items that aren't stored in a table?

7.5.2 Solution

Access list boxes (and combo boxes) in
versions prior to Access 2002 didn't support the
AddItem method that Visual Basic programmers are used to using. To
make it easy for you to get bound data into list and combo boxes, the
Access developers originally didn't supply a simple
technique for loading unbound data. To get around this limitation,
there are two methods you can use to place data into an Access list
or combo box: you can programmatically build the RowSource string
yourself, or you can call a list-filling callback function. Providing
the RowSource string is easy, but it works in only the simplest of
situations. A callback function, though, will work in any situation.
This solution demonstrates both methods. In addition, this solution
demonstrates using the AddItem method of ListBox and ComboBox
controls, added in Access 2002.

One important question, of course, is why you would ever need either
of the more complex techniques for filling your list or combo box.
You can always pull data from a table, query, or SQL expression
directly into the control, so why bother with all this work? The
answer is simple. Sometimes you don't know ahead of
time what data you're going to need, and the
data's not stored in a table. Or perhaps you need to
load the contents of an array into the control and you
don't need to store the data permanently. Prior to
Access 2002, you had no choice but to either create a list-filling
callback function, or modify the RowSource property of the control
yourself. Starting in Access 2002, you can also use the AddItem
method to solve many list filling requirements.

The following sections walk you through using all three of the
techniques for modifying the contents of a list or combo box while
your application is running. The first example modifies the value of
the RowSource property, given that the RowSourceType property is set
to Value List. The second example covers list-filling callback
functions. The final example shows how to use the AddItem method of
the control.

7.5.2.1 Filling a list box by calling the AddItem method

1. Open the
   form frmAddItem in

   07-05.MDB .

2. Change the contents of the list box by choosing either Days or Months
   from the option group on the left. Try both settings and change the
   number of columns to get a feel for how this method works. Figure 7-6 shows the form set to display month names in
   three columns.

Figure 7-6. The sample form, frmRowSource, displaying months in three columns

7.5.2.2 Filling a list box by modifying the RowSource property

1. Open the
   form frmRowSource in

   07-05.MDB .

2. Change the contents of the list box by choosing either Days or Months
   from the option group on the left. Try both settings and change the
   number of columns, to get a feel for how this method works. Figure 7-6 shows the form set to display month names in
   three columns.

7.5.2.3 Filling a list box by creating a list-filling callback function

1. Open the form frmListFill in
   

   07-05.MDB .

2. Select a weekday from the first list box. The second list box will
   show you the date of that day this week, plus the next three
   instances of that weekday. Figure 7-7 shows the
   form with Wednesday, March 14, 2001, selected.

Figure 7-7. Using list-filling callback functions to fill the lists on frmListFill

3. To use this method, set the
   control's RowSourceType property to the name of a
   function (without an equals sign or parentheses). Functions called
   this way must meet strict requirements, as discussed in the next
   section. Figure 7-8 shows the properties sheet for
   the list box on frmListFill, showing the RowSourceType property with
   the name of the list-filling function.

Figure 7-8. The properties sheet entry for the list-filling function

7.5.3 Discussion

This section explains the two methods for programmatically filling
list and combo boxes. The text refers only to filling list boxes, but
the same techniques apply to combo boxes. You may find it useful to
open up the form module for each form as it's
discussed here.

7.5.3.1 Calling the AddItem method

Starting with Access 2002, you can add items to a ListBox or ComboBox
control by simply calling the AddItem method of the control. (You can
remove items from the control by calling its RemoveItem method,
specifying the item number or text to remove.) This technique is by
far the simplest and should be your first choice, given the option.

Selecting an option in the Fill Choice group runs the following code:

Private Sub grpChoice_AfterUpdate( )
Dim strList As String
Dim intI As Integer
Dim varStart As Variant
lstAddItem.RowSourceType = "Value List"
' Clear out the list.
lstAddItem.RowSource = vbNullString
lstAddItem.ColumnCount = 1
grpColumns = 1
Select Case Me.grpChoice
Case 1  ' Days
' Get last Sunday's date.
varStart = Now - WeekDay(Now)
' Loop through all the week days.
For intI = 1 To 7
lstAddItem.AddItem Format(varStart + intI, "dddd")
Next intI
Case 2  ' Months
For intI = 1 To 12
lstAddItem.AddItem Format(DateSerial(2004, intI, 1), "mmmm")
Next intI
End Select
Me.txtFillString = lstAddItem.RowSource
End Sub

This code starts by setting the RowSourceType property of the control
to the text, "Value List":

lstAddItem.RowSourceType = "Value List"

This step is crucial: unless you've set the
RowSourceType property correctly, either at design time or in your
code, you won't be able to call the AddItem or
RemoveItem methods.

Next, the code clears and resets the list's
formatting:

lstAddItem.RowSource = vbNullString
lstAddItem.ColumnCount = 1
grpColumns = 1

Then, depending on the choice you've made, the code
adds days of the week or months of the year to the ListBox control:

Select Case Me.grpChoice
Case 1  ' Days
' Get last Sunday's date.
varStart = Now - WeekDay(Now)
' Loop through all the week days.
For intI = 1 To 7
lstAddItem.AddItem Format(varStart + intI, "dddd")
Next intI
Case 2  ' Months
For intI = 1 To 12
lstAddItem.AddItem Format(DateSerial(2004, intI, 1), "mmmm")
Next intI
End Select

In order to verify that, under the covers, the code is simply
manipulating the RowSource property for you, the example ends by
displaying the RowSource property in a TextBox control on the form:

    Me.txtFillString = lstAddItem.RowSource

Beware that even though it appears that you're actually adding items to the control, what you're really doing is modifying the RowSource property of the control. As such, you're limited by the same restrictions as if you were setting the property manually (see the next section). Specifically, you're limited to the allowed size of the RowSource property, which was 2048 characters in Access 2002 (the size may be larger in your version of Access).

7.5.3.2 Modifying the RowSource property

If you're using Access 2002 or later, you
won't want to use this technique. On the other hand,
for earlier versions of Access, this can be a simple way to create
unbound lists. If you
set a list box's RowSourceType property to Value
List, you can supply a list of items, separated with semicolons, that
will fill the list. By placing this list in the
control's RowSource property, you tell Access to
display the items one by one in each row and column that it needs to
fill. Because you're placing data directly into the
properties sheet, you're limited by the amount of
space available in the properties sheet (this value varies depending
on the version of Access).

You can modify the RowSource property of
a list box at any time by placing into it a semicolon-delimited list
of values. The ColumnCount property plays a part, in that Access
fills the rows first and then the columns. You can see this for
yourself if you modify the ColumnCount property on the sample form
(frmRowSource).

The sample form creates a list of either the days in a week or the
months in a year, based on the value and option group on the form.
The code that performs the work looks like this:

Select Case Me.grpChoice
Case 1  ' Days
' Get last Sunday's date.
varStart = Now - WeekDay(Now)
' Loop through all the days of the week.
For intI = 1 To 7
strList = strList & ";" & Format(varStart + intI, "dddd")
Next intI
Case 2  ' Months
For intI = 1 To 12
strList = strList & ";" & Format(DateSerial(2004, intI, 1), "mmmm")
Next intI
End Select
' Get rid of the extra "; " at the beginning.
strList = Mid(strList, 2)
Me.txtFillString = strList

Depending on the choice in grpChoice, you'll end up
with either a string of days like this:

Sunday; Monday; Tuesday; Wednesday; Thursday; Friday; Saturday; Sunday

or a string of months like this:

January; February; March; April; May; June; July; August; September; October; _
November; December

Once you've built up the string, make sure that the
RowSourceType property is set correctly and then insert the new
RowSource string:

lstChangeRowSource.RowSourceType = "Value List"
lstChangeRowSource.RowSource = strList

If you intend to use this method, modifying
the RowSource property, make sure you understand its main limitation:
because it writes the string containing all the values for the
control into the control's properties sheet,
it's limited by the number of characters the
properties sheet can hold.

If you're using a version of Access prior to Access
2002, you can use at most 2,048 characters in the RowSource property.
If you need more data than that, you'll need to use
a different method. If you're using Access 2002 or
later you shouldn't have a problem, because the size
has been greatly expanded. On the other hand, in those versions,
you're better off using the AddItem method instead.

7.5.3.3 Creating a list-filling callback function

This
technique, which involves creating a special function that provides
the information Access needs to fill your list box, is not well
documented in the Access help. Filling a list using a callback
function provides a great deal of flexibility, and
it's not difficult. This technique provides the
greatest flexibility, and isn't limited by the size
of the RowSource property.

The concept is quite simple: you provide Access with a function that,
when requested, returns information about the control
you're attempting to fill. Access
"asks you questions" about the
number of rows, the number of columns, the width of the columns, the
column formatting, and the actual data itself. Your function must
react to these requests and provide the information so that Access
can fill the control with data. This is the only situation in Access
where you provide a function that you never need to call. Access
calls your function as it needs information in order to fill the
control. The sample form frmFillList uses two of these functions to
fill its two list boxes.

To communicate with Access,
your function must accept five specific parameters. Table 7-4 lists those parameters and explains the
purpose of each. (The parameter names are arbitrary and are provided
here as examples only. The order of the parameters, however, is not
arbitrary; they must appear in the order listed in Table 7-4.)

 ctl

 varId

 lngRow

 lngCol

 intCode

Access uses the final
parameter, intCode, to let you know what
information it's currently requesting. Access places
a particular value in that variable, and it's up to
your code to react to that request and supply the necessary
information as the return value of your function. Table 7-5 lists the possible values of
intCode, the meaning of each, and the
value your function must return to Access in response to each.

 acLBInitialize

 acLBOpen

 acLBGetRowCount

 acLBGetColumnCount

 acLBGetColumnWidth

 acLBGetValue

 acLBGetFormat

 acLBClose

 acLBEnd

You'll find that almost
all of your list-filling functions will be structured the same way.
Therefore, you may find it useful to always start with the

ListFillSkeleton function, which is set up to
receive all the correct parameters and includes a
Select Case statement to handle
each of the useful values of intCode. All
you need to do is change its name and make it return some real
values. The

ListFillSkeleton function is as
follows:

Function ListFillSkeleton (ctl As Control, _
varId As Variant, lngRow As Long, lngCol As Long, _
intCode As Integer) As Variant
Dim varRetval As Variant
Select Case intCode
Case acLBInitialize
' Could you initialize?
varRetval = True
Case acLBOpen
' What's the unique identifier?
varRetval = Timer
Case acLBGetRowCount
' How many rows are there to be?
Case acLBGetColumnCount
' How many columns are there to be?
Case acLBGetValue
' What's the value in each row/column to be?
Case acLBGetColumnWidth
' How many twips wide should each column be?
' (optional)
Case acLBGetFormat
' What's the format for each column to be?
' (optional)
Case acLBEnd
' Just clean up, if necessary (optional, unless you use
' an array whose memory you want to release).
End Select
ListFillSkeleton = varRetval
End Function

For example, the following function from frmListFill,

ListFill1 , fills in the first list box on the
form. This function fills in a two-column list box, with the second
column hidden (its width is set to 0 twips). Each time Access calls
the function with acLBGetValue in
intCode, the function calculates a new
value for the date and returns it as the return value. The source
code for

ListFill1 is:

Private Function ListFill1(ctl As Control, varId As Variant, _
lngRow As Long,  lngCol As Long, intCode As Integer)
Select Case intCode
Case acLBInitialize
' Could you initialize?
ListFill1 = True
Case acLBOpen
' What's the unique identifier?
ListFill1 = Timer
Case acLBGetRowCount
' How many rows are there to be?
ListFill1 = 7
Case acLBGetColumnCount
' How many columns are there to be?
' The first column will hold the day of the week.
' The second, hidden column will hold the actual date.
ListFill1 = 2
Case acLBGetColumnWidth
' How many twips wide should each column be?
' Set the width of the second column to 0.
' Remember, they're zero-based.
If lngCol = 1 Then ListFill1 = 0
Case acLBGetFormat
' What's the format for each column to be?
' Set the format for the first column so
' that it displays the day of the week.
If lngCol = 0 Then
ListFill1 = "dddd"
Else
ListFill1 = "mm/dd/yy"
End If
Case acLBGetValue
' What's the value for each row in each column to be?
' No matter which column you're in, return
' the date lngRow days from now.
ListFill1 = Now + lngRow
Case acLBEnd
' Just clean up, if necessary.
End Select
End Function

The next example, which fills the second list box on the sample form,
fills an array of values in the initialization step
(acLBInitialize) and returns items from the array
when requested. This function,

ListFill2 ,
displays the next four instances of a particular day of the week.
That is, if you choose Monday in the first list box, this function
will fill the second list box with the date of the Monday in the
current week, along with the dates of the next three Mondays. The
source code for

ListFill2 is:

Private Function ListFill2( _
ctl As Control, varId As Variant, lngRow As Long, _
lngCol As Long, intCode As Integer)
Const MAXDATES = 4
Static varStartDate As Variant
Static adtmDates(0 To MAXDATES) As Date
Dim intI As Integer
Dim varRetval As Variant
Select Case intCode
Case acLBInitialize
' Could you initialize?
' Do the initialization.  This is code
' you only want to execute once.
varStartDate = Me.lstTest1
If Not IsNull(varStartDate) Then
For intI = 0 To MAXDATES - 1
adtmDates(intI) = DateAdd("d", 7 * intI, varStartDate)
Next intI
varRetval = True
Else
varRetval = False
End If
Case acLBOpen
' What's the unique identifier?
varRetval = Timer
Case acLBGetRowCount
' How many rows are there to be?
varRetval = MAXDATES
Case acLBGetFormat
' What's the format for each column to be?
varRetval = "mm/dd/yy"
Case acLBGetValue
' What's the value for each row in each column to be?
varRetval = adtmDates(lngRow)
Case acLBEnd
' Just clean up, if necessary.
Erase adtmDates
End Select
ListFill2 = varRetval
End Function

Note that the array this function fills,
adtmDates, is declared as a static variable. Declaring it this way
makes it persistent: its value remains available between calls to the
function. Because the code fills the array in the
acLBInitialize case but doesn't
use it until the multiple calls in the
acLBGetValue case, adtmDates must
"hang around" between calls to the
function. If you fill an array with data for your control,
it's imperative that you declare the array as
static.

You should also consider the
fact that Access calls the acLBInitialize case
only once, but it calls the acLBGetValue case at
least once for every data item to be displayed. In this tiny example,
that barely makes a difference. If you're doing
considerable work to calculate values for display, however, you
should put all the time-consuming work in the
acLBInitialize case and have the
acLBGetValue case do as little as possible. This
optimization can make a big difference if you have a large number of
values to calculate and display.

There are three more things you should note about this second list
box example:

• In the acLBEnd case,
  the function clears out the memory used by the array. In this small
  example, this hardly matters. If you are filling a large array with
  data, you'd want to make sure that the data is
  released at this point. For dynamic arrays (where you specify the
  size at runtime), Erase releases all the memory.
  For fixed-size arrays, Erase empties out all the
  elements.

• This example didn't include code for all the
  possible cases of intCode. If you
  don't need a specific case, don't
  bother coding for it. There was no need to set the column widths
  here, so there's no code handling
  acLBGetColumnWidth.

• At the time of this writing,
  there's a small error in the way Access handles
  these callback functions. Although it correctly calls the
  acLBInitialize case only once when you open a form
  that requires a control to be filled with the function, if you later
  change the RowSourceType in code, Access will call the
  acLBInitialize case twice. This
  doesn't come up often, but you should be aware that
  there are circumstances under which Access will erroneously call this
  section of your code more times than you intended. To solve this
  problem, you can use a static or global variable as a flag to keep
  track of the fact that the initialization has been done and opt not
  to execute the code after the first pass through.

In the list-filling callback
function method, when Access requests the number of rows in the
control (i.e., when it passes acLBGetRowCount in
intCode), you'll usually
be able to return an accurate value. Sometimes, however, you
won't know the number of rows 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
returnedMoveLast 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 x 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 latercalling
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.