ACC2000: List Box Column Property Receives Incorrect Row Information (209146)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209146
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

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 expected, but when you bind the list box, it exhibits the same behavior as a combo box.

CAUSE

This behavior occurs because the list box or combo box's BoundColumn property does not point to a field with unique values.

RESOLUTION

The list box 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 returns 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 returns "Apple" rather than "Grape" when you select the third row. 

Type    Description
-------------------
Fruit   Apple
Fruit   Orange
Fruit   Grape
If you use the Description column for the BoundColumn property, this behavior does not occur because the Description column contains unique values.

Another way to resolve this behavior is to add an AutoNumber field to the underlying table, and then use the AutoNumber field for the BoundColumn property.

You can also use the ListIndex property of the combo box or list box to reference the selected row in the combo box or list box. Microsoft Access sets the ListIndex property value when an item is selected in a list box or list box portion of a combo box. The ListIndex property value of the first item in a list is 0, the value of the second item is 1, and so on. The code below refers to the example listed in the "More Information" section of this article. You can add this code to the AfterUpdate property of the combo box: 
Private Sub Manufacturer_AfterUpdate()
  Me![Stock Number] = Me![Manufacturer].Column(1, Me![Manufacturer].ListIndex)
  Me![Description] = Me![Manufacturer].Column(2, Me![Manufacturer].ListIndex)
End Sub
NOTE: If you use this code, remove the expressions that are assigned to the ControlSource properties of the Stock Number and Description controls.

Note that the ListIndex property value can be accessed indirectly by setting the BoundColumn property of a combo box or a list box to 0. If the BoundColumn property is set to 0, the underlying table field to which the combo box or list box is bound contains the same value as the ListIndex property setting.

MORE INFORMATION

Steps to Reproduce Behavior

  1. If you are working with Microsoft Access database, 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
    Data Type: Text

    Table1 Properties:
    Primary Key: Stock Number

    If you are working with Microsoft Access project, create the following table:

    Table: Table1

    Field Name: Manufacturer
    Data Type: Text

    Field Name: Stock Number
    Data Type: Int
    Allow Nulls: No

    Field Name: Description
    Date Type: Text

    Table1 Properties:
    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:
    Name: Manufacturer
    ControlSource: <leave empty>
    RowSourceType: Table/Query
    RowSource: Table1
    ColumnCount: 3
    BoundColumn: 1
    Width: 3

    Text box:
    Name: Stock Number
    ControlSource: =[Manufacturer].Column(1)

    Text box:
    Name: 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.
If you change the BoundColumn property of the Manufacturer list box to 2 instead of 1, the text boxes on the form display the correct information for all the rows in the list box because the second column contains unique values.

REFERENCES

For more information about combo boxes, click Microsoft Access Help on the Help menu, type combo boxes: what they are and how they work in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the ListIndex property, click Microsoft Access Help on the Help menu, type listindex property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb KB209146
©2004 Microsoft Corporation. All rights reserved.