Moving record to another table

[bseche]

I need help,
I have a table that keeps track of the library books we have. I want the user to be able to click on a button to take the record and move it to another table ( They thinking they deleting it but they actually taking it out the main table and moving it to another table witch is only accessible to admin users . Similar to append query. I don't want it to prompt the user for nothing just take the record they selected and move it.

 

[GaryC]

Your requirement sounds similar to an Archive problem I had. I wanted to 'save' any 'deleted' records into an Archive table for 'Audit' purposes.

I wrote a piece of code that used the "On Delete" event to save the record to a temporary table - prior to the database asking the usual question "Are you sure you want to delete this record?". I then had to test whether they said 'Y' or 'N'. If they say No access puts the record back in the table. (It uses its own buffers to store the record). My code writes to an external database table - but you can amend the syntax to write to another table in the same db. Try looking at "RunSQL" in help !

My code was similar to this (i have simplified):-

Private Sub Form_Delete(Cancel As Integer)
'Need to capture data at this point for archiving
lngDeleted_ID = Me![ID]

strMySQL = "Select [Name].* INTO [Temp Name Tbl] FROM [Name] " _
& "WHERE [Name ID] = " & lngDeleted_ID
DoCmd.SetWarnings False ' supress warning message during SQL write to archive
DoCmd.RunSQL strMySQL
DoCmd.SetWarnings True ' restore warning messages
End sub

Private Sub Form_AfterDelConfirm(Status As Integer)
'after delete confirmation - need to complete writing buffered record to archive
If Status = 0 Then ' cancel = false - user is deleting records
'copy contents of temp table to external archive database

dim x as long
dim strMyArchive as string

strMyArchive = "c:\arch\NameArch.mdb"
x = DCount("[Name]", "[MSysObjects]", "[Name] = 'temp Name tbl'")
If x > 0 Then
strMySQL = "INSERT INTO [Name Tbl] IN " & strMyArchive & "SELECT * FROM [temp Name]"
DoCmd.SetWarnings False
DoCmd.RunSQL strMySQL
DoCmd.SetWarnings True
End If
End Sub

HTH

 

[Chris RR]

See replies under your other posts. Please do not put out multiple posts on the same question.