Run a Event Procedure and Macro in the Before Update Event

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

Can you run both a Event Procedure and a saved Macro in Before Update Event?
I am trying to run my Event Procedure along with a new macro and so far I
can only run one or the other.


Thank you

Frank
 
I have created a DateModified and TimeModified field in the SQL database and
created the Datemodified and TimeModified boxes in the Access Form. I cannot
get this Microsoft example to work with my current Event Procedure. I can
get the Macro to work but then I cannot run the current Event Procedure.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date and time.
DateModified = Now()
TimeModified = Time()

BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub
 
Frank said:
I have created a DateModified and TimeModified field in the SQL database
and
created the Datemodified and TimeModified boxes in the Access Form. I
cannot
get this Microsoft example to work with my current Event Procedure. I can
get the Macro to work but then I cannot run the current Event Procedure.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date and time.
DateModified = Now()
TimeModified = Time()

BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub
Click to expand...


I can't see any reason why that would not work, though you should be aware
the Now() function will store a date/time value composed of both the date
and the time in your DateModified field. You may want to use the Date()
function there, or just have one "LastModified" field that receives Now() --
both date and time. I don't see any point, really, in storing the date in
one field and the time in another.

You say your code above doesn't work. In what way doesn't it work? Is
there an error message? Is the code being called at all? To have it be
called, the form's BeforeUpdate event property must be set to "[Event
Procedure]".
 
Frank said:
Can you run both a Event Procedure and a saved Macro in Before Update
Event?
I am trying to run my Event Procedure along with a new macro and so far I
can only run one or the other.
Click to expand...


The only way you could do this would be to either have the event procedure
run the macro or have the macro run a public procedure that contains the VBA
code from the event procedure. I can't think why you would want to do this,
though. I'd recommend sticking with one or the other: either the event
procedure or the macro.
 
I have created a DateModified and TimeModified field in the SQL database and
created the Datemodified and TimeModified boxes in the Access Form. I cannot
get this Microsoft example to work with my current Event Procedure. I can
get the Macro to work but then I cannot run the current Event Procedure.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date and time.
DateModified = Now()
TimeModified = Time()

BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub
Click to expand...

Change the two lines to

Me!DateModified = Date
Me!TimeModified = Time

where DateModified and TimeModified are the names of controls - textboxes - on
your form, bound to the SQL fields.

Note that Now does ***NOT*** return the current date. It returns the current
date *and time*, accurate to the second. If your DateModified field is
formatted to show only the date, you may get unexpected results when
searching. The time portion will still be there, even if it's not visible!
Also, Time() returns the time on December 30, 1899. You should really consider
just using a WhenModified date/time field (in Access JET or in SQL, makes no
difference) and setting it to Now, to get the date and time all in one field.

John W. Vinson [MVP]
 
John,

I added the below to my current Event Procedure and it works well, thank you.

' Set bound controls to system date and time.
Me!DateModified = Date
Me!TimeModified = Time
 
Back
Top