Insert From Error

[johnlbw]

Im totally lost

Is there something I need to dp to pause my code from executing until my Insert query has finished

the following works - during Debug line by line or if I pop in a msgbox after it.

dont try to validate the Insert into statement - IT DOES WORK

strSQL =


INSERT INTO tblReportTotals ( [AREA 1], STREET, [Date of Construction], [Property Type], STRATA, SurveyID, [Question heading], Answer, index1, index2, SumOf1, SumOf2, SumOf3, SumOf4, SumOf5, SumOf6, SumOf7, SumOf8, SumOf9, SumOf10, [SumOf11-15], [SumOf16-20], [SumOf21-25], [SumOf26-30] )

SELECT qryTotalsForReport.[AREA 1], qryTotalsForReport.STREET, qryTotalsForReport.[Date of Construction], qryTotalsForReport.[Property Type], qryTotalsForReport.STRATA, qryTotalsForReport.SurveyID, qryTotalsForReport.[Question heading], qryTotalsForReport.Answer, qryTotalsForReport.index1, qryTotalsForReport.index2, qryTotalsForReport.SumOf1, qryTotalsForReport.SumOf2, qryTotalsForReport.SumOf3, qryTotalsForReport.SumOf4, qryTotalsForReport.SumOf5, qryTotalsForReport.SumOf6, qryTotalsForReport.SumOf7, qryTotalsForReport.SumOf8, qryTotalsForReport.SumOf9, qryTotalsForReport.SumOf10, qryTotalsForReport.[SumOf11-15], qryTotalsForReport.[SumOf16-20], qryTotalsForReport.[SumOf21-25], qryTotalsForReport.[SumOf26-30]

FROM qryTotalsForReport

dc.CommandText = strSQL
Dim intRecs As Integer = 0
intRecs = dc.ExecuteNonQuery()

if I call a msgbox here it works - if not then there is no data for the next query to read

the issue is - this data is needed for another query that runs imediatly after this one

I could run the next one from qryTotalsForReport but that is just a work arround.

pulling my hair out with this one!!!

John

 

[vis781]

Have you tried ye old faithful..

Application.DoEvents

 

[johnlbw]

I don't want the code to keep running.

I want it to wait until all the records in the insert have been processed.

In another part of the app I need to insert the results of the query into a table as I use automation to get access to output a report as a snapshot based on criteria given at runtime.

Because the insert hasnt finished before the snapshot is run - it is using an empty table

 

[cjard]

ExecuteNonQuery() is a blocking call - your code will pause until the insert has completed.

You need to post more code to give us an idea of context