looking for quick method of archiving a single record

[Guest]

I am looking for a simple way to "Move" a record from my "Orders" table to my
"UnCommisioned" table. I would like to add a command button to my Edit
Project form that performs the move. The two tables are identicle except the
auto numbering formula I use has been removed from the "Uncommisioned" table.
In a nut-shell, if a customer decides not to commision a project, I want to
remove the entire recods set to an archive table (in case they change thier
mind) for later use.

 

[UpRider]

Jack, here's what I do. This assumes referential integrity with cascading
deletes.
It also assumes tblOrders and tblUnCommissioned are exactly the same
structure with identical field names.
You may have to fiddle with this to match them up as you indicated that the
autonumber is missing in one table.

You will need to adjust the WHERE sql clauses to match your form.
You may also need to change the original detail.backcolor. Mine is 16777088.

HTH, UpRider

Private Sub cmdDelete_Click()
dim intMsgBox as Integer
Dim strSQL As String
On Error GoTo err_cmdDelete_Click
Me.Detail.BackColor = vbRed
intMsgBox = MsgBox("Here's what happens when you delete an order:" &
vbCrLf _
& "1. The order is deleted from this screen." & vbCrLf _
& "2. Any payments against this order are deleted." & vbCrLf _
& "3. This invoice number will be added to the Deleted Invoice
Report, " _
& " along with your explanation in the Notes box.", _
vbOKCancel + vbCritical, "D E L E T E T H I S O R D E R")
If intMsgBox = vbCancel Then
Me.Detail.BackColor = 16777088
Exit Sub
End If
Dim db As dao.Database
Set db = CurrentDb
'copy the record from tblOrders to tblUnCommissioned
strSQL = "INSERT INTO tblUnCommissioned " _
& "Select * from tblOrders " _
& "WHERE (((tblOrders.InvoiceNo)=" &
[Forms]![frmOrders]![txtInvoiceNo] & "));"
db.Execute strSQL, dbFailOnError
'delete the record in tblOrders
strSQL = "DELETE tblOrders.InvoiceNo FROM tblOrders " _
& "WHERE (((tblOrders.InvoiceNo)= " &
[Forms]![frmOrders]![txtInvoiceNo] & "));"
db.Execute strSQL, dbFailOnError
Set db = Nothing
Me.Requery
err_cmdDelete_Click_Exit:
Me.Detail.BackColor = 16777088
DoCmd.GoToRecord , , acFirst
Exit Sub
err_cmdDelete_Click:
If Err = 3200 Then
MsgBox "This invoice has payments posted. If you wish to delete the
order, " _
& "you must delete all the payments first.", vbOKOnly +
vbCritical, _
"U N A B L E T O D E L E T E"
GoTo err_cmdDelete_Click_Exit
End If
' If Err = 2046 Then
' GoTo err_cmdDelete_Click_Exit
' End If
MsgBox "Delete Error " & Err & Err.Description, vbOKOnly, "D E L E T E
E R R O R"
GoTo err_cmdDelete_Click_Exit
End Sub

 

[Chaim]

Better idea (if you can do it): Add a column to your Orders table named
'Commissioned'. Make it a Yes/No field. If the project is killed, set this
field to No (meaning it has been uncommissioned). If the project is
active/resurrected, set the field to Yes.

Much cleaner and less risky than moving records from one table to another.
No need to delete records. One step vs. two.

Ditch the archive concept (again, if you can).

Good Luck!

 

[Joseph Meehan]

I am looking for a simple way to "Move" a record from my "Orders"
table to my "UnCommisioned" table. I would like to add a command
button to my Edit Project form that performs the move. The two
tables are identicle except the auto numbering formula I use has been
removed from the "Uncommisioned" table. In a nut-shell, if a customer
decides not to commision a project, I want to remove the entire
recods set to an archive table (in case they change thier mind) for
later use.

I suggest putting them all back into the original table.

Add an "Archive" field. Make it a simple yes no field and then filter
most of your queries and or forms and reports but archive = no.

 

[John Vinson]

I am looking for a simple way to "Move" a record from my "Orders" table to my
"UnCommisioned" table. I would like to add a command button to my Edit
Project form that performs the move. The two tables are identicle except the
auto numbering formula I use has been removed from the "Uncommisioned" table.
In a nut-shell, if a customer decides not to commision a project, I want to
remove the entire recods set to an archive table (in case they change thier
mind) for later use.

I'd consider a different, simpler approach. Add a Yes/No field
"Archived" to your table; base your (usual) forms on a Query with a
criterion of = False on this field. To "archive" a client just check
the box. Their data will still be stored, and can be restored to view
by simply updating this field back to False.

If you do wish to have the added complexity and difficulty of two
tables, you'll need to run an Append query to append the record to be
archived, followed by a Delete query (and then go through the same
process to reinstate the record). These queries can be run from a
macro or (better) from VBA code; if you use code, use a Transaction to
ensure that either both queries run, or neither does.

John W. Vinson[MVP]

 

[Guest]

Well then...couldn't see the forest for the trees. I got mysself so wrapped
up in the Archive thingy...Thanks.
I have been searching the help files for a way to create a filter that is
"permanent" to a Form, but can't seem to get it right.

 

[John Vinson]

Well then...couldn't see the forest for the trees. I got mysself so wrapped
up in the Archive thingy...Thanks.
I have been searching the help files for a way to create a filter that is
"permanent" to a Form, but can't seem to get it right.

Don't use a Filter property - instead, base the form on a Query
selecting the desired records. A Query will work just like a table for
everything you need in this instance.

John W. Vinson[MVP]