1

What I need to do is keep track of any field that changes by the user, and the form I'm using is a Continuous Form. The data is Customer data. So, if the user changes a customer's Last Name, and then changes the same customer's Zip Code, I need to store that info somewhere so I can write it to a report.

The storing part is no problem, nor is the report. What I'm most concerned with is having to write 30 (yeah, there's 30 fields...) AfterUpdate events. I feel like there's probably an easier way that I'm overlooking.

I know the OnDirty event of a form only fires once. Once a form is Dirty, it's Dirty. Is there some form-level event that will fire every time a control is updated?

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Is jQuery an option? – Kobi Tate Aug 19 '16 at 19:03
  • Use `if me.Dirty then me.dirty = false` ? – Ryan Wildry Aug 19 '16 at 19:06
  • @KobiTate - Unfortunately, this is an Access app. We're gonna port it eventually, but for now I just need to band-aid it. – Johnny Bones Aug 19 '16 at 19:11
  • Oh lol I just answered some HTML etc. related questions so I didn't realize what the question was for. My bad... Carry on. – Kobi Tate Aug 19 '16 at 19:12
  • Are the fields in the form bound to a table? If so, you can use the Form's `BeforeUpdate` and `BeforeInsert` events to trap those changes to the fields on your form. Keep in mind these events first just before the item saves. If you want the event to fire as soon as the field changes (prior to save), I think you'll need to do it at the control level like you were thinking. – Ryan Wildry Aug 19 '16 at 19:55
  • 1
    It can be done with an event listener class. Requires a bit more initial setup, but might spark an idea: [link](http://stackoverflow.com/questions/23522230/creating-a-class-to-handle-access-form-control-events) – Fink Aug 19 '16 at 20:58

1 Answers1

1

There is no event that fires when “any” control is changed. In fact I not aware of any development platform that fires an event for any control unless you write code for each control – and that includes those event listener class’s that AGAIN require code for each control. (they tend to be a really brain dead solution since you wind up writing code for each control anyway!)

However, there is record level events for a given form. Thus if ANY control is changed, then the forms before update event will fire. And in that event, you can STILL at that point in time examine the old value (previous value) and the current value before the record is committed to the table.

So in the before update event you can go like this:

  Private Sub Form_BeforeUpdate(Cancel As Integer)

     ' this event does not fire UNLESS the reocrd be change,
     ' send to debug window each control that been changed.

     Dim myControl     As Control

     For Each myControl In Me.Controls

        Select Case myControl.ControlType

           Case acTextBox, acListBox, acComboBox

              ' text box control, check old vs new value
              ' but ONLY check for controls that are bound
              ' to data

              If myControl.ControlSource <> "" Then

                 If myControl.OldValue <> myControl.Value Then

                    Debug.Print "control " & myControl.Name & " was Changeed from" & "" & _
                                Nz(myControl.OldValue, "") & " ->" & Nz(myControl.Value, "")
                 End If
              End If

      End Select

     Next myControl

  End Sub

The above will thus send to the debug window a "list" of controls that were changed by the user. I used a select case above since you may want special code for say a listbox, combo box etc.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51