One thing you should consider is wrapping your code in a transaction. Otherwise, you could corrupt your data. For example, suppose you issue the command to insert the data into the history table, then delete the record from the Total Vacation Allowed table. But before you can issue the command to delete the record from the Employees table, something happens (i.e. program aborts, system crashes, whatever).
In this case your tables are no longer in sync. Here's basically what you want to do (note the BeginTrans, CommitTrans, and the RollbackTrans commands):
Code:
Function DeleteEmployee()
Dim cnn As ADODB.Connection
Dim bolBeginTrans As Boolean
On Error GoTo ErrHandler
bolBeginTrans = False
If (MsgBox("Do you want to delete the Employee ID?", vbYes + vbNo + vbQuestion) = vbYes) Then
Set cnn = CurrentProject.Connection
cnn.BeginTrans
bolBeginTrans = True
cnn.Execute "insert into Employee History..."
cnn.Execute "Delete * from Total Vacation Allowed ..."
cnn.Execute "Delete * from Employees..."
cnn.CommitTrans
End If
ExitFunction:
Exit Function
ErrHandler:
MsgBox Err.Number & vbCrLf & Err.Description
If (bolBeginTrans) Then cnn.RollbackTrans
Resume ExitFunction
End Function
Another thing you might want to consider is to archive your records (rather than writing them to a history table). For example, create another database that contains 2 tables: Total Vacation Allowed and Employees (these tables have the same structure as the 2 tables you currently have. Note they also have the same name). This database is your archive database. Instead of inserting the records into a history table insert them into the 2 archive tables.
Now I assume you have a form where the user can enter Employee information (tied to the table Employees) and I assume you have another form where the user can enter the data into the Total Vacation Allowed table. Finally, I assume you have a report that prints the data from the 2 tables. If so, you could simply have a button (or menu item or something) that when selected, the user can alternately view and/or report on either the archived data or live data. You use the same forms and reports for both sets of data (live and archive). So basically, when the user selects the button, your program simply relinks to the tables in the arhived database. Your forms and reports are now reporting archive data. When they select the button again, your program relinks to the live database and now your forms and reports are reporting live data. Remember, your forms and reports don't change. You simply change the links to where the tables reside (live or archive). You might also consider adding a label on your forms/reports so when the user selects the archived database, the labels are made visible and displays something like "Editing Archived Data".
Finally, since you already have the code to archive the records, you could use the same code to unarchive records if necessary.
To summarize:
1. Create an archive database and import just the structure of your 2 tables and give the 2 tables the same names as the tables in your live database.
2. Create a function that archives/unarchives the data (already done)
3. Create a function that relinks the 2 tables to either the live database or archived database
I have explained how to do this in another thread. So if your interested in how this works, let me know and I will find the thread.
