Auto archive old data on update in MS Access?

[goldriverdancer]

Dear all,

I've thus far created a simple Access database to keep track of all the pumps my (mining) company uses.

What I want to do, is create a form which will automatically archive previous data if new data is written into it. For example, I have a pump that is operating from the 21st of April. Then, it fails on the first of May. I would like to allow a user to enter new data into the form (change pump status from operating to failed and the dates) to display the current status, but not lose previous information by storing it in another table.

Then, if anyone should want to call up the history of a particular pump, they can just look it up in the archive table, and all its past failures and startups can be found.

Can someone help me with this? I don't have much time...

Thank you very much in advance,

Stef the Noob

[DenisOxon]

Hi,

I'm not what your problem is, but it does depend on what tables you have and how they are linked. Maybe you could let us know what tables you have together with the links please ?

Then it should be simple to sort.

Denis

Dear all,

I've thus far created a simple Access database to keep track of all the pumps my (mining) company uses.

What I want to do, is create a form which will automatically archive previous data if new data is written into it. For example, I have a pump that is operating from the 21st of April. Then, it fails on the first of May. I would like to allow a user to enter new data into the form (change pump status from operating to failed and the dates) to display the current status, but not lose previous information by storing it in another table.

Then, if anyone should want to call up the history of a particular pump, they can just look it up in the archive table, and all its past failures and startups can be found.

Can someone help me with this? I don't have much time...

Thank you very much in advance,

Stef the Noob

[ChrisPadgham]

Create a second table with columns for the variable data and an extra field/column effectivedatetime, note in this table PumpId is NOT the primarykey since there will be multiple records for each pumpid.

In the Before_Update & After_Insret events of the form place a piece of SQL to write the record into this table eg

currentdb.execute "INSERT INTO PumpHistory (PumpId, EffectiveDateTime, PumpStatus) VALUES (" & me.pumpid & ", Now, '" & me.pumpstatus & "')

[goldriverdancer]

Create a second table with columns for the variable data and an extra field/column effectivedatetime, note in this table PumpId is NOT the primarykey since there will be multiple records for each pumpid.

In the Before_Update & After_Insret events of the form place a piece of SQL to write the record into this table eg

currentdb.execute "INSERT INTO PumpHistory (PumpId, EffectiveDateTime, PumpStatus) VALUES (" & me.pumpid & ", Now, '" & me.pumpstatus & "')

Thank you all for the input. I really appreciate it. Actually, I was advised not to auto archive because it would just create heaps of problems. So, i got around the problem by creating a separate table, and showing that data in a subform on the main form. I will keep this in mind for the future though. =)

Stef