ACC2002: Access Quits Unexpectedly When You Set Listbox Recordset (287493)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q287493
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

When you change the view of a form several times in succession, from Form view to Datasheet view to PivotTable view and then back to Form view, one of the following behaviors may occur:
  • Microsoft Access shuts down unexpectedly without returning any error message.
  • Access shuts down after returning the following error error message:
    Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience.
    The details of the error message indicate that Access caused an error in an unknown module.

CAUSE

This behavior can occur if you set the control's Recordset property after switching views. For example, you set the Recordset property after switching from Datasheet view to Form view.

RESOLUTION

To resolve this issue, use one of the following methods.

Set the RowSource Property of the Control

The RowSource property of the control allows you to specify the name of a table, query, or SQL statement to generate the list that the control will contain. To set the RowSource property, follow these steps:
  1. Open your form in Design view.
  2. On the View menu, click Properties to view the property sheet.
  3. Select the combo box or list box control to view its properties.
  4. On the Data tab in the property sheet, set the RowSourceType property to Table/Query.
  5. Set the RowSource property to the name of a table, query, or SQL statement.
  6. On the File menu, click Save.
  7. On the View menu, click Form View.

    Note that the combo box or list box control contains a list of items from the table, query, or SQL statement that you specified in the RowSource property.
  8. Switch to other views of the form, and then back to Datasheet view or Form view.
Note that Access does not quit, and the combo box or list box control still contains items from the table, query, or SQL statement that you specified in the RowSource property.

Use the AddItem Method to Add Items to the Control

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 to the combo box. To use the AddItem method to fill the control, follow these steps:

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. NOTE: The sample code in step 4 works only in an Access database (*.mdb).
  1. Perform steps 1-5 of the "Steps to Reproduce the Problem" section that follows.
  2. Set the combo box's RowSourceType property to Value List.
  3. On the View menu, click Code to view the form's module.
  4. 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)
   Do Until rs.EOF
      Me.CategoryID.AddItem rs.Fields("CategoryID").Value & _
      ";" & rs.Fields("CategoryName").Value
      rs.MoveNext
   Loop
End Sub
  5. Press ALT+Q to return to Access.
  6. On the File menu, click Save.
  7. On the View menu, click Form View.

    Note that the Category control contains a list of items from the Categories table.
  8. On the View menu, click Datasheet View.

    Note that the Category control contains a list of items from the Categories table.
  9. Switch to other views of the form and then back to Datasheet view or Form view.
Note that Access does not quit, and combo box contains items from the Categories table.

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. Open the sample database Northwind.mdb.
  2. In the Database window, click Forms, click Products, and then click Design.
  3. On the View menu, click Properties to display the property sheet.
  4. Select the CategoryID combo box to view its properties.
  5. On the Data tab of the property sheet, clear the 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 db As DAO.Database
Private rs As DAO.Recordset
Private pFormView As Integer

Private Sub Form_Open(Cancel As Integer)

    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
    pFormView = Me.CurrentView
End Sub

Private Sub Form_Current()
    If Me.CurrentView <> pFormView Then
        Set Me.CategoryID.Recordset = rs
        pFormView = Me.CurrentView
    End If

End Sub

Private Sub Form_ViewChange(ByVal Reason As Long)
    pFormView = Me.CurrentView
End Sub
  8. Press ALT+Q to return to Access.
  9. On the File menu, click Save.
  10. On the View menu, click Form View.

    Notice that the CategoryID combo box is filled with items from the Categories table, which indicates that the control is bound to a recordset.
  11. On the View menu, click Datasheet View.
  12. On the View menu, click PivotTable View.
  13. On the View menu, click Form View.
  14. On the View menu, click Design View.
Notice that Access quits unexpectedly with no error message.

REFERENCES

For additional information, 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

Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbbug kberrmsg kbnofix KB287493
©2004 Microsoft Corporation. All rights reserved.