Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

How to compare record to see what was changed

Posted on 2011-09-17
10
Medium Priority
?
288 Views
Last Modified: 2012-05-12
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.

0
Comment
Question by:pskeens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 36555432
To do this you need to use the before update method for the fields that you want to demarcate as being revised. Since that you are using an unbounded form you will need to perform a DLookup to check if the new value is the same as the value in the table.

The syntax will be something like

Private Sub BeforeUpdate_txtInstruction

If Me.txtInstruction Is Not Like DLookup("fldInstruction","tblCustomerInstruction","fldCostomerID=" & Me.fldCustomerID) then

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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36555516
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
0
 
LVL 85
ID: 36555976
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:pskeens
ID: 36556500
Scott, I am loading the form with through this Recordset

   
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

Open in new window


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.


0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36556629
<unbound forms>
...oops, ...didn't notice that...
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 36556653
You can just compare the value in the Textbox with your recordset value when you run your Save routine, assuming that you still have that Recordset:

Dim bAddRev As Boolean
'/ Assume you'll change it
bAddRev = True

txt_Carrier.SetFocus
If txt_Carrier.Text <> rst("LD_CARRIER") Then
  bAddRev = False
End If

If bAddRev Then
  txt_apptTime.SetFocus
  If txt_ApptTime.Rext <> rst("LD_ARRIV_DATE") Then
    bAddRev = False
  End If
End If

< and so on for your other fields>

If bAddRev Then
  '/ Add to the Revision #
End If
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 1000 total points
ID: 36569132
I'm with @LSMConsulting on that.
If you persist the recordset that provided the data in the first place, it is easy to compare the values in the controls in your save routine.
Even if you didn't it's easy enough to generate a similar recordset and do the comparsion
Dim GotRevved as boolean
strSQL = "Select * from VW_LOAD_HED where LD_NUM =" & sParm
Set rs = CurrentDb.OpenRecordset(strSQL)
GotRevved = False
GotRevved = GotRevved Or (Me.txt_carrier <> rs("LD_CARRIER")
GotRevved = GotRevved Or (Me.txt_apptTime <> rs("LD_ARRIV_DATE")
GotRevved = GotRevved Or (whatever tests for arrivaltime)
GotRevved = GotRevved Or (whatever tests for vehicle number)

Then when you update
if GotRevved = true then
rs!RevNumber =rs!RevNumber + 1
end if

Relating back to what @boag2000 was getting at, make your revision field an integer with a default of zero and not a boolean.
Each time the condtions for a revision occur, you increment the rev number by one
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569146
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.>
0
 
LVL 2

Author Comment

by:pskeens
ID: 36664493
still working on this guys, but a little delay.  Will come back and accept when I get back to this item.  
0
 
LVL 2

Author Closing Comment

by:pskeens
ID: 36953011
Both good answers and I have actually used both in two different circumstances.  Thanks as always!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

647 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