See if a value in a control has changed

[Martyh]

I need to see if the value in a control has changed from the time that the form opened until the time that the form is closed.

At the beginning of accessing the form ("frmTrxWInvoicesExp"),
I store the sum of the invoices into a variable in a module of Global Variables

The code is:
********************************************************

Private Sub Form_Load()
modGlobalVariables.EarlySumInvExp = Nz(Me![2tblInvoices Subform]!SumOfInvExp, 0)
'MsgBox "The value is " & Me![2tblInvoices Subform]!SumOfInvExp
End Sub

********************************************************
Then when I go to leave the form I access the some additional code.

*********************************
Now for the question:
When I go thru the loop the first time round (ie thru an "open the form" and "close the form") things seem to work as they should. However, the problem that I encounter is that the 2nd time thru (as well as subsequent times) the form no longer gets the value of Me![2tblInvoices Subform]!SumOfInvExp.

Have I got the proper syntax for this sum? or is there something else wrong with my approach to the problem?

 

[Dennisk]

is the form being hidden and not closed as that may be the cause of your problem.

 

[Martyh]

No, I actually have the following code in the close form procedure:

Private Sub cmdClose_Click()
....

DoCmd.Close
...
....

End Sub

 

[KeithG]

I would create an invisible textbox on the form and store the original value in it when the form opens. Then On the close event I would compare the two values in the text boxes to see if they differ.

 

[The_Doc_Man]

Forms have a .Dirty property, set to TRUE if a control has been changed since the last OnCurrent event. It is automagically set behind the scenes by ACCESS (not by your code) after any control update.

You can test for Me.Dirty, which gives you the clue that you need to look further.

That looking further is that you can find the Me.Controls collection which gives you all controls. If the control is a label, rectangle, or line, doesn't have a value. If it is an option button it doesn't have a value. (But the option group that contains that button DOES have a value.)

For those controls that have an associated .Value, they will also have another property called .OldValue, which you can compare to .Value to check for changes. No need for invisible text boxes. You already have one.

REMEMBER: After a Form Update or Current event, the recordset below the form has been changed and the contents of the form now match the contents of the recordset. BY DEFINITION, this is "clean" i.e. Me.Dirty = FALSE. It does not mean, has this form been changed EVER. It means only that the form is or is not changed since the last time the form and the recordsource touched each other via Update, Undo, or navigating the recordset.

 

[Martyh]

Adding...

You can test for Me.Dirty, which gives you the clue that you need to look further.

For those controls that have an associated .Value, they will also have another property called .OldValue, which you can compare to .Value to check for changes. No need for invisible text boxes. You already have one.

Yes this seems to be the way to go, but I am nervous about the fact that this subform (a form within a form) ... How do I call these? Also the control that I am looking at is a calculated field ... the sum of all the invoices. Are there any precautions ??

In addition, is the value in the property called .oldvalue the value when you first called the form or is it the value that just the previous value (the last value before any change that might have taken place during the form open event and then revised back (and not have the original .oldvalue that I'm looking for) ? I specifically need it to be the value when I called the form.)

Does that make any sense??

 

[The_Doc_Man]

You have several explicit or implied questions.

First, there are ways to access controls in subforms. If you do this from the subform's code then the controls on that form are "local" to the code. If not, will need to qualify the sub-form control. Look in Access help if you aren't sure about how to do this. It is something similar to Formname.controls("subformname").controls("targetcontrolname") - but there are other ways to do this, too.

Second, the .Oldvalue comes from the last time that the form and recordsource touched each other. This is normally after a form-triggered update but could also be handled if you performed the UNDO function (via iconic toolbar or a command button on the form). If the form does a requery you will get that effect. If you navigate to another record, you also get that effect.

But .Oldvalue doesn't track every change to a field. I am making an inference here regarding what you asked. There is NO mechanism (unless you write one) to track changes to a control without an intervening save or undo.

Third question - the value in a text box (control) can be bound or unbound. If the underlying data source for the box is a computed field in a query, it is still bound to a record-based source. Just not a table-based source.

On the other hand, an unbound text box that is computed at the Form_Current event time might not have an .Oldvalue update. I honestly don't know what is the .OldValue for an unbound but editable control. I've not played with that feature very much.