What is the best way to flag if an MS Access record has been changed?

Hello Experts.

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.

Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

Again, you can do all of this without a checkbox, you can just compare the dates.

Yes, A checkbox may make it easire to identify the matching/not matching records.

However, ...the added layer of having to update this Value complicates things.
Things like this should be "calculated" (based on comparing dates), not "Stored" (in a checkbox)
Meaning, at any point in you complex logic, you "Forget"  to update the checkboxes, (Or it is updated at the wrong time), everything could fall apart.

In other words, this is like storing a Total Price calculation, you always have to "Update" it. When you should just Calculate it with a query, and it wll always be current.

Finally, What you are not providing us with is a real world example of this "Baseline" in action.

Can you walk us throught a real world scenario of how this system would work?
Because what you just listed complicates this question quite a bit.
Thus confusing what you really need here as the ultimate "Solution"

In the navigation combo's afterupdate, try adding:

If Me.Dirty = True Then
  Me.Dirty = False
End If

before you re-navigate.  This forces a save before you move.


Jeffrey CoachmanMIS LiasonCommented:

Can you explain what you mean by "Baseline"?
Is this a date or date range?

In any event, here is a simple sample.
The DateChanged updates whenever the record is changed.
You can run any of the sample queries I created as your "Baseline"
(I don't really see the need for a checkbox)

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

I agree with Boag2000 ... No need for a check box! ... it sould be a DateChanged field ...

With I a DateChanged field, I would suggest the use of the forms OnDirty event ...

Private Sub Form_Dirty(Cancel As Integer)
    Me.DateChanged = Now()
End Sub

I like the OnDirty event because you know when the record was opened for editing... but that is just me.  Otherwise, I would use the Forms BeforeUpdate event ... but not like you have tried to use it ..

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.DateChanged = Now()
End Sub

benpopeAuthor Commented:
Thank for the comments Folks,

You are literally thinking outside the checkbox.  I had not thought about using a date changed field and need to consider how that would work for my purpose.

I was originally looking at using the checkbox for this reason.  I want to be able to create a baseline, or snapshot, of a given record set at a designated point in time and then determine later if any of the records have been changed.  My reasoning to use a checkbox was that I could run an update query, or code, to set all the check boxes in the selected record set to false when I wanted to create a baseline.  Then change the checkbox value to true when a record changes.  Then I can they know which records in the selected record set changed and do something with the information like print a report with only changed records.   I was also lookiing at using this a means to only import changed records.  For instance, if a user downloaded ,or checked out, records from the server to a client that could then work offline.  I could lock the server records while they were checked out and then only import the changed records when the client came back online.  I was thinking this would be a very straight forward yes or no way of indicating if a record changed or or not.

The idea of using the ondirty method method with the date changed field would eliminate the need to reset the checkbox value to false as it would update each time  the record changed.  I would just need to base my changed querry on a user input (or stored) date / time instead of true or false.  The ondirty event would also fire in a sequence that I don't think will cause a conflict and error 3020 when navigating with the drop down combo box.  I may also be able to use the ondirty event with the original checkbox idea.

I'll have to test this out and reply with my findings.  
benpopeAuthor Commented:
Sorry for the delay in getting back to this and I appreciate your comments.  

My first thoughts were that I needed to create a "baseline" reference for a given record set by setting a "changed" check box field value false at a user defined point in time.   Then, I could set that value true if any existing record changed or if a new record was created.   That way I could determine any differences to the original given record set after the "baseline" was established.

An example scenario of how this would be used with our engineering issues database is a follows:
A user needs to download a filtered record set to work offline from the main server where the backend data tables are stored.  They need to do this if they have to take the engineering issues with them for field work to someplace like a manufacturing plant in Mexico where they can't get an internet connection back to the company server.   To initiate the download, they select a command button from a menu which prompts them to define the record set and then copies all the required server data and programming to their client machine.  The download sequence also flags the downloaded records as "checked out" on the server and locks them from being edited until they are checked back in.  The user now has all the records they need to take with them as a stand alone application.  When they return and reconnect to the server, they select a command button from the database menu to upload, or "check in" the modified record set.  This will unlock the server version of the "checked out" record set and then copy the modified or new records back up to the server.  To program this upload sequence, I wanted to know only the records that were changed, or created, since "check out" so that I would only be updating and adding the differences to the originally downloaded record set.

Another application would be to allow managers to run a query based report to view only records that have been changed since a given point in time.   This one is easy using the suggested date changed field.

Based the comments from you and the others, I think a date changed field will suffice for my needs.  The date changed field and using a query based on the changed date seems like a better solution as it provides more information then simply true or false.  The only trick is that I think I would need to store the date a record set is "checked out" and not make that a user entered parameter on "check in".  Folks could incorrectly enter the "check out" date which has the potential to cause problems during the "check in" sequence.

Hopefully, this helps explain my end purpose more clearly.  I have all the pieces to the desired Check Out/In sequence mentally mapped out (not fully programmed yet), but was having problems with my method of indicating a changed record.  What I was trying to do and where I had the event firing was causing an unanticipated problem with my drop down record navigation control.   I have to get my method of determining which records have been changed nailed down before I can finish writing the remainder of the programming and reports that require it.

Any comments would be appreciated.  Maybe there is a better way of accomplishing the end task of allowing a user of a filtered record set to work off line and then assimilating that modified filtered record set to back to the main record collections.

Jeffrey CoachmanMIS LiasonCommented:
Sorry for the delay,

Do you still need help with this?
benpopeAuthor Commented:
Hi Boaq2000,

I believe I can make the date changed field do what I need, so I will accept that as the solution.  I still need to write and test some code, but other tasks at work are preventing me from doing this right now.   I'll have to get back around to this in more detail when I can.  I appreciate your comments and support and will work with the data changed field as opposed to trying to use a check box as suggested.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.