Trigger Access AND SQL events at the same time?
Posted on 2004-09-23
Here's an interesting dilemma, for me anyway.
I've got an ADP (Access 2000 front-end, SQL Server 2000 back-end). The database deals with Auctions and Lots (items sold at auction). Due to a one-to-many relationship between them (one LOT can have more than one AUCTION), I have created a different Access form for each -- the LOTform (based on the underlying SQL table LOT), and the AUCTIONform (based on the underlying SQL table AUCTION).
When a winning bid (WinBid) is entered into a control on the AuctionForm, and the auction is paid by the winner (noted by another control, PaidStatus, becoming = TRUE), then a profit $ is calc'ed for the Auction nad displayed in a control called AuctionGross; this control is bound to the SQL table AUCTION.
WHEN THAT HAPPENS, I also need for a couple of values to get calc'ed and stored over in the SQL table LOT, whether or not the LOT form is open in Access. (Yes, I need them to be stored, in case the underlying formulae change in the future, I need for the original values to remain unchanged in the database records rather than re-caclc'ed every time those records are displayed in the Access forms.)
I already have AfterUpdate events in the appropriate places on the Auction Form so that when WinBid and PaidStatus are updated, the AuctionGross gets calculated in the front-end (and stored in a control that's bound to the back-end). But can I also trigger calculations in the back-end from an Access AfterUpdate event?
Hope this makes sense. If I'm barking up a wrong or non-existent tree, somebody let me know. Thanks!