ACC2002: Subform Combo Box Displays Incorrect Data in an Access Project (295252)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q295252
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you insert a new record into a main form that contains a linked subform that has a combo box, the value shown in the text portion of the combo box is incorrect.

CAUSE

The subform is linked to the main form by means of an Identity field, and the main form has been filtered so that only a subset of the records is showing.

RESOLUTION

There are two possible resolutions for this problem.

Remove the Identity Field from the Main Form

The easiest solution is to remove the Identity field, or the field that links the forms, from the main form. You can still use the Identity field to link the main form and the subform in the link properties of the subform. However, the combo box in the subform will display the correct value if the Identity field is not present on the main form.

Programmatically Assign the Row Source of the Combo Box

A second solution is to bind and unbind the RowSource property of the combo box. Because the problem occurs only during the insertion of a new record, you can clear the RowSource property of the combo box during the BeforeInsert event, and then bind it back during the AfterUpdate and Undo events. For example, you can do this by following these steps:
  1. First, follow the steps in the "Steps to Reproduce Behavior" section later in this article.
  2. Then, open the main form in Design view.
  3. On the View menu, click Code to view the module of the form.
  4. Add the following code to the module:
    Option Compare Database
Option Explicit

Dim strRowSource As String

Private Sub Form_AfterUpdate()
    Dim ctl As Access.Control

    'Reference the combo box control on the subform. Be sure
    'to change this code to refer to the proper control on your
    'subform.
    Set ctl = Me.Controls("Products1").Form.Controls("CategoryID")

    'Assign the RowSource property back to the string we stored
    'during the BeforeInsert event.
    ctl.RowSource = strRowSource
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim ctl As Access.Control

    'Reference the combo box control on the subform. Be sure
    'to change this code to refer to the proper control on your
    'subform.
    Set ctl = Me.Controls("Products1").Form.Controls("CategoryID")

    'Store the current RowSource property and then clear it.
    strRowSource = ctl.RowSource
    ctl.RowSource = ""
End Sub

Private Sub Form_Undo(Cancel As Integer)
    Dim ctl As Access.Control

    'Reference the combo box control on the subform. Be sure
    'to change this code to refer to the proper control on your
    'subform.
    Set ctl = Me.Controls("Products1").Form.Controls("CategoryID")

    'Assign the RowSource property back to the string we stored
    'during the BeforeInsert event.
    ctl.RowSource = strRowSource
End Sub
  5. On the File menu, click Close and Return to Microsoft Access.
  6. On the View menu, click Form View. Note that the subform displays product records that correspond to the current category record on the main form.
  7. On the Records menu, click Data Entry. This clears both the main form and the subform so that no records are displayed.
  8. Begin typing a new category record into the main form, but do not save the record.
Note that the CategoryID field on the main form contains the value 1, even though this value has already been used. After saving the record on the main form, note that the Identity field is populated with its assigned value from the server, and the text portion of the combo box in the subform correctly displays data.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When you insert a record into a table that contains an Identity field, Microsoft Access automatically populates the Identity field with a value one greater than the maximum identity value of the records that the form has retrieved.

For example, assume that the main form has been filtered so that the maximum value in the Identity field is 10. When you insert a new record into the main form, Microsoft Access automatically populates the Identity field with the next highest value, 11, even though this value may already have been used. When the record on the main form is saved, this value is discarded in place of the actual Identity value assigned by the server. However, before the record is saved, any control referencing the Identity field will display the same incorrect value.

Steps to Reproduce the Behavior

  1. CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

  2. Open the sample project NorthwindCS.adp.
  3. Close the Main Switchboard form if it appears.
  4. On the View menu, point to Database Objects, and then click Forms.
  5. Click New.
  6. In the New Form dialog box, click Form Wizard, and then click OK.
  7. In the Tables/Queries drop-down box, click the Products table.
  8. Add the ProductID, ProductName, and CategoryID fields to the Selected Fields list, and then click Next.
  9. Click Datasheet for the form layout, and then click Finish. Note that a new form named Products1 is created and opens in Datasheet view.
  10. On the View menu, click Design View to view the design of the new form.
  11. Click the CategoryID text box, on the Format menu, point to Change To, and then click Combo Box.
  12. Set the properties of the combo box as follows:
       Combo box
   -------------------------
   Name: CategoryID
   ControlSource: CategoryID
   RowSource: Categories
   ColumnCount: 2
   ColumnWidths: 0"; 1"
   BoundColumn: 1
   List Width: 1"
  13. On the View menu, click Datasheet View.
  14. Click the CategoryID column, and then on the Format menu, click Column Width.
  15. Click Best Fit. This automatically resizes the CategoryID column so that all data is visible.
  16. On the File menu, click Save, and then close the form.
  17. Open the Categories form in Design view.
  18. On the View menu, click Field List to display the field list of the form.
  19. Drag the CategoryID field from the field list to the detail section of the form.
  20. Click the Product List subform, and then delete it.
  21. From the Database window, drag the Products1 form into the detail section of the form to create a new subform.
  22. Set the properties of the subform as follows: 
       Subform
   ----------------------------
   Name: Products1
   LinkChildFields: CategoryID
   LinkMasterFields: CategoryID
   Top: 1.5"
   Width: 5.5"
  23. On the View menu, click Form View. Note that the subform displays product records that correspond to the current category record on the main form.
  24. On the Records menu, click Data Entry. This clears both the main form and the subform so that no records are displayed.
  25. Begin typing a new category record into the main form.
Note that the text portion of the combo box in the subform displays the value Beverages, which is the first category in the Categories table.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

295225 ACC2002: Write Conflict Error When You Add a Record in Multi-Table View

Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbbug KbClientServer kbnofix KB295252
©2003 Microsoft Corporation. All rights reserved.