Results 1 to 6 of 6

Thread: move command

  1. #1
    Join Date
    Dec 2002
    Location
    philippines
    Posts
    2

    Exclamation Unanswered: move command

    just want to know how to move record from table1 to table2?

    this is the scenario...

    i have a datagrid1 ill choose a record to delete then click delete button to delete the record in table1...once the records deleted in table1 that record should be move to table2 so that i can view all the deleted record to datagrid2...

    how can i do thaT?? can u pls tell me some codes for it? thanks....

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You need to run 2 queries. An append query and a delete query.

    INSERT INTO [Table2]
    SELECT Table1.*
    FROM Table1;


    DELETE Table1.*
    FROM Table1;


    Make sure that you run the append query before the delete query!


    David

  3. #3
    Join Date
    Dec 2002
    Location
    philippines
    Posts
    2
    is this code applicable to visual basic??...


    Originally posted by DJN
    You need to run 2 queries. An append query and a delete query.

    INSERT INTO [Table2]
    SELECT Table1.*
    FROM Table1;


    DELETE Table1.*
    FROM Table1;


    Make sure that you run the append query before the delete query!


    David

  4. #4
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Yes, you can run it as SQL statements.

    Private Sub cmdSQLMethod_Click()

    Dim strSQL As String

    strSQL = "INSERT INTO table2 "
    strSQL = strSQL & "SELECT table1.* FROM [table1];"
    DoCmd.RunSQL strSQL


    strSQL = "DELETE table1.*"
    strSQL = strSQL & "FROM [table1]"
    DoCmd.RunSQL strSQL
    End Sub

    David

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i've a question why does everyone keep assigning a one off string to a variable,

    ie
    Dim strSQL As String

    strSQL = "INSERT INTO table2 "
    strSQL = strSQL & "SELECT table1.* FROM [table1];"
    DoCmd.RunSQL strSQL

    instead off

    DoCmd.RunSQL "INSERT INTO table2 " & _
    "SELECT table1.* FROM [table1];"

    i just cant see the point
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    and on the point of the post, why bother just add an extra field called valid of type yes/no and instead of deleting just toggle it's value, keeps all the data together and doesn't create the problem that you only have to worry about backing up onw table. Also you don't need to worry about sql failors ie insert fails and record isn't archived before deleting (tends to only happen if you use resume next) or delete fails and you've still got the orignal meaning you then get problems over primary keys in the deleted table

    the method i tend to use is a byte variable as this allows for multiple states ie 0 = invalidated, 1 = Waiting, 2 = Complete, 3 =archived, etc.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •