I have to run the update query twice for it to work!

[DataMiner]

When I run the code listed below, I get no error messages and everything seems to work correctly, but the records I expect to be updated by query CurWIP_Update3 do NOT get updated.

If I turn CurWIP_Update3 into a select query, it looks as if there will be 90-some records that get updated. But in reality, only 3 records in MasterSchedule get updated.

If I do nothing more than just run CurWIP_Update3 a second time, outside of the code, the records in MasterSchedule get updated OK.

I've tried adding doevents prior to CurWIP_Update3, no help.

Any ideas???



Here is my code:

Sub UpdateCurDueDate()
Dim DB As Database
Set DB = CurrentDb

With DB

'refresh table PendingJobsUpdate_2
.Execute "delete * from pendingjobs_update2", dbFailOnError
.Execute "Pendingjobs_update1", dbFailOnError

'update records in MasterSchedule table
.Execute "pendingjobs_update3", dbFailOnError

'refresh table CurWip_Update2
.Execute "delete * from curwip_update2", dbFailOnError
.Execute "curwip_update1", dbFailOnError

'update records in MasterSchedule table
.Execute "curwip_update3", dbFailOnError



End With
End Sub

Here's the way the data flows:
MasterSchedule (table) >>>> PendingJobs_Update1(append)

PendingJobs_Update1(append)>>>>PendingJobs_Update2 (table)

PendingJobs_Update2(table)>>>>PendingJobs_Update3(update)

PendingJobs_Update3(update)>>>>MasterSchedule(table)

PendingJobs_Update2(table)>>>>CurWIP_Update1(append)

CurWIP_Update1(append)>>>CurWIP_Update2(table)

CurWIP_Update2(table) + Fcst(table) >>>>CurWIP_Update3(update)

CurWIP_Update3(update) >>>>MasterSchedule(table)

 

[RuralGuy]

I'm not sure what is going on but you may want to check the RecordsAffected property as a diagnostic tool to pin down the problem.

 

[DataMiner]

Thanks, already did that. Running the query from within the code, it says there are 183 records affected, but the updates don't show up in the table. Then if I run the query again, outside of the code, it says 183 records affected and the updates DO show up in the table.

 

[RuralGuy]

That exhausted *my* bag of tricks. Good luck.