having option to insert data from 1 table to multiple different tables

exporting data from table and deleting data in table after data exported:

Usually when I move data from 1 table to another and then have data deleted after exported- i will write something like:

Insert into tbl1
select tbl2.*
From tbl2;
//and to delete
Delete tbl2.[ser no], tbl2.[item number], etc...
From tbl2
Where (((tbl2.[ser num] IS NOT NULL)));
But now I am confused- I have a database with 25 tables and user wants capability to send data from any one of 25 tables to any one of other 25 tables-( also deleting data from table after sent)
Is there any short way of doing this?
I have only used command button ot send do it between 2 tables.
thank you
Who is Participating?
peter57rConnect With a Mentor Commented:
Assume that you have a form with two combo boxes cboTblFrom and cboTblTo each containing the listof tables and a button to run the transfer.

You have to create the sql statements and execute them in code.  You can put the code in the click event procedure of  the button.

sub yourbuttonName_Click()

dim strsql
if nz(forms!yourformname!cbotblTo,"") = "" or nz(forms!yourformname!cbotblFrom,"") = "" then ' not selected
msgbox "Select both tables first"
exit sub
end if

strsql = "Insert into " & forms!yourformname!cbotblTo & Select * from " & forms!yourformname!cbotblFrom
Currentdb.execute strsql, dbfailonerror

strsql = "Delete * from " &  forms!yourformname!cbotblFrom
Currentdb.execute strsql, dbfailonerror
End sub

Backup before testing.
davetoughAuthor Commented:
yes and all tables have same fields
davetoughAuthor Commented:
hello peter57r,
am attaching database sample - having trouble with select statement not sure why?
do you any reason for problem
thank you
Sorry, my copy and paste lost a ".
Change this line..

Strsql = "Insert into " & forms!frmExportTables!cboTblTo & " Select * from " & forms!frmExportTables!cboTblFrom
davetoughAuthor Commented:
thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.