ACC: Combo Box in Continuous Form Shows Incorrect Data

ID: Q128158

The information in this article applies to:

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.

Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record.

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.

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

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

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.

Create the following new macro:


      Macro Name              Macro Action
      ------------------------------------
      Refresh Product Combo   Requery

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

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

View the form in Form view.

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.

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.

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:

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

For more information about AutoLookup, search the Help Index for "AutoLookup queries," or ask the Microsoft Access 97 Office Assistant.

Additional query words: blank

Keywords : kbusage FmsCmbo
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb