Auto Archive Function

[Desstro]

I know this is not really necessary. I tried to talk sense into my boss but he want's it this way.

How can I, when a yes/no box is checked, have that record deleted from its current table and copied to another, "archive" table?

See image. Basically he wants is when the Posted field is checked, the PostedDate field is automatically filled in with the current system date and then the record to be moved to an archive table which will be called PostedPkgs.

If it makes any difference the Pkeys are PackageNumber, Program, FacilityArea and FacilityLocation. Will be the same for the archive table.



Code anyone?

[RuralGuy]

You should find this link helpful: http://allenbrowne.com/ser-37.html

[Desstro]

I have a form where my yes/no field is that I put this code in in the onclick event. It returne an "Archiving Failed Error 3131".

The error states "Syntax Error in FROM Clause"

Please help.

[RuralGuy]

Why are you using the IN statement? Isn't your Archive table in the same mdb?

[Desstro]

should it be? I pretty much just copied and pasted what was in the link you suggested and changed the data to match my DB. I created a new DB for archive but i don't see why not use same DB and delete archive annually.

Can you help me with what the code should look like if using the same db with a table called InternalPostedPackageArchive?

[RuralGuy]

It would actually be better if your archive were in another db but you need to specify the .Extent in the IN statement.

[Desstro]

I'm not sure I understand. you mean something like "IN C:\Documents and Settings\thorcc\Desktop\Database Work"?

I would like it to be a different DB.

[RuralGuy]

Nope! Something like:
"IN ""C:\Documents and Settings\thorcc\Desktop\Database Work.ACCDB"" "?
...and don't forget the EXTRA quotes!

[Desstro]

Ok I'm going to give it a shot.

[Desstro]

Private Sub Posted_Click()
On Error GoTo Err_DoArchive
  Dim ws As DAO.Workspace   'Current workspace (for transaction).
  Dim db As DAO.Database    'Inside the transaction.
  Dim bInTrans As Boolean   'Flag that transaction is active.
  Dim strSql As String      'Action query statements.
  Dim strMsg As String      'MsgBox message.
 
  'Step 1: Initialize database object inside a transaction.
  Set ws = DBEngine(0)
  ws.BeginTrans
  bInTrans = True
  Set db = ws(0)
 
  'Step 2: Execute the append.
  strSql = "INSERT INTO InternalPostedPackageArchive ( PackageNumber, Program, FacilityArea, FacilityLocation, ) " & _
    "IN ""C:\C:\Documents and Settings\thorcc\Desktop\Database Work.InternalPostedPackageArchive.accdb"" " & _
    "SELECT PackageNumber, Program, FacilityArea, FacilityLocation FROM PkgcReceivedandScheduledStatus WHERE (Posted = True);"
  db.Execute strSql, dbFailOnError
 
  'Step 3: Execute the delete.
  strSql = "DELETE FROM PkgcReceivedandScheduledStatus WHERE (Posted = True);"
  db.Execute strSql, dbFailOnError
 
  'Step 4: Get user confirmation to commit the change.
  strMsg = "Archive " & db.RecordsAffected & " record(s)?"
  If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
    ws.CommitTrans
    bInTrans = False
  End If
 
Exit_DoArchive:
  'Step 5: Clean up
  On Error Resume Next
  Set db = Nothing
  If bInTrans Then   'Rollback if the transaction is active.
    ws.Rollback
  End If
  Set ws = Nothing
Exit Sub
 
Err_DoArchive:
  MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.Number
  Resume Exit_DoArchive
 
End Sub

[Desstro]

I saw where I had C: in there twice, fixed it and still same error

[RuralGuy]

Your IN statement is still not correct! What is the FULL PATH to your Archive db?

[RuralGuy]

C:\Documents and Settings\thorcc\Desktop\Database Work.InternalPostedPackageArchive.accdb
The Highlighted part in not correct!

[Desstro]

Yup...fixed it. Actually fixed it before I read your last post so I am feeling better about myself now. Sorry, it's been a long night for me. I do appreciate your help and patience. Once again RuralGuy...you are the man!

[RuralGuy]

You'll notice I added code tags to your Post #10. Please use them in the future as it is *much* easier to read code that way.