ACC2000: #Error with Aggregate Function on a Form Based on ADO Recordset (244146)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q244146
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 use aggregate functions, such as Sum, Count, Min, and Max, you may receive a #Error error when the form is based on an ActiveX Data Object (ADO) recordset.

CAUSE

You cannot use aggregate functions on an ADO recordset in Microsoft Access.

RESOLUTION

Base the form on a Data Access Objects (DAO) recordset instead. For example, in the sample database Northwind.mdb, if you want to base the Products form on a recordset object and also want to use aggregate functions on the form, place code in the OnLoad event of the form that bases the form on a DAO recordset.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

The code would look like the following: 
Private Sub Form_Load()
    Dim dbs As DAO.Database 
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT * FROM Products")
    Set Me.Recordset = rst
End Sub

MORE INFORMATION

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.

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.
  2. Click Forms under Objects, and then click New.
  3. In the New Form box, click Form Wizard, click Products in the Choose the table or query where the object's data comes from box, and then click OK.
  4. In the Form Wizard, add all the fields, and then click Finish.
  5. View the resulting form in Design view.
  6. Add a text box to the form with following control source:

    =Count([ProductID])

  7. View the form in Form view. Note that the text box counts the number of Product IDs in the table.
  8. View the form again in Design view.
  9. Open the properties sheet of the form, and then click the Data tab.
  10. Delete the word "Products" from the RecordSource property box, and leave the box blank.
  11. Click the Event tab, and click the Build (...) button for the OnLoad event.
  12. In the Choose Builder dialog box, click Code Builder, and then click OK.
  13. Type the following code:
    Private Sub Form_Load()
    Dim adoRS As New ADODB.Recordset
    With adoRS
        .ActiveConnection = CurrentProject.Connection
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .Open "SELECT * FROM Products"
    End With
    Set Me.Recordset = adoRS
    adoRS.Close
    Set adoRS = Nothing
End Sub
  14. Preview the form in Form View. Note that #Error is returned in the text box.
NOTE: To make this example work, see the "Resolution Section" earlier in this article.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbnofix kbprb kbProgramming KB244146
©2004 Microsoft Corporation. All rights reserved.