ACC2002: Problems Binding List Box Recordset in Datasheet View (287520)


The information in this article applies to:

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

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

SYMPTOMS

When you bind a list box or a combo box control to a recordset, you may see the following problems when you view the form in Datasheet view:
  • The list portion of the list box or the combo box is blank.
  • Microsoft Access does not shut down when you try to close it.
For additional information about problems with setting the Recordset property of list boxes or combo boxes, click the article number below to view the article in the Microsoft Knowledge Base:

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

CAUSE

The Recordset property of a combo box or a list box control does not work if the code is called during the Open event of the form and the form has been opened in Datasheet view. Additionally, controls that are bound to DAO recordset objects prevent Access from shutting down if the form is open in Datasheet view.

RESOLUTION

There are several possible workarounds for this problem. The first workaround is the preferred workaround.

Call the Code During the Load Event of the form

Set the control's Recordset property during the Load event of the form instead of during the Open event. This event allows the control's list portion to be filled and allows Access to shut down if you are using DAO Recordset objects.

Open the Form in Form View or in Continuous Forms View

Instead of opening the form in Datasheet view, open the form in Form view or in Continuous Forms view. It is possible to simulate Datasheet view by opening the form in Continuous Forms view.

Set the RowSource Property of the Control

Instead of setting the Recordset property of the control, set the RowSource property of the control. The RowSource property allows you to use a table name, a query name, or an SQL statement to fill the list portion of the control. You can set the RowSource property of the control in Design view of the form, without having to write VBA code.

Use the AddItem Method to Fill the List

The AddItem method allows you to programmatically add values to the list portion of the control. This technique allows you to open a recordset, and enumerate through it to add values from it to the combo box or the list box. For an example of how to use the AddItem method to fill a control, follow these steps:
  1. 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.

  2. Open the sample database Northwind.mdb.
  3. Open the Products form in Design view.
  4. On the View menu, click Properties.
  5. In the property sheet of the form, click the Format tab, and then set the DefaultView property to Datasheet.
  6. Click the CategoryID combo box to view its properties.
  7. Clear the RowSource property of the combo box, and then set the RowSourceType property of the combo box to Value List.
  8. On the View menu, click Code to view the module of the form.
  9. Add the following Visual Basic for Applications code to the 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)
   Do Until rs.EOF
      Me.CategoryID.AddItem rs.Fields("CategoryID").Value & _
                          ";" & rs.Fields("CategoryName").Value
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
   Set db = Nothing
End Sub
  10. On the File menu, click Close and Return to Microsoft Access.
  11. On the File menu, click Save.
  12. Close the form.
  13. Open the Products form. Note that it opens in Datasheet view automatically.
  14. Click the arrow in the Category combo box. Note that the list is filled with items from the Categories table.
  15. Close the form.
  16. Quit Microsoft Access.
Note that Microsoft Access quits successfully.

STATUS

Microsoft has confirmed that this is a problem in Microsoft Access.

MORE INFORMATION

Steps to Reproduce Behavior

  1. 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.

    WARNING: Following these steps will prevent Microsoft Access from closing on your computer. Make sure you save and close any open work on your computer before following these steps.

  2. Open the sample database Northwind.mdb.
  3. Open the Products form in Design view.
  4. In the property sheet of the form, click the Format tab, and then set the DefaultView property to Datasheet.
  5. Click the CategoryID combo box to view its properties.
  6. On the Data tab, clear the RowSource property of the combo box.
  7. On the View menu, click Code to view the module of the form.
  8. Add the following Visual Basic for Applications code to the module:
    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
  9. On the File menu, click Close and Return to Microsoft Access.
  10. Save the form, and then close it.
  11. Open the Products form. Note that it opens in Datasheet view by default.
  12. Close the form.
  13. Quit Microsoft Access.
Note that the database closes but that Microsoft Access does not quit. In order to shut down Microsoft Access, you must use Windows Task Manager to end the task.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbbug KB287520
©2003 Microsoft Corporation. All rights reserved.