One cliff left to climb! INSERT INTO is copying incorrect records

[EagleOne]

2003

All works well except the INSERT INTO.

By that I mean, I have a routine which finds and deletes offsetting dollar-amount records - if
certain conditions are met. I have confirmed that the records deleted are correct.

The routine cycles through CHOOSERev (Table) until EOF. During that process, there is a negative
dollar-amount and a positive dollar-amount.

For audit-trail purposes, I want to keep track of all the deleted records - both the positive and
negative records.

So, immediately before the Negative record is deleted I issue these commands:

StrSQL = _
"INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev WHERE " & StartActualAmt & " = Amt And" & _
" Amt <= 0 And ((DOV_NUMBER = '" & StartDovNmbr & "') Or (DOC_NUMBER = '" & StartDocNmbr & "'))"
dBs.Execute StrSQL, dbFailOnError

Immediately before the Positive record is deleted I issue these commands:
(NOTE: the only difference is Amt >= 0 here vs Amt <= 0 above)

StrSQL = _
"INSERT INTO CHOOSEOffsets SELECT TOP 1 * FROM CHOOSERev WHERE " & StartActualAmt & " = Amt And" & _
" Amt >= 0 And ((DOV_NUMBER = '" & StartDovNmbr & "') Or (DOC_NUMBER = '" & StartDocNmbr & "'))"
dBs.Execute StrSQL, dbFailOnError

The command is executed except that the exact desired record occurs about 50% of the time.
StrSQL string ever fails but its execution includes undesired records, sometimes it includes
multiple (the multiples all negative) records.

If my records were unique, I believe that the above command work fine. I do not have that luxury
though as the data is not under my control.

Is there a more accurate method (modification?) that I could use which would copy the exact single
record to be deleted (Negative amount; then the offsetting Positive amount - order not important)
from CHOOSERev to CHOOSEOffsets?

Any thoughts appreciated.

Thanks EagleOne

 

[EagleOne]

Currently, I am reviewing that concept of

1) marking the records for deletion
2) moving or copying those records to CHOOSEOffsets
3) deleting those records from CHOOSERev

I have never used: (any thoughts if it is usable in this case?)

Connection?
adAffectCurrent
adAffectGroup
adFilterAffectedRecords

BTW, to delete records I am currently using:

Set rs = dBs.OpenRecordset("CHOOSERev", dbOpenDynaset)

Do until rs.EOF (presented in condensed form)
If (Conditions)
rs.delete
Endif
rs.movenext
Loop

TIA EagleOne