ACC: List Box Column Property Gets Incorrect Row Information

• Microsoft Access versions 1.x, 2.0, 7.0, 97

SYMPTOMS

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

When you select a row from a multicolumn list box or combo box, controls that reference the Column property of the list box or combo box retrieve data from a different row in the list box or combo box.

NOTE: A list box may appear to work as desired, but upon binding the list box, it will exhibit the same behavior as a combo box.

CAUSE

The list or combo box's BoundColumn property does not point to a field with unique values.

RESOLUTION

The list or combo box's BoundColumn property must point to a field that contains unique values. If the BoundColumn property does not point to a field with unique values, Microsoft Access will return information for the first row with a matching BoundColumn value when you select a row in the list box or combo box.

For example, the Type field in the sample table below does not contain unique values. If the Type field is used for the BoundColumn property, a text box referencing the second column will return Apple rather than Grape when you select the third row.

   Type    Description
   -------------------
   Fruit   Apple
   Fruit   Orange
   Fruit   Grape

Another way to resolve this behavior would be to add a Counter field (or AutoNumber field in Microsoft Access 7.0 and 97) to the underlying table, and then use the Counter field (or AutoNumber field)for the BoundColumn property.

In Microsoft Access 7.0 and 97, the following code can be added to the AfterUpdate property of the combo box.

   Me![Stock Number] = _
   Me![Manufacturer].Column(1, Me![Manufacturer].ListIndex)

   Me![Description] = _
   Me![Manufacturer].Column(2, Me![Manufacturer].ListIndex)

   NOTE: If you use this code, remove the expressions that are assigned to
   the ControlSource properties of the Stock Number control and Description
   control.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create the following table:

         Table: Table1
         ---------------------------
         Field Name: Manufacturer
            Data Type: Text
         Field Name: Stock Number
            Data Type: Number
            Field Size: Long Integer
            Indexed: Yes
         Field Name: Description
            Date Type: Text
   
         Table Properties: Table1
         -------------------------
         Primary Key: Stock Number
   
   

2. View the table in Datasheet view. Add the following data to the table:

         Manufacturer   Stock Number   Description
         -----------------------------------------
         IB Co          1              Mouse
         IB Co          2              Printer
         IB Co          3              Hard Drive
         ComCo          4              PC
   
   

3. Create the following new form:

         Form: Test1
         Combo box: Manufacturer
            ControlSource: <leave empty>
            RowSourceType: Table/Query
            RowSource: Table1
            ColumnCount: 3
            BoundColumn: 1
            Width: 3
         Text box: Stock Number
            ControlSource: =[Manufacturer].Column(1)
         Text box: Description
            ControlSource: =[Manufacturer].Column(2)
   
   

4. View the form in Form view.

5. Select the first row in the combo box. Note that the Stock Number and Description boxes correctly display "1" and "Mouse."

6. Select the third row in the combo box. Note that the Stock Number and Description boxes still display the information from the first row.

7. Select the fourth row in the combo box. Note that the Stock Number and Description boxes display the correct information for the fourth row.

REFERENCES

For more information about creating combo boxes, search the Help Index for "creating combo boxes," or ask the Microsoft Access 97 Office Assistant.