Access 2010 - 日期字段问题 [英] Access 2010 - Date field question

问题描述

我有一个数据库，其中有一个联系人表，一个简单的名字，电话和电子邮件。

I have a database that has a table in it for contacts, a simple name, phone, and email.

但是，我还有一个名为"b"的日期字段。上次修改后的".........

However, I also have a date field called "Last Modified".........

我只想知道代码和放在哪里以便随时更改任何记录的任何字段，数据库放置记录上次修改的日期。

I simply want to know what code and where to put it so that anytime any field of any record is changed, that the database puts the date that the record was last modified.

我认为这应该很简单，但我以前从未这样做过。

I think this should be pretty simple, but I've just never done this before.

谢谢提前获取任何建议。

Thanks in advance for any suggestions.

Steven Schuyler Berkeley，California USA

Steven Schuyler Berkeley, California USA

推荐答案

嗨史蒂文，

这听起来很简单，但这取决于您记录更改的方式和记录。

It sounds pretty simple, but it depends on how, and what you want to log your record changes.

关于你在这里可以找到的部分的一篇很好的文章Allen Browne的审计线索：

I good article on the how part you can find in here Audit trail by Allen Browne:

http://allenbrowne.com/appaudit.html

我写了一些代码给在过去执行类似的操作，在表单中更改字段值时保存tblLogChanges中的更改，您可以根据需要进行扩展/修改，请参阅下文：

I wrote a little code to do something similar in the past, saving changes in tblLogChanges when a Field value changed in a Form, which you can expand/ modify to your needs, see below:

首先创建表tblLogChanges例如，包含以下字段：

First create a Table tblLogChanges for example with the following fields:

ID（自动编号）

txtFieldName（文本）

txtFieldName (Text)

txtOldFieldValue（文本）

txtOldFieldValue (Text)

txtNewFieldValue（文本）

txtNewFieldValue (Text)

txtChangeDate（日期/时间）

txtChangeDate (Date/Time)

txtUser（文本）

txtUser (Text)

然后您可以在Before Update事件中使用以下代码：

Then you can use the following code in the Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim ctrl As Control
 Dim db As DAO.Database
 Dim rst As DAO.Recordset
 

Set db = CurrentDb
 Set rst = db.OpenRecordset("Select * From tblLogChanges")
 
If Me.Dirty Then
 
For Each ctrl In Me.Controls
 
 If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Then
     If ctrl.Value <> ctrl.OldValue Then
        
       rst.AddNew
        rst("txtFieldName").Value = ctrl.Name
        rst("txtOldFieldValue").Value = ctrl.OldValue
        rst("txtNewFieldValue").Value = ctrl.Value
        rst("txtChangeDate").Value = Now
        rst("txtUser").Value = Environ("UserName")
        rst.Update
      
    End If
   End If
   
  
Next ctrl
 
End If
 
  rst.Close
   
  Set rst = Nothing
  Set db = Nothing
   

End Sub

希望这会有所帮助，

这篇关于Access 2010 - 日期字段问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！