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

• Microsoft Access 2000

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 need to reference the Microsoft DAO 3.6 Object Library.

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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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.

Additional query words: prb # error errors message

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb