Exceuting a Macro and Event Procedure at the same time

[CAFCrew]

Hi Again everyone,

I have yet another problem with my access db.I need to execute a macro and event procedure at the same point in time i.e. "before update" on the form properties. Is there any way of doing this?

The macro is updating the last updated by, and updated date with the user name and date. The event procedure is prompting users to confirm they want to changes they have made to a record otherwise undoing them.

I need to do both of these at this point so the changes are saved (or not)

Any help is really really appreciated. I have searched the net to no avail

Many thanks

CAFCREW

 

[Remou]

It seems to me that the macro should run in the After Update event. that is, when the user has confirmed that the changes are to be accepted, update the record details with a time stamp and name.
It is possible to run a macro using code (DoCmd.Run Macro) but you may wish to consider changing this particular macro to code (Save As).

 

[mp9]

In my view this should all be done in either the BeforeUpdate or AfterUpdate event of the form, depending on your view of whether the updated by and last updated constitute part of the update.

I would personally opt for the form's BeforeUpdate event, as it has a Cancel option that you can use to tidily stop the update if the user chooses not to "save changes".

So ditch the macro and your current code and use this instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
    Select Case MsgBox("Save changes?", vbQuestion + vbYesNoCancel, "Save")
    Case vbYes
        'Write to audit fields (change field names accordingly!!!)
        LastUpdated = Now()
        UpdatedBy = CurrentUser()
        'Then Save, i.e. proceed with update, i.e. do nothing else
    Case vbNo
        'Undo changes since last save
        Me.Undo
        'Then proceed, i.e. do nothing else
    Case vbCancel
        'Cancel update to allow editing to continue
        Cancel = True
    End Select
End Sub

Hope this helps.

http://www22.brinkster.com/accessory/

 

[mp9]

Oops, typo - missed End If

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
    Select Case MsgBox("Save changes?", vbQuestion + vbYesNoCancel, "Save")
    Case vbYes
        'Write to audit fields (change field names accordingly!!!)
        LastUpdated = Now()
        UpdatedBy = CurrentUser()
        'Then Save, i.e. proceed with update, i.e. do nothing else
    Case vbNo
        'Undo changes since last save
        Me.Undo
        'Then proceed, i.e. do nothing else
    Case vbCancel
        'Cancel update to allow editing to continue
        Cancel = True
    End Select
End If
End Sub

http://www22.brinkster.com/accessory/

 

[CAFCrew]

sounds good..i will give it a go. I thought there must be a way to do it all in VB, but my knowledge of it isn't very good.

Thanks for this, its MUCH appreciated