Updating append queries? (1 Viewer)

[ddiver]

Hi all

I have a database that stores customer records and their associated insurance details. I need to be able to archive any changes made to that record. Currently the user selects 'record change' whenever anything is changed in the details. So I have created an append query for the appropriate tables and stored the details in a new version of the table called 'hsttablename'. This has worked well and I now have a table with all the changes in it.

But what I want to do now is automate the query(s) to run each time the user presses the submit button. Obviously rerunning the append query gives me an error of primary key violation. Would it be possible to get the append query just to update the new changes in one particular record when the submit button was pressed?

Any help would be greatly appreciated
DDiver

 

[Mile-O]

First, change the current autonumber of the archive table to a Number only. It will act as a foreign key rather than a primary key.
Then give the archive table a new field, make this an Autonumber. Make it the primary key.

Link the two tables together, enforce referential integrity, and cascade delete records.

 

[ddiver]

Thanks for the reply SJ!

I have done as you said and this has got rid of the primary key violation problem. But now when I run the append query it just adds *all* the records in again! I need to be able to make the query run and store just the update for the particular record I am working on? Any ideas? Many thanks!
DDiver

 

[neileg]

Unless you have huge numbers of records, why do you want to store them in a different table. You could leave them all in the same table and either flag the curent record or datestamp the records and use the most recent.

 

[ddiver]

Hi Neil

Thanks for your reply. I dont have a huge number of records but I need to store changes made to client's insurance details. How would I go about storing them in the same table as I currently have the client ID as PK in this table thus only one record per client would be allowed? I want to be able to store a series of changes whilst leaving the most recent visible in the DB and then report on the changes in a certain time period. Any help would be gratefully recieved.
DDiver

 

[neileg]

So change the PK to an autonumber instead.

 

[Pat Hartman]

Your append query doesn't have any selection criteria. You need to tell it which record you want to archive. Add a where clause:

Where SomeField = Forms!YourForm!SomeField;