访问跟踪更改代码只有在打开数据库时按shift才能工作。

Question

我创建了一个数据库，供用户计划工作。处理记录后，使用复选框将其标记为“已输入”。我修改了一些我在网络上找到的代码，以跟踪用户所做的更改(只有在我标记它之后)，这样我才知道是否需要编辑某些内容。

这很好，，但是只有当我打开DB?!时才按shift键。

我使用"Form_BeforeUpdate()“和"Form_AfterUpdate()”事件来执行SQL以获取正确的行，然后比较它们。当我在打开DB时不按"shift“，‘前’和‘后’之间没有区别(两者都是‘前’值？)

因此，简而言之:在BeforeEvent上，记录存储在临时表中。在AfterEvent上，记录存储在另一个临时表中。对这两个字段进行比较，并将不同的字段存储在记录集中(连同用户名、日期、.)写到变更表上。就像一种魅力。但只有当我按shift打开数据库时..。

Private Sub Form_BeforeUpdate(Cancel As Integer)
            Dim sSQL    As String
            Dim sUN     As String     'UserName
    bWasNewRec = Me.NewRecord
    sUN = Environ("UserName")
    
    If Me.Ingevoerd = True Then    '"me.ingevoerd is the checkbox
        'Empty TEMP-table          'temp-tables are the 2 tables to store the event values
            sSQL = "DELETE FROM temp_BeforeUpdate;"
                CurrentDb.Execute sSQL
            sSQL = "DELETE FROM temp_AfterUpdate;"
                CurrentDb.Execute sSQL
        'Copy BeforeUpdate values to TEMP-table
        If Not bWasNewRec Then
            sSQL = "INSERT INTO temp_beforeupdate (changedType, changedDate, changedUser, tbl_ID) " & _
                   "SELECT 'OldVal' AS Expr1, Now() AS Expr2, '" & sUN & "' AS Expr3, " & Me.planningID & " as Expr4, * FROM tbl_Planning WHERE planningID = " & Me.planningID & ";"
            CurrentDb.Execute sSQL
        End If
    End If
End Sub

Private Sub Form_AfterUpdate()
        Dim recBefUp    As DAO.Recordset
        Dim recAftUp    As DAO.Recordset
        Dim recEdited   As DAO.Recordset
        Dim sSQL        As String
        Dim sUN         As String
        Dim fld         As Integer
    
    sUN = Environ("UserName")
    If ((Me.Ingevoerd = True) And (Not bWasNewRec)) Then
        sSQL = "INSERT INTO temp_AfterUpdate ( changedType, changedDate, changedUser, tbl_ID ) " & _
               "SELECT 'NewVal' AS Expr1, Now() AS Expr2, '" & sUN & "' AS Expr3, " & Me.planningID & " as Expr4, * FROM tbl_Planning WHERE planningID = " & Me.planningID & ";"
        
        CurrentDb.Execute sSQL
        Set recBefUp = CurrentDb.OpenRecordset("SELECT TOP 1 temp_BeforeUpdate.* FROM temp_BeforeUpdate WHERE (temp_BeforeUpdate.changedType = 'OldVal') ORDER BY temp_BeforeUpdate.changedDate DESC;")             'RecordBeforeUpdate = SQL voor update
        Set recAftUp = CurrentDb.OpenRecordset("SELECT TOP 1 temp_AfterUpdate.*  FROM temp_AfterUpdate  WHERE (temp_AfterUpdate.changedType = 'NewVal')  ORDER BY temp_AfterUpdate.changedDate DESC;")               'RecordAfterUpdate = SQL na update
        
        'enter in "ChangeLog":
            'Set recEdited = CurrentDb.OpenRecordset("ChangeLog")                                            'ChangeLog is where i check if something was changed
            Set recEdited = CurrentDb.OpenRecordset("SELECT * FROM ChangeLog")                              'Recordset to store the before- and after values
            For fld = 9 To recBefUp.Fields.Count - 1
                If Not (Nz(recBefUp.Fields(fld).Value, 0) = Nz(recAftUp.Fields(fld).Value, 0)) Then
                    recEdited.AddNew
                        recEdited.Fields(2).Value = recBefUp.Fields(2).Value                                'ChangedDate
                        recEdited.Fields(3).Value = recBefUp.Fields(3).Value                                'ChangedUser
                        recEdited.Fields(4).Value = "tbl_Planning"                                          'Formulier
                        recEdited.Fields(5).Value = recBefUp.Fields(8).Value                                'TOS
                        recEdited.Fields(6).Value = recBefUp.Fields(9).Value                                'Startdatum
                        recEdited.Fields(7).Value = recBefUp.Fields(5).Value                                'planningID
                        recEdited.Fields(8).Value = recBefUp.Fields(4).Value                                'ID-nummer
                        recEdited.Fields(9).Value = recBefUp.Fields(fld).Name                               'Veld
                        recEdited.Fields(10).Value = Nz(recBefUp.Fields(fld).Value, "-")                    'Oude waarde
                        recEdited.Fields(11).Value = Nz(recAftUp.Fields(fld).Value, "-")                    'Nieuwe waarde
                    recEdited.Update
                End If
            Next fld
        'Empty the temp table.
            sSQL = "DELETE FROM temp_BeforeUpdate;"
                CurrentDb.Execute sSQL
            sSQL = "DELETE FROM temp_AfterUpdate;"
                CurrentDb.Execute sSQL
        Set recBefUp = Nothing
        Set recAftUp = Nothing
    End If
End Sub

'''

Answer 1

我找出了一些代码来与BackEnd (表)建立稳定的连接。由于某种原因，这扰乱了AfterEvent。当我禁用下一部分的时候，它又起作用了.

Public dbConn As DAO.Database
    Private Sub Form_Close()
        Set dbConn = Nothing
    End Sub
    Private Sub Form_Open(Cancel As Integer)
        Set dbConn = OpenDatabase("X:\Arr22_TABLES.accdb")
    End Sub