Solved

Move specific record to another table Access 2003

Posted on 2008-06-24
6
596 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
ID: 21856321
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
ID: 21857549
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
ID: 21857562
Reading it again, do you expect to perform this action by opening the table directly, or are you doing it from a form?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 7

Author Comment

by:jesaja
ID: 21860297
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
ID: 21860376
Thanks, glad to help.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 21862467
Glad to be of assistance

AW
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 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

15 Experts available now in Live!

Get 1:1 Help Now