Microsoft KB Archive/109827

= ACC: List Box Column Property Gets Incorrect Row Information =

Article ID: 109827

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q109827



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



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 you want it to, but when you bind 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

If you use the Description column for the BoundColumn property, the behavior will not occur because the Description column contains unique values.

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.

In Microsoft Access 7.0 and 97, the ListIndex property of the combo box or list box can be used as a pointer to the correct row in the combo box or list box. Non-unique columns in a combo box or list box can be resolved by setting the BoundColumn property to 0 (zero). This will allow a text box on a form to be populated with, for example, a value in combox.column(1) when no columns with the combo box contain unique values.



Steps to Reproduce Behavior
  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   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                          Create the following new form:

<pre class="fixed_text">     Form: Test1 Combo box: Manufacturer ControlSource: 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) </li> View the form in Form view.</li> Select the first row in the combo box. Note that the Stock Number and Description boxes correctly display "1" and "Mouse."</li> Select the third row in the combo box. Note that the Stock Number and Description boxes still display the information from the first row.</li> Select the fourth row in the combo box. Note that the Stock Number and Description boxes display the correct information for the fourth row.</li></ol>

If you change the BoundColumn property of the Manufacturer list box to 2 instead of 1, the text boxes on the form will display the correct information for all the rows in the list box because the second column contains unique values.

<div class="references_section">