Desparately Confused with Append Query.

[Bickertk]

I have a database in which I have four linked tables by vehicle ID.

E.G The first table has basic vehicle data when input the database gives each vehicle an ID number which I use as a primary key.

Other data related to the vehicle such as accident information on loan details and such like are kept in other tables. The vehicles unique ID set in table one is used to link all the information.

In table one a box indicates whether or not this is a current vehicle.

I have then created History tables for each table and what I want to do is move all the data into them when the box indicates this is not a current vehicle. To do this I have done the following.

Made the tables one to many and enforced referential integeraty and clicked cascade delete. Then made Append queries for each table

THE PROBLEM

I have attached a button on my screen and linked the query from the main table when pressed it says appending 2 records which cannot be undone. However when I press OK I get the following message and it does not move the records.

"Fleetmanagement Database set 0 field to null due to a type conversion failure and didn't add 1 record to the table due to key validations, 0 records due to lock violations and 0 record due to validation rule violations."

Can someone explain what this means to me or see where I am going wrong and set me on the right track.

I'd appreciate the help as this in urgent for me to get done.

Cheers

 

[KenReay]

Hi

Well it means it cannot execuute your query becuase to do so would cause it to break one of the rules realting to keys, within your database design:

It could be that the table(s) you are trying to append to already conatin a record with same primary key (do you for example already have the vehicle in the history table, due perhaps to earlier testing, and you are trying to add it again?)

or

It could be that you are trying to break one of the referential integrity rules (eg 'child' tables must have arow in 'master' table with relevant primary key), do yoy by chance have the same referential integrity rules in your history table as in you live tables, and are you trying to add the 'child' information, before you have added the 'master' information?

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[nberryman]

Instead of moving the record why not hide them. Add a Yes/No field called Deleted to the vehicles table and alter your queries to only show records wher deleted is No.

Depending on the size of you database you could then append the records when required.
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com