Microsoft KB Archive/287520

= ACC2002: Problems Binding List Box Recordset in Datasheet View =

Article ID: 287520

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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

 Open the sample database Northwind.mdb. Open the Products form in Design view. On the View menu, click Properties. In the property sheet of the form, click the Format tab, and then set the DefaultView property to Datasheet . Click the CategoryID combo box to view its properties.</li> Clear the RowSource property of the combo box, and then set the RowSourceType property of the combo box to Value List .</li> On the View menu, click Code to view the module of the form.</li>  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 = &quot;SELECT CategoryID, CategoryName FROM Categories &quot; & _ &quot;ORDER BY CategoryName&quot; Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) Do Until rs.EOF Me.CategoryID.AddItem rs.Fields(&quot;CategoryID&quot;).Value & _ &quot;;&quot; & rs.Fields(&quot;CategoryName&quot;).Value rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save.</li> Close the form.</li> Open the Products form. Note that it opens in Datasheet view automatically.</li> Click the arrow in the Category combo box. Note that the list is filled with items from the Categories table.</li> Close the form.</li> Quit Microsoft Access.</li></ol>

Note that Microsoft Access quits successfully.

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> 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.

</li> Open the sample database Northwind.mdb.</li> Open the Products form in Design view.</li> In the property sheet of the form, click the Format tab, and then set the DefaultView property to Datasheet .</li> Click the CategoryID combo box to view its properties.</li> <li>On the Data tab, clear the RowSource property of the combo box.</li> <li>On the View menu, click Code to view the module of the form.</li> <li> 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 = &quot;SELECT CategoryID, CategoryName FROM Categories &quot; & _ &quot;ORDER BY CategoryName&quot; Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) Set Me.CategoryID.Recordset = rs End Sub </li> <li>On the File menu, click Close and Return to Microsoft Access.</li> <li>Save the form, and then close it.</li> <li>Open the Products form. Note that it opens in Datasheet view by default.</li> <li>Close the form.</li> <li>Quit Microsoft Access.</li></ol>

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.

Additional query words: pra

Keywords: kbbug KB287520

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.