ACC: Combo Box in Continuous Form Shows Incorrect Data

Last reviewed: May 12, 1997
Article 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.

  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

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 : FmsCmbo kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 12, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.