Solved

Move specific record to another table Access 2003

Posted on 2008-06-24
6
595 Views
Last Modified: 2008-07-03
I like to move a complete specific selected record in a form to table for archiving.

original table is called tbl_hw and archiving table is called tbl_hw_archive.
Both tbl_hw and tbl_hw_archive have the same filds.

How can I select on record and add this record including all filds to the other table and after this is done delete this record in the original table.

Best regards.
0
Comment
Question by:jesaja
  • 3
  • 2
6 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 250 total points
Comment Utility
the SQL would look like this:

INSERT INTO tbl_hw_archive Select * from tbl_hw where tbl_hw.<PrimaryKeyField> = SomeValue


Not knowing what the fields are in your tables, you would replace .<PrimaryKeyField> with the name of the field in the table that is the PrimaryKey of that table, and replace SomeValue with the Primary_Key_Value of the record that you wish to copy to the archive table.

AW
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
Comment Utility
You cannot INSERT and DELETE from the same SQL.  Once you are satisfied that the record has indeed been inserted in the archive table, run a DELETE query on the original:

DELETE * from tbl_hw WHERE tbl_hw.pk = SomeValue
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Reading it again, do you expect to perform this action by opening the table directly, or are you doing it from a form?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Author Comment

by:jesaja
Comment Utility
Both thanks for your advice forgot about using SQL in a form.

I did this:


Me.Painting = False

    DoCmd.Hourglass True

    DoCmd.SetWarnings False

    

    If (MsgBox("Wollen Sie den aktuellen Datensatz: " & Chr(34) _

            & Me![Temp_ID_Hardware] & " - " & Me![Temp_Model_Name] & Chr(34) & _

        " archivieren?", vbExclamation + vbYesNo + vbDefaultButton2) = 6) Then

       

        DoCmd.RunSQL "INSERT INTO tbl_hw_archive Select * from tbl_hw where tbl_hw.ID_Hardware = " & Me![Temp_ID_Hardware] & ""

        

        If (MsgBox("ACHTUNG!" & Chr(10) & "Wollen Sie den aktuellen Datensatz: " & Chr(34) _

            & Me![Temp_ID_Hardware] & " - " & Me![Temp_Model_Name] & Chr(34) & _

        " löschen?", vbCritical + vbYesNo + vbDefaultButton2) = 6) Then

            

         DoCmd.RunSQL "DELETE * from tbl_hw WHERE tbl_hw.ID_Hardware = " & Me![Temp_ID_Hardware] & ""

        

        End If

    

    End If

               

    DoCmd.Hourglass False

    Me.Painting = True

    DoCmd.SetWarnings True

    

    If Err = 0 Then

        MsgBox ("Der Datensatz wurde ordnungsgemäss archiviert und von der Haupttabelle gelöscht!"), vbInformation, AppName

        Me.Refresh

    End If

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Thanks, glad to help.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
Glad to be of assistance

AW
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 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

10 Experts available now in Live!

Get 1:1 Help Now