Create a "history" for a field on a a table

[miked]

When I enter a plate # into one field, I need that number to be recorded into
another field, and saved. That way all of the previous plate #'s are saved,
and I can change the operational plate #. Any ideas? I have a table that has
all of my criteria in it. ie. Name address, plate #, and previous plate #'s.
I set up a form to record the information easily, and I have a report that
displays all of the entered information. What I would like to happen is every
time I enter a plate number into the plate# field, I want that plate number
to appear in the previous plate # field. Then when I delete the origional
Plate #, I want that number to stay in the previous plate # field. That way
I can have a "History" of the plate numbers, and the current plate number
used.

 

[John Vinson]

When I enter a plate # into one field, I need that number to be recorded into
another field, and saved. That way all of the previous plate #'s are saved,
and I can change the operational plate #. Any ideas? I have a table that has
all of my criteria in it. ie. Name address, plate #, and previous plate #'s.
I set up a form to record the information easily, and I have a report that
displays all of the entered information. What I would like to happen is every
time I enter a plate number into the plate# field, I want that plate number
to appear in the previous plate # field. Then when I delete the origional
Plate #, I want that number to stay in the previous plate # field. That way
I can have a "History" of the plate numbers, and the current plate number
used.

You will need a HISTORY TABLE. You should not use another field in
your current table; that would (at best) keep only one level of
history.

Use a Form for your master table, and a Subform based on the plate
history table. You can use VBA code in the BeforeUpdate event of the
plate# textbox to add a new record to the related table. Something
like:

Private Sub txtPlateNo_BeforeUpdate(Cancel as Integer)
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String

'create an Append query to add a record to the history table
strSQL = "INSERT INTO History([PlateNo]) Values('" & _
Me!txtPlateNldValue & "')"
Set db = CurrentDb
' Create a temporary query; "" means don't store it in Queries
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Me!subHistory.Requery
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps