Append query works, but doesn't...

[ionic-fire via AccessMonster.com]

I have some custom append queries wrapped inside a transaction. They execute
just fine using the db.Execute, strSQL, dbFailOnError method.

Problem is, after the ws.commit instruction, the records are not in the
tables where they are supposed to be!?! Sucessive attempts at this operation
all result in the same end result, except that the primary key value keeps
incrementing by 1 (I know this because I debug.print strSQL before each
append query executes, and I notice that the new record PK has increased by 1)
.. Yet there are no records in the tables with that PK value. It is almost as
if they are all being roll-ed back. I have stepped through my code and it
never executes the ws.rollback method.

I really do not have any idea why it is behaving badly?!? Can anyone offer
some advice? Thanks.



'use transactions to prevent orphan records.
Set ws = DBEngine(0)
ws.BeginTrans
boolInTrans = True
Set db = ws(0)

'Duplicate the main record
'**************************
With db.OpenRecordset("tblTestConditions", dbOpenDynaset,
dbInconsistent + dbAppendOnly)
.AddNew
!ChemicalID = Me.cmbChemicalName.Value
!SampleTypeID = Me.cmbSampleType.Value
!ReactorTypeID = Me.cmbReactorMOC.Value
!TreatmentID = Me.cmbTreatment.Value
!Temperature = Me.Temperature.Value
!TimeAtTemp = Me.TimeAtTemp.Value
!ChargeGasID = Me.cmbChargeGas.Value
!AutoclaveChargePressure = Me.AutoclaveChargePressure.
Value
!ContFlowAirFlow = Me.ContFlowAirFlow.Value
!OperatingPressure = Me.OperatingPressure.Value
!RefID = Me.cmbChooseReference.Value
!TestConditionNotes = Me.TestConditionNotes.Value
!intZPinternal = Me.intZPinternal.Value
.Update 'update the recordset!

.Bookmark = .LastModified
lngCurrentTestID = !TestID 'store current (new) TestID value
to copy records to

'Duplicate the related records!
'-----------------------------------------------------------------
-

'Duplicate Feed Constituent data
'*******************************
strSQL = "INSERT INTO tblTestConditionsFeedConstituents (TestID,
feed_pH, FeedConstituents) "
strSQL = strSQL & "SELECT " & lngCurrentTestID & " As NewTestID,
tblTestConditionsFeedConstituents.feed_pH, tblTestConditionsFeedConstituents.
FeedConstituents "
strSQL = strSQL & "FROM tblTestConditionsFeedConstituents "
strSQL = strSQL & "WHERE (tblTestConditionsFeedConstituents.
TestID = " & Me.TestID & ");"

Debug.Print strSQL
db.Execute strSQL, dbFailOnError 'run append query


'Duplicate Test Catalyst data
'*******************************
strSQL = "INSERT INTO tblTestCatalyst (TestID, CatalystID) "
strSQL = strSQL & "SELECT " & lngCurrentTestID & " As NewTestID,
tblTestCatalyst.CatalystID "
strSQL = strSQL & "FROM tblTestCatalyst "
strSQL = strSQL & "WHERE (tblTestCatalyst.TestID = " & Me.TestID
& ");"

Debug.Print strSQL
db.Execute strSQL, dbFailOnError 'run append query

...SNIP, there are three more Append Queries that all have the same form as
above.
...
...

ws.CommitTrans 'commit pending transactions.
boolInTrans = False 'set flag that transaction finished
Me.Requery 'update results recordset. puts current record
back at 1.


' Display the duplicate. Assumes that new record is last one in
table.
Dim rsc As DAO.Recordset
Dim strFoo2 As String
Set rsc = Me.RecordsetClone
With rsc
strFoo2 = "TestID = " & lngCurrentTestID
.FindFirst (strFoo2)
If .NoMatch Then
MsgBox "No match; filtered?"
Exit Sub
Else
Me.Bookmark = .Bookmark
End If
End With
rsc.Close
Set rsc = Nothing
End With

End If
End If


Exit_cmdCopyLastValues_Click:
On Error Resume Next
Set db = Nothing

If boolInTrans Then 'Rollback if the transaction is active.
ws.Rollback
MsgBox "There was an error. This record-copy request has been cancelled.
", vbOKOnly, strAppTitle
End If

Set ws = Nothing

Exit Sub

Err_cmdCopyLastValues_Click:
MsgBox Err.Description & " Error number: " & Err.Number
GoTo Exit_cmdCopyLastValues_Click

 

[ionic-fire via AccessMonster.com]

Now it has decided to start working, even though I have not changed any code
at all.....very strange..... any ideas for why it might have been behaving
badly, at least temporarily?

 

[ionic-fire via AccessMonster.com]

Now it has decided to start working, even though I have not changed any code
at all.....very strange..... any ideas for why it might have been behaving
badly, at least temporarily?