How Do I Flag records that have been changed?

[Terry DeJournett]

I have 5 tables. In each table is a field named "Code". I need to have a
"1" inserted into this field if any record is changed in the table, and a
"2" inserted if any new records are added to the table.

Could anyone guide me in the right direction to do this, or can it be done?

Thank you very much.

 

[Phil Stanton]

A Changed record is difficult to define. When you add a new record and say
fill in a name, you have changed the "Blank Record" to a record with detail

I have 4 additional fields in my table (Rather than your 1 "Code")
Entered Date/Time
EnteredBy Text
Updated Date/Time
UpdatedBy Text

Private Sub Form_AfterInsert()

Entered = Now
EnteredBy = CurrentUser()

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Updated = Now
UpdatedBy = CurrentUser()

End Sub

Then I have the above bits of code on AfterInsert (When the record is first
created)
and AfterUpdate which records all changes

Probably more informative than what you are suggesting as there are likely
to be many changes to a record

HTH

Phil

 

[Dirk Goldgar]

I have 5 tables. In each table is a field named "Code". I need to
have a "1" inserted into this field if any record is changed in the
table, and a "2" inserted if any new records are added to the table.

Could anyone guide me in the right direction to do this, or can it be
done?

Thank you very much.

In the form's BeforeUpdate event, you can set the field using code like
this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!Code = 2
Else
Me!Code = 1
End If

End Sub

However, this scheme could be messed up if the user creates a new
record, saves it -- which could happen automatically if the user enters
data on a subform -- and then edits it again. If that's not a problem,
the above code is all you need. If it is a problem, though, you can use
a module-level flag variable to catch the fact that you started a new
record, like this:


Dim mfNewRecord As Boolean ' at module level


Private Sub Form_Current()

mfNewRecord = Me.NewRecord

End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)

If mfNewRecord Then
Me!Code = 2
Else
Me!Code = 1
End If

End Sub