Inserting current DateTime into Audit table

Question

I am in the process of implementing an audit log to record a brief description of changes to the database. My audits table consists of an autonumber PK, empID(number), Description(memo), and auditDate(date/time). My empID and description are being properly inserted without throwing errors, but my date/time is not being inserted. I am thinking this may not be as simple as misplaced quotation marks. My VBA code is as follows:

in the afterInsert event:

Dim strQuery As String
'Dim js As Integer
Dim currDateTime As Date
currDateTime = Now()


strQuery = "INSERT INTO Audits ([emp Number], Description, dateofAudit) VALUES (" & Me.empID & ", '" & "insertion" & "'," & currDateTime & " )"


CurrentDb.Execute (strQuery)

Like I said, I can get the first three values in fine, but when I attempt to insert the date time, I run into problems. Any input is appreciated. Hopefully this is not as simple as misplaced quotation marks, as I tried about 4 variations of quotation mark placement before submitting this question:)

Answer 1

Try it this way.

strQuery = "INSERT INTO Audits ([emp Number], [Description], dateofAudit)" & _
    vbCrLf & "VALUES (" & Me.empID & ", 'insertion', Now())"

Also give yourself an opportunity to examine the finished text string.

Debug.Print strQuery 

With that approach, you wouldn't need your currDateTime variable. The Date/Time value would be determined when the db engine evaluates the Now() function ... the time at which the INSERT statement is executed.

If you want the time as per your original approach, format currDateTime and add # delimiters.

strQuery = "INSERT INTO Audits ([emp Number], [Description], dateofAudit)" & _
    vbCrLf & "VALUES (" & Me.empID & ", 'insertion', " & _
    Format(currDateTime, "\#yyyy-mm-dd hh:nn:ss\#") & ")"