On Dirty, save all changes as NEW record

[Colin@Toyota]

Hi All,

As the title states, I need to find a way to check if changes were made to a record, and if they were, save the info as a new record, leaving the original untouched.

I would guess this could be accomplished using the on dirty event and perhaps some "insert into" sql, but in reading about applications of the on dirty event, all I can find is how to save changes to the current record.

I have looked at the creation of audit trails, and tried to apply the code to what I need, but have thus far failed...

Can anyone offer some insight? Or a reference?

Cheers,

Colin

 

[ajetrumpet]

Hi All,

As the title states, I need to find a way to check if changes were made to a record, and if they were, save the info as a new record, leaving the original untouched.

Colin,

Dirtying a form only applies to the bound objects. that's the first thing to consider here. The next thing to consider would be how to "preserve" all of the original values, which you should probably do through variables. that might be the easiest way to do it. in other words:

1) on open of form, pass all bound fields of the table to variables
2) on close of form, check for the dirty property
3) if true, create a new record in the table you want and fill the fields with the values from the variables (using a recordset object in VB)

remember though, the dirty() property of the form refers to CHANGES made to bound forms. so in other words, if a bound control is changed from the orig. value, and then changed back to the orig. value before the form is closed, technically no changes took place to the record, but the dirty() property is STILL TRUE. be careful of that scenario, or you'll end up with dups in the table.

 

[Colin@Toyota]

Hey Adam!

I was hoping you might be lurking around today!

Essentially, this db is fairly simple... It tracks historic pricing of our parts. So when we need to update the exhange rate on parts sourced from out of country, or if the duty cost changes, I don't want to have to re-enter all the other information.

Part info is in the form (part number, description, etc) and the pricing info is in the subform (these are two tables linked by part number). Also, all the text boxes are bound!

Fortunately for me, I did a little bit of research on how the best way to go about this might be before I started builing it, so I know about the limitations and potential consequences of using the on dirty event (but thanks for summing it up so consisely!).

I just don't have the experience to come up with "just use variables" to hold the previous values of the fields!

As for your 3), how would I go about using a recordset object, and what are the differences/benefits to using that over some "insert into" SQL?

Cheers,

Colin


P.S.
Good to hear from ya!

 

[Colin@Toyota]

Also, since the only data being edited will be on the subform, would the on load event be better for passing the field values to variables? Instead of the on close for the me.dirty check, is there an event that would run if the user goes to the next record, closes the form, etc?

 

[ajetrumpet]

i attached an example. notice the following:

1) if you change the bound fields and press the button, a new record is added
2) if you change them, but press the button after you change them back to their original values, no record is added, because the form is the same as when you opened it (per the variables that have the orig. values in them)
3) if you press the button and don't dirty the form, nothing happens to the table


let me know if this makes no sense.

 

[Colin@Toyota]

I was just about to post a question about that (changing a value and changing it back), as I was just reading a little bit about the .oldvalue

I will take a look, and let you know.

As always, I really appreciate you help buddy!

 

[Colin@Toyota]

Since any of the fields that would be changed by a user are all numbers or a checkbox, would it be beneficial to store the variables as doubles, integers and currency?

Also, instead of a command button, what if I put the me.dirty check in the subform's before update event?

And since I have about 10 variables, might it be more efficient to use the .oldvalue in something like:

for each cntrl in me.controls
if cntrl <> cntrl.oldvalue then
etc...

?

 

[ajetrumpet]

Since any of the fields that would be changed by a user are all numbers or a checkbox, would it be beneficial to store the variables as doubles, integers and currency?

store the variables according to what type they are in the table fields. that should work.

Also, instead of a command button, what if I put the me.dirty check in the subform's before update event?

i spose that would work, but it depends on the situation. if you focus the subform and change it right away, then the dirty() property has been used. so be careful of that.


as far as the oldvalue stuff is concerned, i would test it to see if that works. your sample code is on the right track. if it works, i would certainly use it myself.

 

[Colin@Toyota]

Cool, thanks dude.

One more thing, since this is occuring in a subform that is linked to the main form by the part number field, do I need to bring the part number as a variable to the new record? or will that happen automatically along with the PartPricingID (autonumber) field?

i spose that would work, but it depends on the situation. if you focus the subform and change it right away, then the dirty() property has been used. so be careful of that.

But it should be ok with the built-in check for each field's value, no?

 

[Colin@Toyota]

Hehehe

One more "one more thing"...

   If Me.Dirty = True Then
      If varPart <> Me.partnumber Or var1 <> Me.one Or var2 <> Me.two Or var3 <> Me.three Or var4 <> Me.four Or var5 <> Me.five Then
         DoCmd.SetWarnings False
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
               With rs
                  .AddNew
                     ![partnumber] = varPart
                     ![1] = var1
                     ![2] = var2
                     ![3] = var3
                     ![4] = var4
                     ![5] = var5
                  .Update
                  .Close
               End With
            Set rs = Nothing
         DoCmd.SetWarnings True
               DoCmd.Close
      Else
         DoCmd.Close
      End If
   Else
      DoCmd.Close
   End If

Isn't this setting the new record to the values that we stored in the variables? (which would be the original values)

 

[ajetrumpet]

Cool, thanks dude.

One more thing, since this is occuring in a subform that is linked to the main form by the part number field, do I need to bring the part number as a variable to the new record? or will that happen automatically along with the PartPricingID (autonumber) field?

you have to include the PK in the variables Colin, otherwise the field can't be filled in for the new record when you go to write the stuff to the recordset.



But it should be ok with the built-in check for each field's value, no?[/QUOTE]

I think you are right on this.

 

[Colin@Toyota]

you have to include the PK in the variables Colin, otherwise the field can't be filled in for the new record when you go to write the stuff to the recordset.

So for this table (recordset) the PK is an autonumber field "PartsPricingID". How do I include that?

The foreign key is the part number, which I gather I have to include as well...

 

[ajetrumpet]

Hehehe

One more "one more thing"...

   If Me.Dirty = True Then
      If varPart <> Me.partnumber Or var1 <> Me.one Or var2 <> Me.two Or var3 <> Me.three Or var4 <> Me.four Or var5 <> Me.five Then
         DoCmd.SetWarnings False
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
               With rs
                  .AddNew
                     ![partnumber] = varPart
                     ![1] = var1
                     ![2] = var2
                     ![3] = var3
                     ![4] = var4
                     ![5] = var5
                  .Update
                  .Close
               End With
            Set rs = Nothing
         DoCmd.SetWarnings True
               DoCmd.Close
      Else
         DoCmd.Close
      End If
   Else
      DoCmd.Close
   End If

Isn't this setting the new record to the values that we stored in the variables? (which would be the original values)

Here's the code back, with comments:

'[color=red]CHECK TO SEE IF FIELDS WERE CHANGED AT ANY POINT[/COLOR]
If Me.Dirty = True Then
      '[color=red]IF ANYTHING IS CHANGED, CREATE THE NEW RECORD FROM ALL OF THE VARIABLES[/COLOR]
      If varPart <> Me.partnumber Or var1 <> Me.one Or var2 <> Me.two Or var3 <> Me.three Or var4 <> Me.four Or var5 <> Me.five Then
         DoCmd.SetWarnings False
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
               With rs
                  .AddNew
                     '[color=red]WRITE THE OLD VALUES TO THE TABLE FROM WHEN YOU OPENED THE FORM[/COLOR]
                     ![partnumber] = varPart
                     ![1] = var1
                     ![2] = var2
                     ![3] = var3
                     ![4] = var4
                     ![5] = var5
                  .Update
                  .Close
               End With
            Set rs = Nothing
         DoCmd.SetWarnings True
               DoCmd.Close
      Else
         DoCmd.Close
      End If
   Else
      DoCmd.Close
   End If

 

[ajetrumpet]

so for this table (recordset) the pk is an autonumber field "partspricingid". How do i include that?

The foreign key is the part number, which i gather i have to include as well...

my mistake colin, i'm sorry. Autonumbers with automatically be included when you create the record from the rest of the fields.

 

[Colin@Toyota]

Ahh I see what you have done now... the "original" record is changed and saved, then the "new" record is inserted with the "original" values.

Now, to throw a wrench in it, how hard would it be to add a timestamp and username onto the "changed" record. Would it be possible to set the If criteria to me.one = var1 or etc..., and then insert the new values into the new record via !PartNumber = me.PartNum?

my mistake colin, i'm sorry. Autonumbers with automatically be included when you create the record from the rest of the fields.

Won't the part number be included as well through the parent/child relationship?

 

[ajetrumpet]

Ahh I see what you have done now... the "original" record is changed and saved, then the "new" record is inserted with the "original" values.

Now, to throw a wrench in it, how hard would it be to add a timestamp and username onto the "changed" record. Would it be possible to set the If criteria to me.one = var1 or etc..., and then insert the new values into the new record via !PartNumber = me.PartNum?

just add the new values to the record where you're writing it to the recordset. timestamp value would be TIME(), and the username you get from wherever you have that information stored in the database at that time.



Won't the part number be included as well through the parent/child relationship?[/QUOTE]

I would think that the above should be correct about the relationship Colin.

 

[Colin@Toyota]

Adam,

I'm gonna try this tomorrow, as I want to go home now. Thanks for all your help!

Here's the code I came up with:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim cntrl As Control

    strTimeStamp = Now()
    strUserID = Environ("UserName")
    
    If Me.Dirty = True Then
    
        For Each cntrl In Controls
            If cntrl.Value = cntrl.OldValue Then
                Next cntrl
            Else
                DoCmd.SetWarnings (False)
                Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
                With rs
                    .AddNew
                        ![Exchange Rate] = Me.Exchange_Rate
                        ![FOB] = Me.FOB
                        ![Misc] = Me.Misc
                        ![Freight] = Me.Freight
                        ![TMC Royalty] = Me.TMC_Royalty
                        ![Duty Rate] = Me.Duty_Rate
                        ![Tooling Upfront] = Me.Tooling_Upfront
                        ![Tooling] = Me.Tooling
                        ![Volume Forecast] = Me.Volume_Forecast
                        ![TCI Margin] = Me.TCI_Margin
                        ![Dealer Margin] = Me.Dealer_Margin
                        ![Timestamp] = strTimeStamp
                        ![UserID] = strUserID
                    .Update
                    .Close
                End With
                Set rs = Nothing
                DoCmd.SetWarnings (True)
                Exit Sub
            End If
          
End Sub

 

[ajetrumpet]

Colin, just one thing I think:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim cntrl As Control

    strTimeStamp = Now()
    strUserID = Environ("UserName")
    
    If Me.Dirty = True Then
    
        For Each cntrl In Controls
            If cntrl.Value = cntrl.OldValue Then
                Next cntrl
            Else
                DoCmd.SetWarnings (False)
                Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
                With rs
                    .AddNew
                        ![Exchange Rate] = Me.Exchange_Rate
                        ![FOB] = Me.FOB
                        ![Misc] = Me.Misc
                        ![Freight] = Me.Freight
                        ![TMC Royalty] = Me.TMC_Royalty
                        ![Duty Rate] = Me.Duty_Rate
                        ![Tooling Upfront] = Me.Tooling_Upfront
                        ![Tooling] = Me.Tooling
                        ![Volume Forecast] = Me.Volume_Forecast
                        ![TCI Margin] = Me.TCI_Margin
                        ![Dealer Margin] = Me.Dealer_Margin
                        ![Timestamp] = strTimeStamp
                        ![UserID] = strUserID
                    .Update
                    .Close
                End With
                Set rs = Nothing
                DoCmd.SetWarnings (True)
                Exit Sub
            End If
        [COLOR="Red"]NEXT CNTRL[/COLOR]
          
End Sub

the program will most likely give you a "for" without "next" error when you run this.

 

[gemma-the-husky]

this is quite tricky really when you think about it

without using loads of variables, one way would be to insert the current record into a temporary table, in the forms current event (maybe just a maketable query)

now if the record changes, you will get a form beforeupdate event, and a forms afterupdate event.

if you dont change the record, you can just ignore the temp record.

so i would probably just save the new change as normal, but use the after update to insert the record we just saved in the temp table.

this is especially useful if you save the history in a different table


then in the forms before

one way would be to i would have thought the way to do this, is

 

[DCrake]

Not to leave things out you may not actually require an audit of the revision if the changes made to the record did not matter as far as an audit trail is concerned. Lets say it is just a spelling correction or something similar. If you use the after update property of each field that would entail the need for a audit to occur you can set a boolean flag that would kick off the new record.

I had to do something similar

Private Sub TxtDateLeft_AfterUpdate()
  If Me.TxtDateLeft <> Me.TxtDateLeft.OldValue Then
      bFlag = True
      strChangesMade = "The Date left field was changed from " & Me.TxtDateLeft.OldValue & " to " & Me.TxtDateLeft & ". By " & strUserName & " on " & Date & " at " & time() & "."
   End If
End Sub

Now when the user saved the changes if bFlag was True it would create the new rercord in the Audit table saving the strChangesMade variable to a description field. This let me know what had changed. In your example it creates the new record but does not give any indication as to what actually changed. Unless I missed a bit.

When you complete the audit you reset the flag back to false.

David