SQL 'insert into' in vba query not updating table

I am attempting to use VBA in access 2003 to run a SQL 'insert into' command. The SQL command is part of a loop such that the code updates with a variable string on each pass. The code runs and works, however when running over several passes the SQL does not update the required tables on all passes. However the code does update on some of the passes, if i run the VBA code again then a different set of passes are updated to the table by the SQL. The SQL takes about 2 minutes to run. The VBA is show below. How can i make the code write to the tables on each pass?

Private Sub Holding_Click()
On Error GoTo err

Dim i As String
Dim j As String
Dim a As String
Dim CON As ADODB.Connection
Dim rstWEEK As New ADODB.Recordset


Set CON = CurrentProject.Connection
CON.CommandTimeout = 0

rstWEEK.Open "week", CON, adOpenKeyset, adLockOptimistic

i = InputBox("Start week")
j = InputBox("End week")

rstWEEK.MoveFirst
Do Until rstWEEK!week = i
rstWEEK.MoveNext
Loop

DoCmd.RunSQL "delete tbl_summary"

exitloop2:

rstWEEK.MovePrevious
a = rstWEEK!week
rstWEEK.MoveNext
i = rstWEEK!week
rstWEEK.MoveNext
SysCmd acSysCmdSetStatus, i

DoCmd.RunSQL "insert into tbl_summary SELECT 'holding' as measure, '" & i & "' as data_week, week_num.year_week as year_week, year_number, week_number, SUM([tblOrderBookDetail~wk" & i & "].ACTUAL_SALE_VALUE - [tblOrderBookDetail~wk" & i & "].VAT_PAYABLE) AS value " _
& "FROM [tblOrderBookDetail~wk" & i & "] LEFT OUTER JOIN week_num ON [tblOrderBookDetail~wk" & i & "].Delivery_Date = week_num.date_number LEFT OUTER JOIN [tblOrderBookDetail~Wk" & a & "] ON [tblOrderBookDetail~wk" & i & "].SALE_ITEM = [tblOrderBookDetail~Wk" & a & "].SALE_ITEM AND [tblorderbookdetail~wk" & i & "].Reference_No = [tblOrderBookDetail~Wk" & a & "].Reference_No " _
& "WHERE ([tblOrderBookDetail~wk" & i & "].Reference_No IN (SELECT reference_no FROM [tblorderbookdetail~wk" & i & "] WHERE sale_item LIKE 'zzg%')) AND ([tblOrderBookDetail~Wk" & a & "].Reference_No IS NULL) AND ([tblOrderBookDetail~Wk" & a & "].SALE_ITEM IS NULL) " _
& "GROUP BY week_num.year_week, week_num.year_number, week_num.week_number;"

If i = j Then
GoTo exitloop1
Else
GoTo exitloop2
End If

exitloop1:

SysCmd acSysCmdRemoveMeter
rstWEEK.Close
CON.Close

MsgBox "Done"

err_exit:
Exit Sub

err:
MsgBox err.Description
GoTo err_exit
End Sub


Thanks
Barney

I'd first remove the error trapping and see if any unexpected errors pop up.

error trapping seemed to be fine, however the following link has resolved the problem


http://forums.aspfree.com/showthread.php?p=205000#post205000