Moving Records from tables to table via one Button!

[chadycom]

Hello,

It is great to find such a place where experts can help beginners like me :)

I am attaching a sample DB file that has a Form with 2 sub forms linked to 2 tables and one query.
The records in the Projects_List and Details tables are linked via a reference number. These are projects I am working or also known as funnel form.

I need to add a kind of combo button having 4 selections; Active, Ordered, Lost, and Cancelled.

What I need is keep the default selection on Active. But let's say I win the deal, I need to select Ordered and once selected, I need the application to automatically:
1- Move the records from the current Projects_List and Details tables into the new ones called Deal and Deal Details tables respectively
2- The Bucket Field will change to the new Field called Status and the Status is updated to Ordered.

If I select Lost then the same this happen but the Status field will also replace the Bucket Field with the Lost value and so on for the rest

I hope I was able to explain enough as I seek your kind help

Thank you very much

[rconverse]

You can create an append query from to move the data from one table to the other and then a delete query to remove the records from the old table (that have now been moved to the new tables). You could call this query from the "AfterUpdate" event of the combo box you'll create with Active, Open, Lost...

For that, I would create a new table with the statuses.

If you need more specific help, just post back.

HTH
Roger