benpope
asked on
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
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.
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.
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
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Sorry for the delay,
Do you still need help with this?
Do you still need help with this?
ASKER
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
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
If Me.Dirty = True Then
Me.Dirty = False
End If
before you re-navigate. This forces a save before you move.
HTH,
pteranodon