插入当前日期时间到审核表 [英] Inserting current DateTime into Audit table

问题描述

我是在实施审计日志来记录的简短更改数据库的描述的过程。我的审核表包含一个自动编号的PK，EMPID（数量），描述（备忘录），和auditDate（日期/时间）。我EMPID和说明被正确地插入，没有抛出错误，但没有被插入我的日期/时间。我想这可能不是那么简单放错位置的引号。我的VBA code是如下：

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:

在afterInsert事件：

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)

就像我说的，我可以得到精美的前三个值，但是当我试图插入日期的时候，我遇到的问题。任何输入AP preciated。但愿这不是简单，只要放错位置的引号，因为我想提交这个问题之前，引号的位置约4的变化：）

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

推荐答案

尝试这种方式。

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 

通过这种方法，你就不需要你的 currDateTime 变量。当数据库引擎评估 NOW（）功能......时间的日期/时间值将被确定在该插入语句。

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.

如果你想的时候按你原来的做法，格式为： currDateTime 并添加＃分隔符。

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

这篇关于插入当前日期时间到审核表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！