Moving row to another table

[bigmacholmes]

I have a database that has a completeddate field on each row.


When the row is initially filled, the completedate will not be until the project is completed.
After this field is updated or filled, i want to move the entire row to another table automatically.

Is there a way to do this?

[pbaldy]

As a general rule you shouldn't move records around. I'd leave it there and let the presence of a date there let me query for completed, uncompleted or both. If you really want to move it, you'd use an append query and then a delete query, each using that field for a criteria.

[HawkGuru]

bigmacholmes,
I would tend to agree with Pbaldy on this one. Moving records around can lead to later problems. However, there are times when this may be a necessity. I created an employee table and an employee archive table to illustrate how this is accomplished. The SQL for the query would be:

INSERT INTO EmployeeArchiveT ( Employee, Email, DOB, CityOfBirth, StateOfBirth, Citizenship, NOKName, NOKPhoneNumber, NOKAlternatePhoneNumber, NOKRelationship, NOKLanguageSpoken )
SELECT EmployeeT.Employee, EmployeeT.Email, EmployeeT.DOB, EmployeeT.CityOfBirth, EmployeeT.StateOfBirth, EmployeeT.Citizenship, EmployeeT.NOKName, EmployeeT.NOKPhoneNumber, EmployeeT.NOKAlternatePhoneNumber, EmployeeT.NOKRelationship, EmployeeT.NOKLanguageSpoken
FROM EmployeeT
WHERE (((EmployeeT.Employee)=[Forms]![EmployeeInfoForm]![Employee]));

You could set up a macro to first run the query and then perform the delete action for the record on the old table.