Event conflicts produce errors in saving records

[Pete Merenda]

I finally figured the cause of hampering problem -- which is most of the
battle, but I can't seem to fix it. The initial problem was a "can't save
record" error despite that data was loading to forms and writing to tables
properly.

I removed a macro that I had on the form's current event, and the issue went
away, but of course that function served a purpose. I have five relevant
fields: three date fields (Date1, Date2, Date3), a 'Status' field, and a
'LastUpdate' field.

My goal:
1. If Date1 is not null and Date 2 is null and Date3 is null, set the value
of Status to 1
2. If the Status field changes, set LastUpdate to Now ()

I can't figure out which form (and/or field) events to attach the actions to
accomplish the tasks. For that matter, I may need help with the code.

If it's helpful, this is what I've done (which, again, produces an "Access
can't save record error")

For 1, I wrote a macro ([Date1] Is Null And [Date2] Not Null]..., Status =
1) and attached it to the form's OnCurrent, but the field value was not
setting, so I inserted code on the form's Before Update, as follows:

With Me!Status
If Nz(.Value) <> Nz(.OldValue) Then
Me!LastUpdate = Now
End If
End With

For 2, I attached the following to Before Update on the 'Status' field
Me.[LastUpdate] = Now

Your help is greatly appreciated.

 

[John Smith]

Firstly, if this is the only purpose of the Status field then you do not need
it, it is derived data and breaks the normalisation of your table.

The Current event fires every time you access the record, regardless of
whether anything has been changed, so your code is altering records every
time. The place to do this is the *Form* before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
if not IsNull(Date1) and IsNull(Date2) and IsNull(Date3) then
' Condition is met
if Nz(Date1) <> Nz(Date1.OldValue) or Nz(Date2) <> Nz(Date2.OldValue) _
or Nz(Date3) <> Nz(Date3.OldValue) then
' At least one of the fields has changed
LastUpdate = Now
end if
end if
End Sub

If Status also has some other purpose then you can update it when you update
LastUpdate.

HTH
John