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

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.

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

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:

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.