Following setting: form with lots of controls, mainly checkboxes. Complicated ruleset which boxes might be checked at a time (kind of a frame on adrenaline ). The rules are implemented via triggers on a a sql server backend. I'm searching for a way to requery the form each time a box is (un-)checked/another value is changed without using the events of the controls itself.
I've tried form_dirty, form_afterupdate, afterinsert, but nothing works like I want it to. So is there a way to tell access to requery the form each time the value of a control has changed (well, to be exact: to save the recordset and then requery, thus reflecting the changes made by the trigger)?
This one drives me nuts, so any help would be greatly appreciated
I am afraid that what you are asking is not possible - the changes to the data in a form are cached and are only committed to the backend as a single transaction. The only way to force committal on the change of each control is to trap the controls' events. So - you will need an event handler for each control (say before update). The good news is you can create a generic function you call from each controls before update event so any complex code can be written only once - use the Form.ActiveControl property to identfy the control if it is significant. To commit the change and read the changes to the data (in a bound form):
Me.Dirty = False
One minor reticence - I would check with the DBA that they are happy with this - depending on the trigger this could really put some load on the server and will certainly increase network traffic. If the trigger is quite static I would consider replicating its affects at the front end as a presentation function only.
Thanks for the feedback. I think I've now tested all form level events that are even only remotely related to data manipulation and none seem to work
So I'll go for the control-based approach. I won't replicate the changes in the frontend for display, though. The rules are quite complex and data may be inserted/changed by other means than the form, so the triggers are a must. Additionally, the rules are subject to change and updating the application every time is no practical solution. There is a dedicated Server for the backend and I checked with the admins, so no problem there (wrt these records, read/write ratio is estimated to be around 95/5, so I'm not expecting horrendous traffic, anyway).