ACC: Bound Multi-Selection List Box Always Returns NullLast reviewed: August 28, 1997Article ID: Q140483 |
The information in this article applies to:
SYMPTOMSModerate: Requires basic macro, coding, and interoperability skills. 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. This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
CAUSEA 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.
RESOLUTIONThe 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 FieldAlthough 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 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 FieldWhen a control is bound to a field in the underlying recordset of the form, Microsoft Access does two things automatically for you:
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.
REFERENCESFor more information about database normalization, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q100139 TITLE : ACC: Database Normalization BasicsFor more information about multiple-selection list boxes, search the Help Index for "MultiSelect Property," or ask the Microsoft Access 97 Office Assistant. For more information about a multiple-selection, search the Help Index for "Selected." Keywords : kbusage Version : 7.0 97 Platform : WINDOWS Hardware : x86 Issue type : kbprb Solution Type : kbcode |
================================================================================
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |