Record Stamping Woes

  • Thread starter Thread starter Robert Neville
  • Start date Start date
R

Robert Neville

My record stamping code keeps returning an error when updating a value
in place. I would like to record stamp the creation date and an
updated date. The creation occurs once and the updated value change
with each edit. This objective seems straight forward, yet I am still
having trouble after searching numerous record stamping posts. The
code executes without incident when records do not have a value in the
updated field. Please let me know where my code went awry.

Complete Error below
Update or CancelUpdate without AddNew or Edit. [3020]

FYI
This code was previously more complex adding the current user's name
to the record stamp. This functionality may come back, yet I excluded
the comment code for post simplicity. Plus, each form and underlying
table has different names for the updating and creation fields thus
the reason to pass the control arguments to the procedure.

*******Code Starts**********

Public Sub FrmRecStamp(ctlUpdated As Control, _
ctlCreated As Control)
' Purpose: Timestamp current form record, called from
Form_BeforeUpdate
' Arguments: ctlUpdated: = as updated control on the form
' Arguments: ctlCreated:= as created control on the form
' Example: Call FrmRecStamp(Me!txtCompUpdated, Me!txtCompCreated)

Const cstrProc As String = "FrmRecStamp"

On Error GoTo FrmRecStamp_Err

If Len(ctlUpdated.Value) > 0 Then
ctlUpdated = Now()
Else
ctlUpdated = Now()
End If

If IsNull(ctlCreated) Then
ctlCreated = Now
End If

FrmRecStamp_Exit:
Exit Sub

FrmRecStamp_Err:
Call ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number,
Err.Description, True)
Resume FrmRecStamp_Exit

End Sub

*******Code End**********
 
What event on the form are you using to call this code?

You should could try the before update event. However, since you ONLY want
this when the record is being added, then I suppose the on-insert event
would even be better, as then it would only get called during creating, and
you can eliminate the code to check if the fields are empty.
 
Robert Neville said:
My record stamping code keeps returning an error when updating a value
in place. I would like to record stamp the creation date and an
updated date. The creation occurs once and the updated value change
with each edit. This objective seems straight forward, yet I am still
having trouble after searching numerous record stamping posts. The
code executes without incident when records do not have a value in the
updated field. Please let me know where my code went awry.

Complete Error below
Update or CancelUpdate without AddNew or Edit. [3020]

FYI
This code was previously more complex adding the current user's name
to the record stamp. This functionality may come back, yet I excluded
the comment code for post simplicity. Plus, each form and underlying
table has different names for the updating and creation fields thus
the reason to pass the control arguments to the procedure.

*******Code Starts**********

Public Sub FrmRecStamp(ctlUpdated As Control, _
ctlCreated As Control)
' Purpose: Timestamp current form record, called from
Form_BeforeUpdate
' Arguments: ctlUpdated: = as updated control on the form
' Arguments: ctlCreated:= as created control on the form
' Example: Call FrmRecStamp(Me!txtCompUpdated, Me!txtCompCreated)

Const cstrProc As String = "FrmRecStamp"

On Error GoTo FrmRecStamp_Err

If Len(ctlUpdated.Value) > 0 Then
ctlUpdated = Now()
Else
ctlUpdated = Now()
End If

If IsNull(ctlCreated) Then
ctlCreated = Now
End If

FrmRecStamp_Exit:
Exit Sub

FrmRecStamp_Err:
Call ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number,
Err.Description, True)
Resume FrmRecStamp_Exit

End Sub
Click to expand...

I don't see how this code can cause that error, but it does
look like a lot of code to do such a simple job.

First, there is no need to use code to set the created date,
just set the txtCompCreated text box's default property to
=Now() and it is taken care of automatically.

Second, you set the updated text box's value to Now()
whether it is null or not so there is no need for the If -
Else.

If you made those changes the entire procedure would be
reduced to one line of code:
ctlUpdated = Now()

So, you might want to consider forgetting about the
procedure and place the one line:
Me!txtCompUpdated = Now()
in the form's BeforeUpdate event.

Maybe those modifications will also shake things up enough
so that whatever is causing the error will be easier to
spot.
 
Back
Top