How to best handle updating field in another table when check box is true
Posted on 2014-01-08
I have a database where each submission will have multiple associated samples. There is a check box to mark the submission closed when work is completed on it. I also need to update the "completed" field for each sample associated with the submission. I would like to do this automatically when the check box is "true" and undo it if the user makes an error and later unchecks the box. Submissions and samples are two different tables related by Submission ID. I have an update query to update in bulk all the samples that have been missed so far but I'm not sure that is the way to handle the day to day updates. I'm also not sure how to run the update query in a silent mode that the user doesn't see. Maybe VBA, macro or something else would be better? Any suggestions on the best way to handle it?