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

Posted on 2011-05-09
Last Modified: 2012-06-27
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
Question by:davetough

    Author Comment

    yes and all tables have same fields
    LVL 77

    Accepted Solution

    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.

    Author Comment

    hello peter57r,
    am attaching database sample - having trouble with select statement not sure why?
    do you any reason for problem
    thank you
    LVL 77

    Expert Comment

    Sorry, my copy and paste lost a ".
    Change this line..

    Strsql = "Insert into " & forms!frmExportTables!cboTblTo & " Select * from " & forms!frmExportTables!cboTblFrom

    Author Comment

    thank you

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now