Hello,

I have written a SQL statement called srtSQLSelect. It sums multiple fields with the same parent record. When I run it as a query in Access, the statement works fine. When I convert to VBA I get run-time error 3000, which does not have an error message. If I debug the code, "DoCmd.RunSQL strSQLInsert" is highlighted.

strSQLInsert includes a select query, and RunSQL only works on action queries, but I was advised that by declaring and combining statements this problem would be avoided.

Have I made another mistake or was I given bad advice?

Cheers!

Private Sub cmdCalculateTotal_Click()

Dim strSQLSelect As String
Dim strSQLDelete As String
Dim strSQLInsert As String

'Disable delete/append warnings
DoCmd.SetWarnings False

'Store total rebate for service request
strSQLSelect = "SELECT [tblResFactors].[nbrServiceRequestNumber], " & _
"Sum([tblDirectInstalls].[nbrTotalMeasureRebate]) As SumOfnbrTotalMeasureRebate " & _
"FROM tblResFactors INNER JOIN tblDirectInstalls " & _
"ON [tblResFactors].[pkResFactorsID] = [tblDirectInstalls].[fkResFactorsID] " & _
"WHERE ((([tblResFactors].[pkResFactorsID]) = [tblDirectInstalls].[fkResFactorsID])) " & _
"GROUP BY [tblResFactors].[nbrServiceRequestNumber]"

' Delete existing records
strSQLDelete = "DELETE * FROM tblDirectInstallSum"
DoCmd.RunSQL strSQLDelete

' Insert new records
strSQLInsert = "INSERT INTO tblDirectInstallSum " & strSQLSelect
DoCmd.RunSQL strSQLInsert

'Replace delete/append warnings
DoCmd.SetWarnings True

When I'm trying to trouble-shoot SQL constructed in VBA, I insert lines like this just before using those strings.

strSQLInsert = "INSERT INTO tblDirectInstallSum " & strSQLSelect
Debug.Print strSQLInsert

DoCmd.RunSQL strSQLInsert


that way you can SEE what the SQL will look like when it runs. Then you can spot any syntax errors.

Two other observations.
As an alternative to the RunSQL syntax, you might consider

CurrentDB.Execute strSQLInsert, dbFailOnError

That runs without raising an alert which means you don't have to run the risk of setting Warnings off and the remembering to set them back on.

Why are you trying to store sums? Storing calculated values is seldom, if ever, recommended: it's a violation of database normalization principles.

However, to answer your specific question, you're better off using the Execute method of the Database object (see what Allen Browne has in Action queries: suppressing dialogs, while knowing results for some of the reasons why:



By using the Execute method with the dbFailOnError option (and error handling), you might get a useful error message.

BTW, note that I fixed your Select statement: it was redundant to have the Where clause.

Thanks George and Doug,

I noted the fixed SQL statement and implemented both suggested fixes. Both returned the same error message.

Is it possible that my table is not formatted correctly? I'm using the same table that was created when I used the SQL statement in a "make table query"

Cheers

So what's the error message?

In order for your query to work, tblDirectInstallSum would need to have two fields in it. The first would have to be the same data type as field nbrServiceRequestNumber in table tblResFactors, and the second would have to have a numeric data type.

Hi Doug,

The error message was the same as before, run-time error 3000, which does not have an error message.

In order to work around the issue, we implemented a make table query which runs for every record each time a command button is used. We were hoping to sum data for only the current record to keep things quick, but this will work.

Thank you everyone for your input!!