ACC: ItemData Property Returns Value from Previous Row

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

The ItemData property of a combo box or list box does not return the value from the row that you specified in the rowindex argument; instead, it returns the value from the previous row.

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.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

CAUSE

The ColumnHeads property for the combo box or list box is set to Yes.

RESOLUTION

Use a condition to check the ColumnHeads property. If it is set to Yes, add 1 to the row index. The following example demonstrates how to add 1 to the row index if the ColumnHeads property is set to Yes and then to pass it to the ItemData property:

1. Open the sample database Northwind.mdb.

2. Open the Orders form in Design view.

3. Set the ColumnHeads property of the CustomerID combo box to Yes.

4. Add a textbox to the form with the following properties:

         Form: Orders
         -------------------------------------------
         Text Box:
         Name: ItemDataTest
         ControlSource: =MyItemData("CustomerID", 0)
   

5. On the View menu, click Code to view the form's module.

6. Type the following procedure:

         Function MyItemData(ctlName As String, RowIndex As Long)
            On Error Resume Next
            If Me(ctlName).ColumnHeads Then RowIndex = RowIndex + 1
            MyItemData = Me(ctlName).ItemData(RowIndex)
         End Function
   
   

7. Open the form in Form view. Note that the ItemDataTest text box contains the value "ALFKI", which is the first data item in the CustomerID combo box.

STATUS

This behavior is by design.

MORE INFORMATION

The ItemData property returns the data in the bound column of the specified row in a combo box or list box. Rows in combo and list boxes are indexed starting with zero. For example, to return the item in the sixth row of a combo box, you would specify 5 for the ItemData property's row index. If the ColumnHeads property for a combo box or list box is set to Yes, the column name is displayed as the first item in the list. Microsoft Access treats this row as the first data item instead of the first row in the control's row source.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Open the Orders form in Design view.

3. Set the ColumnHeads property of the CustomerID combo box to Yes.

4. Add a textbox to the form with the following properties:

         Form: Orders
         -----------------------------------------
         Text Box:
         Name: ItemDataTest
         ControlSource: =[CustomerID].[ItemData](0)
   

5. Open the form in Form view. Note that the value of the ItemDataTest text box is "Customer ID" instead of the CustomerID "ALFKI" in the first row of the row source.

REFERENCES