moving a record from one table to another

[Liz_I3]

I am working with two tables with a one to many relationship.

I have used the 2 tables to create a form with a subform.

When a user marks a record for deletion useing a check boxe I want to copy the record includeing the data on the subform to 2 separate tables, that are exact copies of the original 2 tables the only difference is that these two table would only have deleted records. Then of course I would delete the records from the original two tables.

I am tring to keep the records marked for deletion in separate table idential to the original two and maintain the one to many relationship. Is this possible or do I have to create a query using the 2 original table and make one large table with the fields from both original tables to hold the deleted records?


Thanks
Liz

 

[Norie]

Why delete the records? You could add a Yes/No field that indicates that the records are no longer needed.

This could then be used in queries/forms etc to only view current records.

 

[Andrew Fergus]

Following on from Norie, I agree that it would be better / easier to retain the records in the original table and to flag them for exclusion. However, rather than create a Yes / No field, you could use the check box you already have in place and use this field to determine which records you no longer want to show on the forms and reports. This makes it far easier to undelete records later on.

However, if you really must transfer the records to 2 new tables then yes it can be done, and I would do it using 4 queries - one to append the main data across, another to append the subform data, and 2 queries to delete the old records from both tables. You can then wrap all of these queries up in a single macro and attach it to a button on one of your forms.

HTH, Andrew.

 

[Liz_I3]

Thanks
Norie and Andrew

I agree, I will take your advice and leave them in the 2 original tables.

Thanks again
Liz