Append Query problem

[ionic-fire via AccessMonster.com]

I have created an append query in vba code which is inside a For...Next loop.
I create an SQL statement to find matching related records in one table, and
then insert these matching records into a temporary table. I repeat for each
value of unique primary key that is provided (in an array).

The issue I have is that the append query only works for the first matching
record, not the subsequent records. I have verified that correct SQL
statements are generated during each iteration of the loop, but for some
reason no inserts occur after the first one. The temp table is cleared at the
beginning of the subroutine.

Any input would be helpful, I thank you very much for any assistance.


Here is my code:
====================
If boolNoRecords = False Then 'the "No Records Found" flag is not set.

For x = 0 To UBound(lngArrayTestID)
If lngArrayTestID(x) > 0 Then 'do not allow TestID = 0
condition

'lngArrayTestID() contains non-duplicated testID values
(primary key).

strTempSQL1 = "INSERT INTO " & strResultsTable

strTempSQL1 = strTempSQL1 & " SELECT * FROM "
strTempSQL1 = strTempSQL1 & strSourceQuery2 & " WHERE "
strTempSQL1 = strTempSQL1 & "TestID = " & lngArrayTestID(x)

DoCmd.SetWarnings False 'turn off warnings
DoCmd.RunSQL strTempSQL1 'execute "INSERT INTO" query
DoCmd.SetWarnings True 'turn on warnings


End If
strTempSQL1 = "" 'make sure it is cleared before starting

Next x 'repeat the process for all TestID values in
lngArrayTestID()
End If 'end the If boolNoRecords = False clause

 

[Dale Fye]

My first thought would be that you have a unique index on one of your columns
in the temp table.

First thing I would do is replace:

Docmd.SetWarnings False
DoCmd.RunSQL strTempSQL1 'execute "INSERT INTO" query
DoCmd.SetWarnings True 'turn on warnings

With:

Currentdb.Execute strTempSQL1, dbFailOnError

This will eliminate the warnings, but will cause an error (which you should
provide an error handler for) if the insert fails. Using the error number
and description, you should be able to figure out why subsequent inserts are
not working.

HTH
Dale

 

[ionic-fire via AccessMonster.com]

Indeed, that was the root problem. I had several unique indexes set in my
temp table. I turned off Indexing for all the columns, and now the append
query works splendidly. Thank you very much!

My first thought would be that you have a unique index on one of your columns
in the temp table.

First thing I would do is replace:

Docmd.SetWarnings False
DoCmd.RunSQL strTempSQL1 'execute "INSERT INTO" query
DoCmd.SetWarnings True 'turn on warnings

With:

Currentdb.Execute strTempSQL1, dbFailOnError

This will eliminate the warnings, but will cause an error (which you should
provide an error handler for) if the insert fails. Using the error number
and description, you should be able to figure out why subsequent inserts are
not working.

HTH
Dale

I have created an append query in vba code which is inside a For...Next loop.
I create an SQL statement to find matching related records in one table, and

[quoted text clipped - 37 lines]

lngArrayTestID()
End If 'end the If boolNoRecords = False clause