check if record was modified using me.dirty

[sunset1215]

hi all,

i'm trying to write a code that checks if the field was modified before popping up a message box.

however, i think that the me.dirty is not working here. is it because my [exprTotal] is a calculated field? if it is so, how can i check if the field is modified without me.dirty?

code attached below. any help or advice is greatly appreciated.

Private Sub exprTotal_GotFocus()
If Me.Dirty Then
If Me.exprTotal = 0 Then
GoTo exit_sub
Else
If Me.exprTotal Mod 3 = 0 Then
MsgBox "Msg", vbOKOnly
cmdAddLoyInc.Enabled = True
Else
End If
End If
End If
exit_sub:
End Sub

 

[The_Doc_Man]

Me.Dirty works fine, but there are some "gotcha" points to consider.

If the underlying data is computed, Access knows that. I'm assuming that you have a query under your data? Or is that a computed control based on a table?

In any case, for an ordinary bound field based in a text box or related types, you can compare [control].Value to [control].OldValue to see if the value changed since the most recent Form_Current event. Catch is, not every control has a .OldValue, and it depends on whether it is bound or not. Further, if the underlying field isn't something you can update, I'm not sure you will have a .OldValue property to check. And a computed field would be high on my list of "something you cannot update."

Where this is going is that if the control has no .OldValue to differ from .Value after a field update, I don't think Me.Dirty gets updated after a change. I would NEVER EVER enable a text box based on a computation. I'd leave that puppy locked up tight.

 

[sunset1215]

yes, my computed field is from a query. it is not stored in any tables. if that's the case, can i use one of the fields from the formula to check?

example:
[exprTotal]: [x] + [y]
so can i use [x] with the me.dirty?

however, i am updating my [x] with another vba code. so i believe i should be using the [control].Value and [control].OldValue you mentioned to check in my case. could you kindly post a sample code to show me how it is used/applied?

 

[sunset1215]

ok, so i've tried changing the code to use the oldvalue property. but it's still not working. i'm not sure where it has gone wrong.

Private Sub lngKci_GotFocus()
If Me.lngKci.Value > Me.lngKci.OldValue Then
If Me.exprTotal = 0 Then
GoTo exit_sub
Else
If Me.exprTotal Mod 3 = 0 Then
MsgBox "Msg.", vbInformation
cmdAddLoyInc.Enabled = True
Else
End If
End If
End If
exit_sub:
End Sub

 

[The_Doc_Man]

What I am saying is that if the control cannot be updated because it is not bound to a table-based field, you might not have an .OldValue to run a comparison. Access is very tricky about what properties it shows you through the VBA interface. Somehow it knows when having a .Value or .OldValue is inappropriate. If you are taking traps with "Object does not have this property" then you have run afoul of this situation.

Me.Dirty makes sense ONLY for those fields that are bound, because the concept of dirty doesn't mean that the field has been changed. What it really means is that the contents of an enabled, unlocked text box no longer match the underlying contents of the record from which the text box was filled. But if the text box cannot be updated because the field cannot be updated, then the form cannot ever get dirty because of the attempt to change the indicated text box.

If the text box is computed, it cannot trigger a "dirty" event. There is no way to tell that the data changed except to write more VBA to capture the value of the field during the Form_Current event (i.e. the time when the form and underlying record still match). Then compare as needed. Put the variable in the class module's declaration area. As long as the form is open, this variable SHOULD persist. (No guarantees in writing, though.)

 

[sunset1215]

i've achieved what i needed in another way, by checking the value right after it has been modified. no issues have popped up for me as of now. thanks for the information. i've learned something new today.

here's the code:

Private Sub Command11_Click()
'when closing [FormName2], msgbox will pop up to ask user if client has completed a trial. If yes,
'add 1 to the number of trials. If no, do nothing and close form.
 
If MsgBox("Has client completed a trial?", vbYesNo) = vbYes Then
Forms![FormName1]!lngKci.Value = Forms![FormName1]!lngKci.Value + 1
MsgBox "Added completed trial to total count.", vbInformation
DoCmd.Close acForm, "[FormName2]", acSaveYes
 
 
'next, check if client has completed 3 trials.
 
Forms![FormName1]!exprTotal.SetFocus
If Forms![FormName1]!exprTotal = 0 Then
GoTo exit_sub
Else
If Forms![FormName1]!exprTotal Mod 3 = 0 Then
MsgBox "Msg.", vbInformation
Forms![FormName1]!cmdAddLoyInc.Enabled = True
Else
End If
End If
 
Else
MsgBox "Trial not completed.", vbInformation
DoCmd.Close acForm, "[FormName2]", acSaveYes
Forms![FormName1]!exprTotal.SetFocus
End If
 
exit_sub:
End Sub

 

[JANR]

Just as an FYI, in your code this line:

DoCmd.Close acForm, "[FormName2]", acSaveYes

saves Design changes and not changes to records, if you change the design of a form during runtime and want to retain those changes when you open the form the next time then it's ok. But it is not common to do that.

I recomend that you change it to:

DoCmd.Close acForm, "[FormName2]", acSaveNo

Another tip is that if you have a close form code on an active form you don't need to hardcode the formname, you can do this instead:

DoCmd.Close acForm, Me.Name, acSaveNo

JR

 

[sunset1215]

thanks for the advice and tips.