How to move records from one table to another

[e151280]

Greets, all.....

i'm a casual access user, who has wandered into waters that are too deep.

have 2 tables (table_a & table_b)related by a common id number... no referential integrity hassles because the tables already contain orphaned data that i have to keep.

what i'd like to do is this:

from the form that displays the data (displayform), create a button that will archive the selected record from table_a, and any related child records from table_b to a third & fourth table (arc_table_a, and arc_table_b). I'd then like to delete the now archived records from table_a & table_b. I have been trying to do this with an update query, with no success, and then i started looking for a way to do it programmatically. I also was wondering if SQL statements would work.

ex:

the open form has a field named 'id' that corresponds to the field names in the active and archive tables

select * from table_a where table_a.id = displayform.id
select * from table_b where table_b.id = displayform.id

am i headed in the right direction?

any assistence is appreciated.

 

[fneily]

You could create a macro that would be placed on the OnClick event of the button that runs some queries(OpenQuery action). The first would be an append query from table_a to arc_table_a (criteria would be [forms]![Formname]![table_aID]). Then the next would be a delete query using the same criteria on table_a. Then the third and fourth would do the same for table_b to arc_table_B and then delete again from table_b.
You would start your macro with Echo and Setwarnings equal to NO. Then at the end, reset them back to YES. Any book can show you how to create the append and delete queries.

 

[TheAceMan1]

How are ya e151280 . . .

By any chance are the id's [blue]autonumber![/blue]

See Ya! . . . . . .

 

[e151280]

The ID field is not autonumber... just an ordinary text field. it is unique in table_a, however, with multiple matching child records in table_b. same situation in arc_table_a and arc_table_b.

 

[TheAceMan1]

OK e151280 . . .

Hold the ID in a variable then:
[ol][li]Append the record from table_a to arc_table_a with an [blue]Append[/blue] query.[/li]
[li]Next append the records from table_b to arc_table_b with an [blue]Append[/blue] query.[/li]
[li]Repeat the above (in reverse order) using a [blue]Delete[/blue] query.[/li]
[li]Requery the form.[/li][/ol]

See Ya! . . . . . .

 

[fneily]

My post was deja vu.