After Update Event, Add a Date Creates Error

[d9pierce1]

Members.zip

HI all, I am testing certain things that I have had issues with before and do not understand why this hangs up my form and creates an error on save?

I have it commented out now but in the after update event on the form, I put the following code.

Me.TxtModified=Now()

When I go to modify record I get an error that says encountered error, cant save record at this time Yes/no.....

I would love to understand why this does this? What am I missing here?
Thanks
Dave

[ssanfu]

Not sure what to tell you.

I added a member, added a couple lines of notes and clicked the save button. The record didn't save (as indicated by the pencil in the record selector)
I changed the code for the save button. I used the Dirty property. But that wouldn't save the record. Correction.... The record was saved, but the pencil didn't/ wouldn't go away.
My normal Access is 2010. So I switched to A365 (2016). Same thing - the pencil didn't/ wouldn't go away. I created a dB in 2010 and moved it to A2016 (different confuser). This time, in both 2010 and 2016, when I clicked the save button, the record was updated and the pencil went away.

On your dB:
I tried C&R - no joy.
Tried de-compiling - no joy.

Tried importing the objects to a new A2010 dB. Still the same behavior. Data saves, but pencil indicator won't go away.


I'm beat for tonight ..... maybe I'll think of something overnight.

Hopefully someone else will have an answer.......

[Micron]

I would avoid mixing embedded macros with code. You have events on controls (before update/ after update) events on form (current, after update) but are calling embedded macros from buttons. You can end up with code that fires first, then a macro, then code - or some other wonky mix.

But if you're editing a member name, your check for such an existing record with that name likely won't be found because the current value in the control is being looked for, and it doesn't exist. Thus you're cancelling the update for the whole form, not just the control

'if exististing, then get that record
    If SNkey > 0 Then
        TGTkey = SNkey
        Me.Undo

This might be leaving your record in an 'edit' state (at least via Save button click) because of the code Undo, yet you have a macro step to do just that. What is firing first will depend on what's going on, and there is more than one way to do the same thing in terms of going to a new record or saving one (buttons vs record navigation controls). So in one case, clicking a button means a control has just lost the focus. If it has an update event, it's firing before the macro, and quite possibly after the form update event. A real mish-mash of events, some of which are not obvious. If you really wanted to trouble shoot and keep the current approach, maybe you can impose single step on the macros as well as put breaks on all the events and single step through everything. Then you might be able to sort it out. Since I don't use macros, I'm not liking the whole idea of mixing the two.

I'd suggest doing code or macros, but not both, and researching events for controls and events for forms. It's quite involved and one needs to understand (note that I didn't say remember) their relationship to one another.

[d9pierce1]

Members - Update.zip

Hi all and thanks for all the input.
I have taken out all the macros per your comments, added error code, and have tried to resolve this many different ways.

So everything works well excpet i still get an error when i edit a record and hit the save button if i have the forms afterupdate event on to set the TxtModified = Now()

My goal here is to have the Now() added to the TxtModified filed in form footer anytime that record has changed in any form field. It does change the record but creates a
runtime 2501 error? I have used this before and never had any issues with it till lately.

Would love to see if someone has any ideas on this????? I uploaded the current db with updates to code, no macros!
Thanks
Dave

[davegri]

The acCmdSaveRecord is triggering the After_Update event which is not logical, sort of a circular suicide effect. Try the below mods. The NewRecord is to avoid having an update date for new records (which have not yet been updated).

Private Sub CmdSave_Click()
    On Error GoTo Err_Handler
    If Not Me.NewRecord Then
        TxtModified = Now()
    End If
    DoCmd.RunCommand acCmdSaveRecord
Exit_Handler:
    Exit Sub
Err_Handler:
    Select Case Err
        Case Else
            MsgBox "Error " & Err & ": " & Error$, vbExclamation, "CmdSave_Click()"
            Resume Exit_Handler
    End Select
End Sub

and

Private Sub Form_AfterUpdate()
    ' TxtModified = Now()
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not Me.NewRecord Then
        TxtModified = Now
    End If
End Sub

[d9pierce1]

Thank you
will give this a shot!
Dave