ACC: Combo Box in Continuous Form Shows Incorrect Data

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When you select a row in a combo box in a continuous form or a form that is open in Datasheet view, the text portion of the combo box in other records appears empty.

CAUSE

The behavior occurs under the following conditions:

• The form is a continuous form, or it is open in Datasheet view so that you can view multiple records.
• The combo box's RowSource property is set to a parameterized query to limit the number of rows in the combo box based on criteria in another field in the form.
• The combo box criteria for the current record eliminate the rows that were selected in the other records.

When the criteria for the query that the combo box is based on change, rows that were selected in the other records may be eliminated from the recordset. As a result, the non-BoundColumn values are no longer available to be displayed.

RESOLUTION

Do not hide the BoundColumn field. If you want to see a different column, use the DLookup() function or the AutoLookup technique to display the information you want in another control on the form.

MORE INFORMATION

Steps to Reproduce Behavior

The following example creates two synchronized combo boxes. The selection you make in the first combo box determines the contents of the second combo box.

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

2. Create a new table called My Products with the following properties:

         Table: My Products
         --------------------------
         Field Name: Category ID
            Data Type: Text
            FieldSize: 4
         Field Name: Product ID
            Data Type: Number
            FieldSize: Long Integer
   
   

3. Create the following new query:

         Query: Product Combo Query
         ------------------------------------------------------
         Type: Select
         Field: ProductID (or Product ID in versions 1.x and 2.0)
            Table: Products
         Field: ProductName (or Product Name in versions 1.x and 2.0)
            Table: Products
         Field: CategoryID (or Category ID in versions 1.x and 2.0)
            Table: Products
            Criteria: Forms![My Products Form]![Category Combo]
   
      Click the check box in the Show row in the Category ID column to
      clear the check box.
   
   

4. Create the following new macro:

         Macro Name              Macro Action
         ------------------------------------
         Refresh Product Combo   Requery
   
         Refresh Product Combo Actions
         ------------------------------
         Requery
            Control Name: Product Combo
   
   

5. Create a new form with the following properties and controls based on the My Products table. Save the form as My Products Form.

         RecordSource: My Products
   

         Combo box:
            Name:          Category Combo (Control Name in version 1.x)
            ControlSource: Category ID
            RowSourceType: Table/Query
            RowSource:     Categories
            ColumnCount:   2
            BoundColumn:   1
            ColumnWidth:   .2 in
            Left:          1.1
            Top:           0
            After Update:  Refresh Product Combo
   
         Combo box:
            Name:          Product Combo
            ControlSource: Product ID
            RowSourceType: Table/Query
            RowSource:     Product Combo Query
            ColumnCount:   2
            ColumnWidth:   0
            BoundColumn:   1
            Left:          3.1
            Top:           0
   
         Detail Section:
            Height:        .17
   
   

6. View the form in Form view.

7. In the first record, select Beverages (or BEVR in version 1.x) in the Category Combo combo box and Chai in the Product Combo combo box.

8. In the second record, select Beverages (or BEVR in version 1.x) in the Category Combo combo box and Chang in the Product Combo combo box.

9. In the third record, select Condiment (or COND in version 1.x) in the Category Combo combo box. Note that Chai and Chang now show the Category ID number instead of the text (in versions 1.x and 2.0, the information in the field disappears entirely).

REFERENCES

For more information about the AutoLookup technique, please see the following article in the Microsoft Knowledge Base:

  ARTICLE-ID: Q95643
  TITLE     : ACC: Row Fix-up in the Northwind.mdb Order Form