Move specific record to another table Access 2003

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.
LVL 7
jesajaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Arthur_WoodConnect With a Mentor Commented:
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
 
GRayLConnect With a Mentor Commented:
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
 
GRayLCommented:
Reading it again, do you expect to perform this action by opening the table directly, or are you doing it from a form?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jesajaAuthor Commented:
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
 
GRayLCommented:
Thanks, glad to help.
0
 
Arthur_WoodCommented:
Glad to be of assistance

AW
0
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.