Do this as a transaction. See this link for setting it up.
No need for code. Doing it as a transaction stops others from seeing Table A's PAID field as TRUE from the first update until the second update resolves the issue for those cases that are not fully paid.Code:DBEngine(0).BeginTrans UPDATE TableA SET PAID = TRUE ; UPDATE TableA INNER JOIN TableB ON TableA.SettlementID = TableB.SettlementID SET TableA.PAID = TableA.PAID AND TableB.PAID ; DBEngine(0).CommitTrans dbForceOSFlush
Further, since this will be VERY SIMPLE SQL it should be incredibly fast when compared against an explicit recordset loop.
If you weren't sure, that second UPDATE will make TableA.PAID = FALSE the first time it hits a case of TableB.PAID = FALSE.
Thanks for this suggestion. I am not having any success in getting it to work right. Please see posts (10 and 17). I am hoping to get yours to work as these two tables will be the largest two tables in operation. Your suggesting may be the most efficient ?? I'm hoping anyway. Any help getting it working right would be appreciated. Thanks much.