ACC2000: Bound Multi-Selection List Box Always Returns Null

ID: Q210203

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

A multiple-selection list box bound to a field in a form's underlying record source through the ControlSource property returns a Null value regardless of the selection or selections made in the list.

CAUSE

A bound control can only return and represent a single value stored in a field in the form's underlying recordset. Because multiple selections cannot be represented in a single field, a Null value is returned.

RESOLUTION

The Selected property or ItemsSelected collection can be used to retrieve information regarding which items are selected in a multiple-selection list box.

You will find information below that demonstrates one way to simulate binding a multiple-selection list box to a single field by storing and managing a semicolon-delimited list of values. For example, if your list box is filled with different fruits, one possible combination of selections might be stored as "Apples;Oranges;Bananas."

MORE INFORMATION

Drawbacks to Storing Multiple Selections in a Single Field

Although this article demonstrates how to store multiple selections from a multiple-selection list box into a single field, you should realize that it is not good database design, and you should explore alternatives. Storing a list of values in a single field violates basic rules of database normalization theory. First Normal Form in Normalization theory states that all column (field) values must be atomic, or indivisible. For example, in a single field you should not store a list of items such as "Apples, Oranges, Bananas" because there is no simple way to manipulate, sort, or query on these values independently of one another.

The following example demonstrates how to create a multiple-selection list box in the sample database Northwind.mdb's Customers form that enables you to select a list of "many" employees for each customer. Perhaps a customer should only work with a set of specific employees assigned by this list box. A new field called Employees is added to the Customers table to hold a semicolon-delimited list of Employee ID numbers managed by a multiple- selection list box to be added to the Customers form.

The disadvantage of this design is that you would not be able to determine what customers are currently assigned to a particular employee, or vice versa.

Ideally, because "many" selections need to be stored for the "one" record, you might want to explore storing these selections in a separate "many" table rather than in a single (or multiple fields) in the "one" table. The assignment of employees to customers is a many-to-many relationship such that "one" customer can have "many" employees and "one" employee can have "many" customers. Good database design dictates that you should create a third table, often called a "junction" or "assignment" table between the many-to-many tables. In the example Northwind database, the Order Details table offers an excellent example of this. The Order Details table is positioned between the Orders and Products tables. Rather than assigning many products to one order using a multiple-selection list box, a subform is used in the Orders form to edit the Order Details junction table.

How to Simulate Binding a Multiple-Selection List Box to a Field

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

When a control is bound to a field in the underlying recordset of the form, Access does two things automatically for you:

When the data in the field changes, perhaps by moving from record to record, Microsoft Access synchronizes the data in the control with the data in the field.

When the data in the control is changee, perhaps by the user, Access saves the data from the control to the bound field.

The following two Visual Basic procedures, mslbSyncList and mslbSaveList, facilitate this functionality for a multiple-selection list box.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Open the sample database Northwind, create a new module, and type the following line in the Declarations section if it isn't already there:


Option Explicit

Type the following procedures:


Sub mslbSyncList(ctlMSLB As Control, ctlBound As Control)
   Dim i As Integer
   Dim SourceList As String, SearchItem As String
On Error Goto Err_mslbSyncList
   SourceList = ";" & ctlBound & ";"
   For i = 0 To ctlMSLB.ListCount - 1
      SearchItem = ";" & ctlMSLB.ItemData(i) & ";"
      ctlMSLB.Selected(i) = (InStr(SourceList, SearchItem) > 0)
   Next i
Bye_mslbSyncList:
   Exit Sub
Err_mslbSyncList:
   Beep
   MsgBox Error$, 16
   Resume Bye_mslbSyncList
End Sub

Sub mslbSaveList(ctlMSLB As Control, ctlBound As Control)
   Dim MyList, i
   MyList = Null
On Error Goto Err_mslbSaveList
   For Each i In ctlMSLB.ItemsSelected
      MyList = MyList & ";" & ctlMSLB.ItemData(i)
   Next
   If Len(MyList) > 0 Then MyList = Mid(MyList, 2)
   ctlBound = MyList
Bye_mslbSaveList:
   Exit Sub
Err_mslbSaveList:
   Beep
   MsgBox Error$, 16
   Resume Bye_mslbSaveList
End Sub

Open the Customers table in Design view. Add a new field called Employees with data type Text, and set the FieldSize property to 255.

Save and close the table.

Open the Customer form and add a text box with the following properties:
```
   Name: txtEmployees
   ControlSource: Employees
   Visible: No 
```
NOTE: For testing purposes, you may want to leave the Visible property set to Yes to examine the semicolon-delimited list being generated.

Add a new list box with the following properties:


   Name: mslbEmployees
   RowSource: Employees
   ColumnCount: 2
   ColumnWidths: 0
   MultiSelect: Simple

On the View menu, click Code to open the form's module and type the following procedures:
```
Private Sub Form_Current()
   mslbSyncList Me![mslbEmployees], Me![txtEmployees]
End Sub

Private Sub mslbEmployees_AfterUpdate()
   mslbSaveList Me![mslbEmployees], Me![txtEmployees]
End Sub
 
```
NOTE: The mslbSyncList procedure should be called from any code that changes the contents of the field that the semicolon-delimited list is stored into. The mslbSaveListfield procedure should be called from any code that changes the contents of the multiple-selection list box itself.

View the form in Form view, move from customer to customer, and make different selections in the new multiple-selection list box.

REFERENCES

For additional information about database normalization, please see the following article in the Microsoft Knowledge Base:

Q209534 Database Normalization Basics

For more information about multiple-selection list boxes, click Microsoft Access Help on the Help menu, type "MultiSelect Property" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: prb

Keywords : kbusage kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb