How track all changes made to a record in Microsoft Access

tranvangiang17
tranvangiang17 used Ask the Experts™
on
Dear Experts:

I was requested to add a “Date/Time” field on a table to time-stamp a new record and / or any changes on the record (inputting from a input form).  The new filed called “DateModified” was added onto the table (and an invisible “DateModified” text box on the input form as well); then I add the following code In the before update event:

Private Sub DATE_MODIFIED_BeforeUpdate(Cancel As Integer)
        Me!DateModified = Now()
End Sub


However, the “DateModified” has been time-stamped even though there is NO changes made to the record at all (I have just opened form to view the record and exit the form without any changes).

Please help me to correct the problem: That is, only set the “DateModified” to “Now()” value whenever there is any changes in the record or newly created record..
 Input Form with added DateModified field.jpg DateModified Time-Stamped.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try changing to the _AfterUpdate event.
Im not to sure on this one, cause i havent used it with access, but what about setting up auditing as a GP to track the changes made to it. i use it now for word docs and excel. Just a thought.

Author

Commented:
Dear Andrew Webster

I did change the _BeforeUpdate to _AfterUpdate event:


Private Sub DATE_MODIFIED_AfterUpdate()
    Me!DateModified = Now()
End Sub

After clearing out all old "DateModified", I changed the top record and open / close the next 4 record... the "DtaeModified" field for all 5 test are still blank / null (see snapshot attached).

Have I missed something?

Van Tran

Author

Commented:
Dear Andrew Webster,

File Attached  (Sorry, I somehow missed this for the previous post)

Van
DateModified-Result-AfterUpdate.jpg
My bad.

It should be the FORM's Before_Update event.

Private Sub Form_Before_Update
    Me!DateModified = Now()
End Sub

That'll do it.

Author

Commented:
Hi Andrew Webster,

I have tried your advice shown below:

Private Sub Form_BeforeUpdate
   Me!DateModified = Now()
End Sub

The result returns to square #1 as I've  submitted at the beginning: That is the record(s) were open for viewing / no changes still had the date stamped!!!  Please see the attachment for details (i.e. first recors has changes time-stamped OK; but the other 4 records have NO chages... but they're all stamped with Date & Time too???.

Please help...

Van
DateModified-Result-FORM-BeforeU.jpg
Ok.  If I were you, I'd check your code elsewhere in the form as that MUST mean that something is updating your data.  The code I've given you is correct, it won't run unless there is a change made to the data.  However changes can be mode both by the user, and possibly by code elsewhere.  Check the Me.Dirty flag (property) on the form, as that is set to True  if changes are made to the data.

You've got a bit of detective work to do.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
" (and an invisible “DateModified” text box on the input form as well)"
No need for that.

 "The new filed called “DateModified” was added onto the table"

I have this in almost every application:

Private Sub Form_BeforeUpdate (Cancel As Integer)
  ' you need to test for NOT a new record
If Not Me.NewRecord Then Me![DateModified] = Now()   ' update the field in the table

End Sub

mx
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
Suggestion - use two fields:

DateEntered
DateModified

If they are BOTH the same date, then you know the record hasn't been updated.  If they are different, you know when the record was created and when it was updated.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Suggestion - use two fields:"

Except:

Private Sub Form_BeforeUpdate (Cancel As Integer)
  ' you need to test for NOT a new record
If Not Me.NewRecord Then
     Me![DateModified] = Now()   ' update the field in the table
Else
     Me![DateEntered] = Now()   ' new record only
End If
End Sub

mx
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
In all my tables, I use DateEntered and DateModified fields.  I typically set the DateEntered field with a default value.  Then update DateModified field via programming...  so I wouldn't (shouldn't, I expect) have a need for the code DatabaseMX just provided.

Just my 2 more cents.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I've actually seen Default values get wiped out, accidentally or due to corruption.  The code approach guarantees both values will be set.

mx

Author

Commented:
Drea DatabaseMZ,

Per your instruction, I have added the following lines of code at the end of the form's VB code (see code attache)

Private Sub Form_BeforeUpdate (Cancel As Integer)
  ' you need to test for NOT a new record
If Not Me.NewRecord Then
     Me![DateModified] = Now()   ' update the field in the table
Else
     Me![DateEntered] = Now()   ' new record only
End If
End Sub

The results shown were as same as those of the beginning question: The record(s)  opend for viewing also shown as updated (time stamped on "Date Modified).

I have attached the snapshots of:

- The Form design
- The Form Code (VB code for the form)
- The table before update
- The table after update

Please note that only the first record was updated; the rest (last 4) were opened to view without any changes (but still have dated stamped?)

I do not see why?

Please help.

Thanks

Van

Option Compare Database
Option Explicit

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command69_Click:
    Exit Sub

Err_Command69_Click:
    MsgBox Err.Description
    Resume Exit_Command69_Click
    
End Sub


Private Sub Form_Current()
    Call JOB_CAT_AfterUpdate
    Call OE__Y_N__Change
End Sub

Private Sub JOB_CAT_AfterUpdate()
    If JOB_CAT.Value <> "" And OE__Y_N_.Value <> "N" Then
        Dim cnnCurrentDB As ADODB.Connection
        Dim rstCategoryDescription As ADODB.Recordset
        
        Set cnnCurrentDB = New ADODB.Connection
        Set rstCategoryDescription = New ADODB.Recordset
        
        Set cnnCurrentDB = CurrentProject.Connection
        rstCategoryDescription.Open ("SELECT * FROM [Job Category Table]"), cnnCurrentDB, adOpenDynamic, adLockReadOnly
    
        rstCategoryDescription.Find "JOB_CAT='" & JOB_CAT.Value & "'"
        EMPLOY_TYPE.Value = rstCategoryDescription("Category")
        
        rstCategoryDescription.Close
        cnnCurrentDB.Close
    Else
        EMPLOY_TYPE.Value = ""
    End If
End Sub


Private Sub OE__Y_N__Change()
    If OE__Y_N_.Value = "Y" Then
        JOB_CAT.Enabled = True
    Else
        JOB_CAT.Value = ""
        JOB_CAT.Enabled = False
    End If
    
    If OE__Y_N_.Value = "Y" Then
        EMPLOY_TYPE.Enabled = True
    Else
        EMPLOY_TYPE.Value = ""
        EMPLOY_TYPE.Enabled = False
    End If
    
    If OE__Y_N_.Value = "Y" Then
        BUSINESS_NM.Enabled = True
    Else
        BUSINESS_NM.Value = ""
        BUSINESS_NM.Enabled = False
    End If
    
    If OE__Y_N_.Value = "Y" Then
        POSITION.Enabled = True
    Else
        POSITION.Value = ""
        POSITION.Enabled = False
    End If
    
    
End Sub

Private Sub OE__Y_N__LostFocus()
    Call OE__Y_N__Change
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)


If Me.[OE?(Y/N)] = "Y" Then
If Len(Me.JOB_CAT & "") = 0 Then
   MsgBox "Job Category is a required field"
   Me.JOB_CAT.SetFocus
   Cancel = True
   Exit Sub
End If
End If


' you need to test for NOT a new record
If Not Me.NewRecord Then
     Me![DateModified] = Now()   ' update the field in the table
Else
     Me![DateEntered] = Now()   ' new record only
End If



End Sub

Open in new window

Snapshot-of-table--befere-Mod.bmp
Snapshot-of-table--after-Mod.bmp
Form-with-2-added-Date-instances.bmp
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"However, the “DateModified” has been time-stamped even though there is NO changes made to the record at all (I have just opened form to view the record and exit the form without any changes)."

Post any code you have in the Form Open, Load and Current events.

mx

Author

Commented:
Dear DatabaseMX,

Since I've inherited the application from somebody who already left the Dept; so I am kind of confused (with regard to how to make changes on the design per customer requests; please forgive me for this).

The form was open with a click on the first "option" (namely "Option1") on the Main Switchboard .  The Code Snippet shows the complete Code for the switchboard that includes the "form open  / load" and Current event.

Once the form (Option1 - on OE Data Form) is open, the "step" ( i.e. clicking on forward arrow at bottom of from) will move to the "next record"; and the previously viewed / updated records were SAVED...  Pleas note that  these stepping forward also time-stamped the record(s) with NO changes (just viewing).

I hope these explanations and attachments will help you so you can help me since my knowledge on VB form is limited.

Many thanks; and I remains.

Van
Option Compare Database
Option Explicit

Private Sub Command26_Click()
On Error GoTo Routine1

DoCmd.TransferText acExportDelim, , "OE Data", "C:\windows\OEData.txt"
DoCmd.TransferText acExportDelim, , "Bureau Table", "C:\windows\OEBureauTable.txt"
DoCmd.TransferText acExportDelim, , "Item Desc Table", "C:\windows\OEItemDescTable.txt"
DoCmd.TransferText acExportDelim, , "Job Category Table", "C:\windows\OEJobCategoryTable.txt"
DoCmd.TransferText acExportDelim, , "Section Table", "C:\windows\OESectionTable.txt"
MsgBox "Backup complete. Thank you!"

Routine2:
    Exit Sub
Routine1:
    MsgBox Error$
    Exit Sub
End Sub

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True
    DoCmd.Maximize
    
End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

    Me.Caption = Nz(Me![ItemText], "")
    FillOptions
    
End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

    ' The number of buttons on the form.
    Const conNumButtons = 8
    
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
    Dim intOption As Integer
    
    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with the focus.
    Me![Option1].SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption
    
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set dbs = CurrentDb()
    strSQL = "SELECT * FROM [Switchboard Items]"
    strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    strSQL = strSQL & " ORDER BY [ItemNumber];"
    Set rst = dbs.OpenRecordset(strSQL)
    
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (rst.EOF) Then
        Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
        While (Not (rst.EOF))
            Me("Option" & rst![ItemNumber]).Visible = True
            Me("OptionLabel" & rst![ItemNumber]).Visible = True
            Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
            rst.MoveNext
        Wend
    End If

    ' Close the recordset and the database.
    rst.Close
    dbs.Close

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

    ' Constants for the commands that can be executed.
    Const conCmdGotoSwitchboard = 1
    Const conCmdOpenFormAdd = 2
    Const conCmdOpenFormBrowse = 3
    Const conCmdOpenReport = 4
    Const conCmdCustomizeSwitchboard = 5
    Const conCmdExitApplication = 6
    Const conCmdRunMacro = 7
    Const conCmdRunCode = 8

    ' An error that is special cased.
    Const conErrDoCmdCancelled = 2501
    
    Dim dbs As Database
    Dim rst As Recordset

On Error GoTo HandleButtonClick_Err

    ' Find the item in the Switchboard Items table
    ' that corresponds to the button that was clicked.
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
    rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
    
    ' If no item matches, report the error and exit the function.
    If (rst.NoMatch) Then
        MsgBox "There was an error reading the Switchboard Items table."
        rst.Close
        dbs.Close
        Exit Function
    End If
    
    Select Case rst![Command]
        
        ' Go to another switchboard.
        Case conCmdGotoSwitchboard
            Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]
            
        ' Open a form in Add mode.
        Case conCmdOpenFormAdd
            DoCmd.OpenForm rst![Argument], , , , acAdd

        ' Open a form.
        Case conCmdOpenFormBrowse
            DoCmd.OpenForm rst![Argument]

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rst![Argument], acPreview

        ' Customize the Switchboard.
        Case conCmdCustomizeSwitchboard
            ' Handle the case where the Switchboard Manager
            ' is not installed (e.g. Minimal Install).
            On Error Resume Next
            Application.Run "WZMAIN80.sbm_Entry"
            If (Err <> 0) Then MsgBox "Command not available."
            On Error GoTo 0
            ' Update the form.
            Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
            Me.Caption = Nz(Me![ItemText], "")
            FillOptions

        ' Exit the application.
        Case conCmdExitApplication
            CloseCurrentDatabase

        ' Run a macro.
        Case conCmdRunMacro
            DoCmd.RunMacro rst![Argument]

        ' Run code.
        Case conCmdRunCode
            Application.Run rst![Argument]

        ' Any other command is unrecognized.
        Case Else
            MsgBox "Unknown option."
    
    End Select

    ' Close the recordset and the database.
    rst.Close
    dbs.Close
    
HandleButtonClick_Exit:
    Exit Function

HandleButtonClick_Err:
    ' If the action was cancelled by the user for
    ' some reason, don't display an error message.
    ' Instead, resume on the next line.
    If (Err = conErrDoCmdCancelled) Then
        Resume Next
    Else
        MsgBox "There was an error executing the command.", vbCritical
        Resume HandleButtonClick_Exit
    End If
    
End Function

Open in new window

Snapshot-of-Option1-on-switchboa.bmp
Snapshot-of-Option1-Property--on.bmp
Snapshot-of-Option1---OE-Form---.bmp
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Is there any code on the Form Current event of the form that is being opened and has this issue ?

Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

mx

Author

Commented:
Dear DatabaseMX,

I attached the "OE 2010" App (in ZIP file) with only 5 records (with only fake info) in the OE Data table.

Please note:
- The concerned table is : OE Data
- The concerned Form is : OE Data (i.e. the first option on the main menu / switchboard)

To reproduce the problems:

1- Open the OE Data Form (either through the main menu / first option or through the OE Data Form on DB)
2- Make some change(s) (say NEW comments and / or any inputs box that are NOT read-only type)  on only 1 or 2 records on top.
3- Use arrow (at the bottom of "OE Data" form) to go / step to the next records (only 5 in the DB now)
4- Open the OE Data table and look at the "DateModifed" field to see the problem (i.e. Date-Time  stamped on the records that have no changes!!!)

Please let me know your response.

Thanks.

Van
OE-2010-for-DatabaseMX.zip
Database Architect / Application Developer
Top Expert 2007
Commented:
This issue is as I suggested.  There is code running in the Form Current event that is changing values on your form, such that as you navigate through records, EACH record immediately becomes Dirty (edited).  If if the user does nothing but nav to the next record, the current record ends up being 'saved' ... more importantly the BeforeUpdate event occurs - thus setting your DateModified value, even though the user did not really 'modify' anything (in this scenario).

You first issue in when you execute this:
Call JOB_CAT_AfterUpdate
which down in that code a bit does this:

EMPLOY_TYPE.Value = rstCategoryDescription("Category")

which causes the record to become dirty.  You can see this yourself by temporarily turning on the Form Record Selectors ... and you will see that pencil come on as soon as the form loads ... and will appear for each record you nav to.

So, you need to fix this first. Then ... there is more in the next call

Call OE__Y_N__Change   ... that code changes several values.

It's a really bad idea to set values in the Form current event, for exactly this and several other reasons.  The Form Current event is mainly intended to enable/disable controls and/or make controls visible/not visible based on the values of other controls ... among other things.

So ... you have a bit of redesign to do.

Setting values such as you are doing can be (and should be) done in the Form Before Insert and Form Before Update event - such as where we are trying to set the DateModified.

mx

Author

Commented:
Thank you DatabaseMX.

Per your suggestion,  am working on redesign the form.

In the event I run into any other issue. I will contact EE again for assistance.  Otherwise, for this moment I know the direction to resolve the problem.

Thanks again.

Van
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
you are welcome

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial