What is the best way to flag if an MS Access record has been changed?
Posted on 2008-10-30
I am looking for the best method to flag an MS Access record that has been changed. I want to be able to run a query that will only show records that have been changed since a known baseline. I would like to do this for reporting and as a step in an importing routine. To do this, I added a "record changed" checkbox field to the record and user form in question. I can programmatically set this to false when I need to create a baseline. The trouble, or question, is how to set the checkbox to true when any field, or control value, in the record/form is changed or updated by the user.
I have tried this under the before update event.
Me.chkbxChanged.Value = True
This works if the user just saves or moves away from the changed record/form using the navigation buttons. However, the form in question has a combobox control that lets the user quickly move to a different record in the current record set. When the user navigates with the combobox control, the dreaded Error 3020, Update or CancelUpdate without AddNew or Edit occurs.
I think I can see why it does this, based on the combobox after update code.
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IssueKey] = '" & Me![Combo100] & "'"
Me.Bookmark = rs.Bookmark
I'm thinking the trouble is the form's before update event is firing when the combobox attempts to move to another record and the active RS record set can't be edited.
Do I need to modify the record changed field at the record level in the before update event instead of simply trying to modify the form's changed field? Something like:
Form_BeforeUpdate(Cancel As Integer)
Dim rs1 As Object
Set rs1 = Me.Recordset.Clone
'need to add code to select and modify only the current record if changed
mytable.changed.value = true
These are my thoughts so far, but there may be better way to do this. Ultimately, I just need a good method that works for flagging if a record has been changed or updated and works regardless of how the users navigates away from the changed record. Any suggestions would be most appreciated.