Microsoft KB Archive/244146

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

Article ID: 244146

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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
 Open the sample database Northwind.mdb. Click Forms under Objects, and then click New. 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. In the Form Wizard, add all the fields, and then click Finish. View the resulting form in Design view.</li> Add a text box to the form with following control source:

=Count([ProductID])

</li> View the form in Form view. Note that the text box counts the number of Product IDs in the table.</li> View the form again in Design view.</li> Open the properties sheet of the form, and then click the Data tab.</li> Delete the word "Products" from the RecordSource property box, and leave the box blank.</li> Click the Event tab, and click the Build (...) button for the OnLoad event.</li> In the Choose Builder dialog box, click Code Builder, and then click OK.</li>  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 </li> Preview the form in Form View. Note that #Error is returned in the text box.</li></ol>

NOTE: To make this example work, see the "Resolution Section" earlier in this article.

Additional query words: prb # error errors message

Keywords: kbnofix kbprb kbprogramming KB244146

-

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

© Microsoft Corporation. All rights reserved.