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?
Private Sub cmdCalculateTotal_Click()
Dim strSQLSelect As String
Dim strSQLDelete As String
Dim strSQLInsert As String
'Disable delete/append warnings
'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"
' Insert new records
strSQLInsert = "INSERT INTO tblDirectInstallSum " & strSQLSelect
'Replace delete/append warnings