Move a record from one table to another table.

[botts121]

I have a database that I am looking to be able to move one record from a table to another table with a click of a button. But moving the record from the one table to another table depends on certain values that is selected in one of the drop down boxes. So I am stuck as to how to do this with the (I'm guessing) VBA code. So if anyone can help, I would greatly appreciate it.



Thanks in advance,

Matt

[RuralGuy]

Not necessarily VBA code but at least two queries; one query to select and append the record to another table and another query to delete the original record.

[botts121]

but wouldn't you still need some sort of code to make it work? Like code for in the button itself?

[RuralGuy]

I would use code but you can get Macros to run queries if you want. No error checking of course but it works most of the time.

[pdouglas]

I would make sure the table I am copying stuff be the same as what I am copying from. If you have a field that is linked to another table, keep the link. If you have a field that is calculated, keep that. ....
Easiest way would be to Right click on the table you are copying, select "Copy", then right click, Paste, and name the new table. Select the Stucture only (don't copy data). Then delete any fields you don't need. This will keep your relationships intact.
This way, if your county field is numeric and is linked to another table with County Information, it will know that "19" means "Los Angeles" and displays accordingly.

Still, you would need two queries, one to append from old to new and a second to delete based on whatever criteria you select. I would suggest making the two run seperately so that you could verify the data was copied before deleting it. (Not just run 1 macro with them "Whoomp, Bang".)

Good Luck
Paul