Efficient way to commit changes instantly?

Question

Without creating an AfterUpdate event handler for each control on a form, is there an easier way to force the record to commit after any control is updated? Or do I have to do it individually for every control?

Answer 1

Well, I just thought of adding the events at runtime, but I'm having trouble with the syntax. Can someone teach me 2 things?

How do I call a function in the form module while adding the event in VBA?
and
How do I write an expression for the event in VBA?
As I iterate over the controls, I try this for calling a method in the form module:
Me.Controls(i).AfterUpdate = "=MyCommitMethod"
and this for trying to write an expression:
Me.Controls(i).AfterUpdate = "=[me].[Dirty]=False"

Neither work.

Answer 2

Without creating an AfterUpdate event handler for each control on a form, is there an easier way to force the record to commit after any control is updated? Or do I have to do it individually for every control?

Hi HTHP,

I did some experiments long ago in this direction, but unfortunately I did not succeed to make modifications in VBA in runtime. But you can describe (or program) the logic behind many things to make applications realy dynamical.

In the (few) forms that I use, each control has its own (regular) AfterUpdate event. In this AfterUpdate event a Sub in the form's module is called that translates the specific control's AfterUpdate event to a general description of AfterUpdate event in terms of Form and Control, calling a procedure that is contained in a library reference database. This procedure then is used by any application. Offline you can add the AfterUpdate events to the respective controls.

This approach is used for any important event of the controls. In relation to the AfterUpdate event specifically, you also have to consider the Exit event, as AfterUpdate event are not fired when code changes a value of a control, but you can catch them on Exit events. One of my constraints is that controls behave always the same whether their value changed by hand or by code.

The definition of all these events is in my environment absolutely no problem, as I have defined them once in generalized forms, and I use the same forms all over the application, and in any other application.

Imb.

Answer 3

"Hi. The form's AfterUpdate event will only fire after you move away from the current edited record - by saving it, moving focus to another record or form, or by closing the form. 

As you need to update your computed field every time one of the contributing textboxes has been updated you will indeed need to use the AfterUpdate event of each textbox to call your requery method, as NeoPa has advised."

Taken from: https://bytes.com/topic/access/answers/892007-form-event-fires-after-any-field-updated

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

If you work the way Access was designed for you to work, then no code is needed. You update an existing record (bound to an underlying query), and when you move to another record the changes are automatically saved. Again: zero code needed.

If you think you can create a new record, say a Contact record, and save the record after entering FirstName, and again after LastName, etc., then we need to have a talk with you about proper database design.

---

-Tom. Microsoft Access MVP

Answer 5

All I want to do is commit changes to the Azure SQL server after a control's data is updated before the user moves off the record. I usually do this by placing Me.Dirty = False in the AfterUpdate events of Textbox/ComboBox/CheckBox controls. Pretty much all of my controls already do this. I'm just looking for an easier way to register the event handlers with that line of code than manually hardcoding a Textbox_AfterUpdate() method for every single control. Some forms and datasheets have like 20-40 data controls.

Was I not explaining myself well, or is there something wrong with this approach? I see it all day in web browsers with AJAX and XMLHttpRequests. I do not expect to use this in all cases, such as creating new records. I began doing it because there are 100+ people using this particular App at the company all a the same time. With that many users, people constantly edit data and leave their screens without saving or exiting the form. So I had to force the commits.

I've had great results doing this. But I'm not saying there may not be problems I'm unaware of, I'm just saying from my experience so far its been working great for years. I am open to learning why and how this could be bad design if that is the case, because I do care, I just don't understand.

The records contained in these particular Datasheet's Recordset already exist. I do not allow users to Add/Delete records either when I do this.

At the end of the day, I was just looking for a way to code faster. If I can iterate over controls when Form opens, check the type of control and set the AfterUpdate event to commit the changes to the form if I desire that control to do so. As I said I'm already doing this, just manually.

Answer 6

Without creating an AfterUpdate event handler for each control on a form, is there an easier way to force the record to commit after any control is updated? Or do I have to do it individually for every control?

You can create a function named (for example) "SaveRecord" in the form's module, with the code to save the record; e.g.:

    Function SaveRecord()

        If Me.Dirty Then Me.Dirty = False

    End Function

Then, in Design view, you can select all of the editable controls on the form, open their joint property sheet (which will include all the properties those controls have in common), and enter this for the AfterUpdate property:

    =SaveRecord()

That's not quite as automated as you want, but it's a lot simpler than creating an event procedure for every control.

Or you could create a public function in a standard module that receives a reference to the form in question and saves that form's record:

    Function SaveRecord(frm As Access.Form)

        With frm
            If .Dirty Then .Dirty = False
        End With

    End Function

You could then use this function in a number of forms, setting each relevant control's AfterUpdate event property to this:

    =SaveRecord([Form])

You could even avoid having to set all the event properties yourself by calling one function in the form's Open event to set all the event properties for the editable controls on that form.  Because you aren't changing VBA code at run time, this will work.  Let me know if you need me to write that function for you.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 7

Why not use a timer and close forms or even th db if it remains idle for a period of time?

http://rogersaccesslibrary.com/download3.asp?SampleName=LogUsersOffNonUse.mdb

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 8

Thank you. I created a function in a global module as you suggested. I looped over the Form's Controls collection and, for the controls I needed, I set .AfterUpdate = "=SaveRecord([Form])" as you suggested. It works great.