Incorrect values written during an update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an after udpate event that is supposed to write the updated value to
an audit log. The code does update the correct record, but it updates it to
a time field. For example, the updated record should read 8/1/2007, instead
it reads 12/30/1899. If I select that field, the value shows as 12:05:44 AM.

Here is the code:
Private Sub dtmCCSContractSigned_AfterUpdate()

Dim NewVal As Date

NewVal = Me.dtmCCSContractSigned

strSQL = "UPDATE tAuditLogDt SET dtmNewVal= " & NewVal & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLogDt WHERE txtNTName='" &
fOSUserName() & "')"

DoCmd.RunSQL strSQL
End Sub

Any thoughts?

PJ
 
Date/time values need to be delimited with # characters (and need to be in a
format Access will recognize, since it doesn't respect regional settings).

Try:

Private Sub dtmCCSContractSigned_AfterUpdate()

Dim NewVal As Date

NewVal = Me.dtmCCSContractSigned

strSQL = "UPDATE tAuditLogDt SET dtmNewVal= " & _
Format(NewVal, "\#yyyy\-mm\-dd hh\:nn\:ss\#) & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLogDt WHERE txtNTName='" &
fOSUserName() & "')"

DoCmd.RunSQL strSQL
End Sub
 
Back
Top