ACC2002: Combo Box or List Box Recordset Is Lost When You Switch Form Views (282358)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q282358
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

In Microsoft Access, after you set the Recordset property of a list box or combo box, the list is empty after you change between form views (such as Datasheet view, PivotTable view, or PivotChart view).

CAUSE

This behavior occurs because Access resets the control's Recordset property when you switch between different views of the form.

RESOLUTION

To work around this issue, use one of the following methods.

Method 1: Set the RowSource Property of the Control

Instead of setting the control's Recordset property, set its RowSource property in Design view of the form instead. The RowSource property allows you to use a table name, query name, or SQL statement to fill the list portion of the control. You can set the RowSource property of the control in Design view without having to write Microsoft Visual Basic for Applications (VBA) code.

Method 2: Use VBA Code to Fill the List

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. It is possible to use the AddItem method to programmatically add items to the list portion of a combo box or list box control in an Access form. This technique allows you to open a recordset and enumerate through it to add values to the combo box or list box. To use the AddItem method to fill the control, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
  2. Open the Products form in Design view.
  3. On the View menu, click Properties to display the property sheet.
  4. Select the CategoryID combo box and view its properties.
  5. Clear the CategoryID combo box's RowSource property.
  6. Set the CategoryID combo box's RowSourceType property to Value List.
  7. On the View menu, click Code to view the form's module.
  8. On the Tools menu, click References.
  9. Verify that the Microsoft ActiveX Data Objects 2.5 (or higher) library is selected, and then click OK to close the References dialog box.
  10. Add the following code to the form's module:
    Private Sub Form_Open(Cancel As Integer)
   Dim rs As ADODB.Recordset
   Dim strSQL As String

   strSQL = "SELECT CategoryID, CategoryName FROM Categories " & _
            "ORDER BY CategoryName"
   
   Set rs = New ADODB.Recordset
   With rs
      .ActiveConnection = CurrentProject.AccessConnection
      .Source = strSQL
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
      Do Until .EOF
         Me.CategoryID.AddItem .Fields("CategoryID").Value & _
            ";" & .Fields("CategoryName").Value
         .MoveNext
      Loop
   End With
End Sub
  11. On the File menu, click Close and Return to Microsoft Access.
  12. On the File menu, click Save.
  13. Close the form.
  14. Open the Products form in Form view.
  15. Click the drop-down arrow to the right of the CategoryID combo box.

    Note that the list is filled with items from the Categories table.
  16. On the View menu, click Datasheet View.
  17. Click the drop-down arrow to the right of the CategoryID column.
Note that the list still contains items from the Categories table as expected.

STATUS

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

MORE INFORMATION

Steps To Reproduce the Problem

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Access and open the sample database Northwind.mdb.
  2. Open the Products form in Design view.
  3. On the View menu, click Properties to display the property sheet.
  4. Select the CategoryID combo box and view its properties.
  5. Clear the CategoryID combo box's RowSource property.
  6. On the View menu, click Code to view the form's module.
  7. Add the following code to the form's module:
    Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String

   strSQL = "SELECT CategoryID, CategoryName FROM Categories " & _
            "ORDER BY CategoryName"
   Set db = CurrentDb
   Set rs = db.OpenRecordset(strSQL)
   Set Me.CategoryID.Recordset = rs
End Sub
  8. On the File menu, click Close and Return to Microsoft Access.
  9. On the File menu, click Save.
  10. On the View menu, click Form View.
  11. Click the drop-down arrow to the right of the CategoryID combo box.

    Note that the list is filled with items from the Categories table, indicating that the control is bound to a recordset.
  12. On the View menu, click Datasheet View.
  13. Click the drop-down arrow to the right of the CategoryID column.
Note that the list is empty, indicating the control is no longer bound to the recordset.

REFERENCES

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

287520 ACC2002: Problems Binding List Box Recordset in Datasheet View

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbbug kbpending KB282358
©2004 Microsoft Corporation. All rights reserved.