Solved

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

Posted on 2008-10-30
8
1,984 Views
Last Modified: 2013-11-27
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
    rs1.edit
   'need to add code to select and modify only the current record if changed
    mytable.changed.value = true
    rs1.update

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.

Thanks.
0
Comment
Question by:benpope
8 Comments
 
LVL 14

Expert Comment

by:pteranodon72
Comment Utility
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.

HTH,

pteranodon
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
benpope,

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)

JeffCoachman
Access-EEQ23863169AuditFormBefor.mdb
0
 
LVL 11

Expert Comment

by:datAdrenaline
Comment Utility
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


0
 

Author Comment

by:benpope
Comment Utility
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.  
 
Cheers,
Ben
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 125 total points
Comment Utility
benpope,

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"

JeffCoachman
0
 

Author Comment

by:benpope
Comment Utility
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.

Thanks,
Ben  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Sorry for the delay,

Do you still need help with this?
0
 

Author Closing Comment

by:benpope
Comment Utility
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.

Cheers,
Ben
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now