Delete Record / Send Email 1

[jbento]

I posed this question before, but I'm going to do it again. The following is the original question:

A user is on a record on a form. He/she finds that they no longer want that record in the database. I have a Delete button on the form. When they click the button they think they will delete the record, but I want that record to go to a archive table, and then at the same time I want an email message to come to me to notify me of what is going on. Does anyone know code for this? If no one has code for the whole thing, I would definitely want to know how to send the record to the archive table, when the user clicks the Delete button.

This is the code I got for it:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim db As DAO.Database, rst As DAO.Recordset

'Copy Record and Save Record to new table
Set db = CurrentDb
Set rst = db.OpenRecordset("YourArchiveTable&quot
rst.AddNew

rst!field1 = Me!field1
rst!field2 = Me!field2
rst!field3 = Me!field3
' Note: the fields should correspond to your names
' add as many as you need etc.

rst.Update
rst.Close
db.Close


'Delete record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

'send e-mail
DoCmd.SendObject acSendNoObject, "", acFormatTXT, "To-you", , , "Deleted a record", "message", True


Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

There is one problem with the code, when ran. The following line comes up with an error:

Dim db As DAO.Database, rst As DAO.Recordset

I'm working with a Access 2000 database. Could that be the problem, or do someone know why the code doesn't run correctly. Please help. Thanks.

Jerome

 

[DougP]

Did you check the "Microsoft DAO 3.6 Object Library" in the VBA editor

Open somehing so you get to the VBA editor.

In there click on "Tools" menu Then "Rferences" menu (they are at the very top
you know "File" "Edit" "Tools"
"References..."

Then in the list on the left look way down for
"Microsoft DAO 3.6 Object Library"
now you have to click in the little box on the left of it to use it in your database.

Now there is another issue too I ran across before
If the "Microsoft DAO 3.6 Object Library" is checked and you get an error then take the DAO off your code like so

Dim db As Database, rst As Recordset '<<<< SEE NO DAO

OK

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[jbento]

Doug,
YES I DID, AND IT WORKED. I'm at work now, and I just checked the DAO setting, and it was not set, but once I did that, everything worked FINE. Thanks so much.