Copy form data to new table

[scouser]

Hi. I have a table 'tblComputers' from which I have created a query then a form 'frmComputers'. I want to create a new table that contains identical fields 'tblScrapComputers' then add a button to 'frmComputers' that copies data from 'tblComputers' to 'tblScrapComputers' then deletes the record from tblComputers.

Can anyone help on this one?
Thanks,
phil.

 

[MarkK]

What I'd do is add a 'status' field to tblComputers. Then when a computer dies, all you have to do is change its status to 'scrap' and arrange the funeral.

 

[scouser]

reply

What I'd do is add a 'status' field to tblComputers. Then when a computer dies, all you have to do is change its status to 'scrap' and arrange the funeral.

Hi. I currently have that set-up. However software etc.... is allocated to each computer. If i simply flag the computer as 'scrap' it does not delete related records thus free up licences. If I delete the computer object then the deletion will cascade to all related tables. Hope I am making sense?
Thanks,
Phil.

 

[MarkK]

It makes zero sense to me to maintain mutiple tables with exactly the same structure. Program the 'Status_AfterUpdate' event to do the work you need done when you change the status.

 

[scouser]

Duplication

I agree regards the duplication. However it seemed the easiest solution! tblComputers is related to many other tables..........computers are allocated to users, hardware is allocated to computers, software is allocated to users, computers are allococated to network ports etc......the afterupdate could prove tricky?
Regards,
Phil.

 

[MarkK]

Given your description of the relationships it seems to me that moving the record would be more difficult.
Also, what appears the easiest solution in the short term is rarely that when all things are considered.
But it's your call. My opinion is that creating a second table for items that already have a table is a make work project. On the other hand, I'm not religious about it. Every action has it's own costs and payoffs and you're obviously in better position to see all the implications.
Consider though that once you move the record and a user wants to find it, and this will happen, she'll have to look in two places, and you'll have to write that possibility into your system. This is the kind of duplication of effort you want to avoid unless you have a pretty good reason for it.
Cheers,

 

[scouser]

Duplication

I am the only user. tblScrapComputers would just be a holding table.
Phil.

 

[GaryPanic]

check the samples - some clever spark has already done this ..
must of been about 6 months ago ..
g

 

[scouser]

Sample

Thanks for the heads up, I will have a search.
Phil

 

[WhyteRhyno]

Can't find a Sample, and would like to know the answer

Regardless of all the discussion about whether or not this SHOULD be done, I would really like to know how it CAN be done. I have a similar situation as the original poster, and I really do want to move records from the original table to an "archive" table, as my company's policies dictate. So if anyone can answer the original question without telling me why I should not be doing it this way, it would be very greatly appreciated.

I couldn't find the sample database referenced earlier in this thread. Anyone know the name of it?

Thanks,
WhyteRhyno

 

[scouser]

Code

Guys here is what I have:

I copied tblComputers as tblScrapComputers. I added 2 additional columns to tblScrapComputers:
date_scrapped & reason_scrapped. I then created relationship ComputerID-ComputerID (did not enforce I referential integrity).

Added a button to frmComputers then the following code to the 'on_click' event:

Private Sub cmdScrap_Click()
If MsgBox("Are you sure you wish to scrap this PC", vbYesNo, "Scrap PC?") = VbMsgBoxResult.vbYes Then
        Dim scrapReason As String
        scrapReason = InputBox("What is the reason for the scrap?", "Reason for scrap", "NEW PC")
        
        DoCmd.SetWarnings False
        
        DoCmd.RunSQL ("INSERT INTO tblScrapComputers ([ComputerId],[ComputerMakeID],[ComputerModelID],[SerialNumber]," _
        & "[ServiceTag],[OperatingSystemID],[OSLicenseKey],[RAM],[Processor],[DatePurchased],[WarrantyPeriod]," _
        & "[Notes],[date_scrapped],[reason_scrapped])" _
        & "VALUES('" & [ComputerId] & "','" & [ComputerMakeID] & "','" & [ComputerModelID] & "','" & [SerialNumber] _
        & "','" & [ServiceTag] & "','" & [OperatingSystemID] & "','" & [OSLicenseKey] & "','" & [RAM] _
        & "','" & [Processor] & "','" & [DatePurchased] & "','" & [WarrantyPeriod] & "','" & [Notes] & "','" & Date & "','" & scrapReason & "')")
        
        DoCmd.RunSQL ("DELETE FROM tblComputers WHERE [ComputerId] = '" & [ComputerId] & "'")
        
        Me.Requery
        
        DoCmd.SetWarnings True
         
        
    Else
        MsgBox ("no scrap")
    End If
End Sub

Obviously rename/delete/create fields where appropriate.

Regards,
Phil.

Note: Thanks to Matthew Bird for above code.

 

[MarkK]

Since your tables are identical, you might be able to simplify that SQL using something like...

CurrentDb.Execute _
  "INSERT INTO tblScrapComputer " & _
  "SELECT * FROM tblComputer " & _
  "WHERE ComputerID " & Me.ComputerID

Then do the date and reason...

CurrentDb.Execute _
  "UPDATE tblScrapComputer " & _
  "SET ScrapDate = #" & now() & "#, " & _
  "ScrapReason = '" & ScrapReason & "' " & _
  "WHERE ComputerID = " & Me.ComputerID

And then do your delete. Also, using Database.Execute doesn't show warnings so you don't have to turn them off and then back on.
Cheers,

 

[WhyteRhyno]

SQL commands work beautifully

Hey Scouser, I was just coming back to the forum to tell you I found the way to do this, which you've already found. In general, I'm discovering there's a lot you can do running various SQL action commands from within Access.

Thanks for verifying my findings, and thanks to Lagbolt too!