On Dirty, save all changes as NEW record (1 Viewer)

[Colin@Toyota]

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.

I'm missing an End If in there too...

I will also need to set the fields in the form back to the values stored in the variables somehow so that Access doesn't automatically save the changes because the form is dirty... Would it be best to call the variables back to each of the text boxes? or could I use me.undo to remove the changes (as long as that doesn't negate the above!)

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.

In my case there is another form that shows the part information, with a subform in datasheet view that will list all the associated records from the pricing table... in essence, the first record in the subform will be the original pricing info, and the last will be the most recent, allowing the user to see all the changes over time.

 

[Colin@Toyota]

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

All the pricing data (current and historical) are in the same table...

The only issue with saving changes to the current record, is that I need to time/date stamp the record with the changes

 

[Colin@Toyota]

So in trying to run the code, Access is balking at the cntrl.value = cnrtl.oldvalue line... saying "object does't support this property or method"

hmm...

 

[gemma-the-husky]

time stamping is easy

just have fields for

lastchangedon (date)
lastchangedby (text)

in the forms beforeupdate event

lastchangedon = now()
lastchangedby = currentuser (or some other function)

 

[Colin@Toyota]

time stamping is easy

just have fields for

lastchangedon (date)
lastchangedby (text)

in the forms beforeupdate event

lastchangedon = now()
lastchangedby = currentuser (or some other function)

I've got that down, but if I need the timestamp to go with the "changed" record. I may have misunderstood your post, but I thought you were saying to save/move the unchanged record into a temp table, and save the changes to the existing record... which as I type this, no longer seems like it would be an issue, as the unchanged record would pull its old time/date and user stamp with it, and the changed record's time/date and user stamp could be overwritten.

 

[Colin@Toyota]

So I have the code sort of working... I tried it, forgetting to reset the fields to the original values, and it added a record with the changes, but changed the original record too. That's ok, I think I can handle fixing that.

For some reason, the subform then displayed the "changed" values of the first record for all the other parts, but after checking the table this was not the case.

Any ideas? Do I need to requery the form/subform after I run this code?

Thanks for all the help!!

Colin

 

[Colin@Toyota]

UPDATE

Everything to do with the records saving, etc is working, just now when I open the form and go through the part numbers, all the info displayed in the subform is the same as the first record. I think it has to do with the how I reset the fields in the before update event...

Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    strTimeStamp = Now()
    strUserID = Environ("UserName")
    
    If Me.Dirty = True Then
              
        If Me.FOB <> varFOB Or Me.Exchange_Rate <> varExRate Or Me.Misc <> varMisc Or Me.Freight <> varFreight Or Me.TMC_Royalty <> varTMCR & _
            Me.Duty_Rate <> varDuty Or Me.Tooling_Upfront <> varTooling Or Me.Tooling <> varToolCst Or Me.Volume_Forecast <> varVolume & _
            Me.TCI_Margin <> varTCI Or Me.Dealer_Margin <> varDlr Then
                
            DoCmd.SetWarnings (False)
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
            With rs
                .AddNew
                    ![PartNumber] = Me.PartNumber
                    ![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)
            
            Me.FOB = varFOB
            Me.Exchange_Rate = varExRate
            Me.Misc = varMisc
            Me.Freight = varFreight
            Me.TMC_Royalty = varTMCR
            Me.Duty_Rate = varDuty
            Me.Tooling_Upfront = varTooling
            Me.Tooling = varToolCst
            Me.Volume_Forecast = varVolume
            Me.TCI_Margin = varTCI
            Me.Dealer_Margin = varDlr
            
            Exit Sub
            
        Else
            
            Exit Sub
            
        End If
    
    End If
          
End Sub

 

[Colin@Toyota]

FINAL UPDATE:

Got it working.

Somehow, the parent/child link between form and subform was gone... replaced it, and it has worked since.

Also, I took out the one-by-one approach to revaluing the fields, and went with me.undo.

Thanks to everyone who helped!

Cheers,

Colin