Insert update date in a form

[Jeff H]

In Access 365 I have some code in a form to insert the date when a record is changed, but it works sometimes and not other times. When it does not work, this is the error I get: “Update or CancelUpdate without AddNew or Edit.” How can I make it work all the time?

I have two places where I use the code, BeforeUpdate and a command button.

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty And Not blnNoUpdate Then
    If MsgBox("The form was changed. Enter the date " & _
        "in the Updated field?", vbYesNo) = vbYes Then _
        Me.Updated = Now()
End If
End Sub

Code: Select all

Private Sub cmdUpdate_Click()
blnNoUpdate = True
Me.Updated.SetFocus
Me.Updated = Now()
Me.Dirty = False
blnNoUpdate = False
End Sub

[HansV]

Could you attach a zipped sample database?

[Jeff H]

Sure. It's got a lot of pictures so it's pretty big and zipping doesn't reduce it much. I'm uploading the zipped folder to my OneDrive now. I'll email you the link once it's uploaded.

[HansV]

I'd remove the line

Code: Select all

Me.Updated.SetFocus

from both procedures. It isn't needed and appears to interfere with moving to a different record. See if that solves your problem.

If not: do you see a pattern in when the error occurs?

[HansV]

PS the button on the dashboard is labeled 'Editing Fom'.

[Jeff H]

I'm not sure it does resolve it. The error seems to occur after some setting gets established by the code. That is, once I get the error I have to close the db and reopen to fix it, which also means I can't save the record where it occurred.

I had added the SetFocus because at one point I got an additional error that I couldn't change a control unless it had the focus. I think I had gotten that error because of something I did when adding a picture. I agree, that command was annoyingly preventing going to the next record and also requiring another msgbox query about inserting the date. I assumed I was still in the photos field and thought I had to select the Updated field. I've taken that out.

However, after doing that I got the error again. I'm going to make a point of thinking about what I have just done and where the focus is before moving triggering the BeforeUpdate event. If I can find the pattern and still can't resolve it, I'll post again.

In any case, as always, thanks for your time Hans.

- Jeff