Appending records into history table of changes made to customer info (1 Viewer)

[BlueSpruce]

Every time users update any information about a customer, in the form's after update event I am appending a copy of the customer record with the date/time the change was made, and the user who made the change to a customer history table. However, when a new customer is first added, it's also appending that record to the history table. What is the best way to prevent that initial record from being added to the history table?

Private Sub Form_AfterUpdate()

   On Error GoTo locErrorHandler
   cmdSave.Transparent = True
   cmdUndo.Transparent = True

   CurrentDb.Execute "qryCustomerHistory_AppendAfterChange", dbSeeChanges
   lstCustomerHistorySIID.Requery
   lstCustomerHistorySIID = lstCustomerHistorySIID.ItemData(0)
   DisplayCaption_HistoryTab
   txtDisplayCustomer.Requery

locExitHere:
   Exit Sub

locErrorHandler:
   ErrorHandler Me.Name, "Form_AfterUpdate"
   If gErrorResponse = 1 Then Resume
   If gErrorResponse = 2 Then Resume Next
   Resume locExitHere
End Sub

 

[MarkK]

Without claiming this is the best way...

Private isNew_ As Boolean

Private Sub Form_Current()
    isNew_ = Me.NewRecord
End Sub

Private Sub Form_AfterUpdate()
    If isNew_ Then Exit Sub
    ....
End Sub

 

[arnelgp]

you can also use the the Form's BeforeInsert event to Set a Variable to true.
then Reset it on the Form's AfterUpdate

 

[XPS35]

Why don't you store a new record? Now you are missing the initial values in the history.

 

[BlueSpruce]

Without claiming this is the best way...

Private isNew_ As Boolean

Private Sub Form_Current()
    isNew_ = Me.NewRecord
End Sub

Private Sub Form_AfterUpdate()
    If isNew_ Then Exit Sub
    ....
End Sub

you can also use the the Form's BeforeInsert event to Set a Variable to true.
then Reset it on the Form's AfterUpdate

XPS35 Said: Why don't you store a new record? Now you are missing the initial values in the history.

Agreed, I was thinking along those lines. The history form is highlighting changes made to any values from the previous record, so how can I compare against the original record when the customer was first added if I don't include the original record in the history table?

Private Sub Form_Current()

   Dim PriorSIID As Integer
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim ctl As Control
 
   If Parent.lstCustomerHistorySIID.ListIndex = Parent.lstCustomerHistorySIID.ListCount - 1 Then
      PriorSIID = Parent.lstCustomerHistorySIID
   Else
      PriorSIID = Parent.lstCustomerHistorySIID.ItemData(Parent.lstCustomerHistorySIID.ListIndex + 1)
   End If
 
   strSQL = "SELECT * FROM tblCustomerHistory WHERE CustomerHistorySIID=" & PriorSIID
   Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
 
   For Each ctl In Me
      Select Case ctl.ControlType
         Case acTextBox, acComboBox
            If ctl & "" = rs(ctl.ControlSource) & "" Then
               ctl.BackColor = vbWhite
            Else
               ctl.BackColor = vbYellow
            End If
      End Select
   Next

End Sub