pskeens
asked on
How to compare record to see what was changed
What is the best way to look at a record and see what was changed before updating it?
Example:I have a Record and if the Products or Customer Instructions changed I want to mark it as a revision.
If nothing else changed but just adding a truck number and name when they sign in I just want to update the record and not mark it as revised.
So I need to look at the existing record and see what was changed before updating.
Unbound Form so I am writing the record and updates with VB which works fine, just need this to complete it.
Example:I have a Record and if the Products or Customer Instructions changed I want to mark it as a revision.
If nothing else changed but just adding a truck number and name when they sign in I just want to update the record and not mark it as revised.
So I need to look at the existing record and see what was changed before updating.
Unbound Form so I am writing the record and updates with VB which works fine, just need this to complete it.
Your post states that you need to "See" what changed.
Does this mean you want to "Display" the value (the before or after value), ...or does this simple mean you want to update the Revised field "silently"...?
It is also not clear if you need this on more than one field.
For example, you can do this if you want to update the Revised field if anything changed in the record.
Private Sub Form_BeforeUpdate(Cancel As Integer)
me.revised=True
End Sub
(Note that this is on the BeforeUpdate event of the *form*)
But note here the persistence...
If you created a record at 9am, I will presume that the Revised Field would be set to False. (buy default, because it is a new record, and hence, no revisions)
At 10AM the record is changed, now the Revised field gets set to True.
...so far so good...
Now, ...what happens if the record is changed again at 11 AM? (the "Change" from 10 am to 11 am)...How would this "change" be noted?
In other words, if the revised field is ever set to True, does it stay that way forever?...or at some point does this get reset?
If it gets reset, then you will have to manage this carefully
per form open close?
Per user?
Per user form open close
per day?
per Database Open/close
...etc
JeffCoachman
Does this mean you want to "Display" the value (the before or after value), ...or does this simple mean you want to update the Revised field "silently"...?
It is also not clear if you need this on more than one field.
For example, you can do this if you want to update the Revised field if anything changed in the record.
Private Sub Form_BeforeUpdate(Cancel As Integer)
me.revised=True
End Sub
(Note that this is on the BeforeUpdate event of the *form*)
But note here the persistence...
If you created a record at 9am, I will presume that the Revised Field would be set to False. (buy default, because it is a new record, and hence, no revisions)
At 10AM the record is changed, now the Revised field gets set to True.
...so far so good...
Now, ...what happens if the record is changed again at 11 AM? (the "Change" from 10 am to 11 am)...How would this "change" be noted?
In other words, if the revised field is ever set to True, does it stay that way forever?...or at some point does this get reset?
If it gets reset, then you will have to manage this carefully
per form open close?
Per user?
Per user form open close
per day?
per Database Open/close
...etc
JeffCoachman
How are you loading your unbound forms? Depending on how you do this, you may just be able to compare the current data (the value on the form) with the data that you used to fill the form. For example, if you are using a Class module to work with your form, you could easily build a method to flag the class whenever your relevant data is changed.
ASKER
Scott, I am loading the form with through this Recordset
Let me clarify a couple of things. I do not need to verify the change on load, I need to verify it on save. If these things change:
Carrier
Appointment Time
Arrival Time
Vehicle Number
Then I do not want to add to the revision #.
What happens is on save it looks to see if the load was revised before using a lookup string. If it was revised then it adds one to the previous number to get the next revision #. If it was not revised then it updated the record (column REVISED) with "Y" and sets the revision # to 1.
This is with any other change other than listed above. If only these things listed are changed it does not trigger a revision and should only be an update to the record.
strSQL = "Select * from VW_LOAD_HED where LD_NUM =" & sParm
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
txt_carrier = rs("LD_CARRIER")
txt_reqDate = rs("LD_REQ_DATE")
txt_shipDate = rs("LD_SHIP_DATE")
cbo_custID = rs("LD_CUST_ID")
txt_CustID = rs("LD_CUST_ID")
txt_CustName = rs("LD_CUST_NAME")
txt_CustCity = rs("LD_CUST_CITY")
txt_CustState = rs("LD_CUST_STATE")
txt_CustZip = rs("LD_CUST_ZIP")
txt_custPhone = rs("CUST_PHONE")
txt_delCarrier = rs("LD_DEL_CARRIER")
Me.txt_apptDate = rs("LD_APPT_DATE")
Me.txt_apptTime = rs("LD_ARRIV_DATE")
cbo_shipToID = rs("LD_SHIPTO_ID")
txt_shipToName = rs("DET_NAME")
txt_shipToCity = rs("DET_CITY")
txt_shipToState = rs("DET_STATE")
txt_shipToZip = rs("DET_ZIP")
txt_shipToPh = rs("DET_PHONE")
txt_Instr = rs("LD_INSTRUCTIONS")
Me.txt_delDate = rs("LD_DEL_DATE")
txt_bolNum = rs("LD_SHIP_NUM")
txt_bolLoc = rs("LD_SHIP_LOC")
cbo_inbShipperID = rs("LD_INB_SHIPPER")
txt_locName = rs("LD_LOC")
cbo_loc = rs("LD_LOC")
txt_createdBy = rs("LD_CREATED_BY")
txt_createdOn = rs("LD_CREATED_DATE")
txt_modifiedBy = rs("LD_MODIFIED_BY")
txt_modifiedOn = rs("LD_MODIFIED_DATE")
CHK_CANCELLED = rs("LD_CANCELLED")
txt_PrtDate = rs("LD_PRINT_DATE")
If rs.("LD_REV_NUM") > 0 Then
txt_rev.Visible = True
lbl_rev.Visible = True
txt_rev = rs("LD_REV_NUM")
Else
txt_rev.Visible = False
lbl_rev.Visible = False
End If
rs.Close
Set rs = Nothing
Let me clarify a couple of things. I do not need to verify the change on load, I need to verify it on save. If these things change:
Carrier
Appointment Time
Arrival Time
Vehicle Number
Then I do not want to add to the revision #.
What happens is on save it looks to see if the load was revised before using a lookup string. If it was revised then it adds one to the previous number to get the next revision #. If it was not revised then it updated the record (column REVISED) with "Y" and sets the revision # to 1.
This is with any other change other than listed above. If only these things listed are changed it does not trigger a revision and should only be an update to the record.
<unbound forms>
...oops, ...didn't notice that...
...oops, ...didn't notice that...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh,
Your rev's are already good
<What happens is on save it looks to see if the load was revised before using a lookup string. If it was revised then it adds one to the previous number to get the next revision #. If it was not revised then it updated the record (column REVISED) with "Y" and sets the revision # to 1.>
Your rev's are already good
<What happens is on save it looks to see if the load was revised before using a lookup string. If it was revised then it adds one to the previous number to get the next revision #. If it was not revised then it updated the record (column REVISED) with "Y" and sets the revision # to 1.>
ASKER
still working on this guys, but a little delay. Will come back and accept when I get back to this item.
ASKER
Both good answers and I have actually used both in two different circumstances. Thanks as always!
The syntax will be something like
Private Sub BeforeUpdate_txtInstructio
If Me.txtInstruction Is Not Like DLookup("fldInstruction","
Me.fldRevised=Yes
End If
End Sub
You may need additional code if there is no instructions and I can help with that if you wish to proceed with this approach.
An alternative would be to use a bounded for for the component that may need revising. Then use the onchange event to trigger the revise action.