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:)

Hazlip

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\#") & ")"

Sidonius

Works like a charm, thank you-- I will accept your answer. On a side note, I've noticed that this insertion is not commited until I do something else on the form where this code resides. Is there a simple command I can put after the query is executed to go ahead and commit the data? – Hazlip

Which after insert event? I assumed you meant the form's after insert event. In that case the row should have already been saved. If instead you're using after insert of some form control(s), switch to the form after insert event. If it sounds like I misunderstood your comment, please try again. :-) – Sidonius

It seems to be working now, but before, I was having some troubles. After an insertion on my form, in the afterInsert() event, I would run this SQL to insert into audits. However, if I pulled up my audits table after inserting an employee from my form, there would not yet be a record of this insertion. I was wondering if there would be problems there with concurrent use, because it seemed that there was a period of time in between the FORM insertion and the AuditLog insertion in the VBA code. \n Anyways, it seems to be working fine now. Thanks for your help! – Hazlip

Answer 2

try to format datetime like this:

protected DateTime GetDateWithoutMilliseconds(DateTime d)
    {
        return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second);
    }

Saloma