Importing Data

[Chrismkr]

I need some help witht the following

In this db I have two tables

Table one - (1) ec_facility - new
Table two - (1) ec_facility - old

I also have a form that will let me import data into table one ((1)
ec_facility - new)

My Goal is when I import the data into table I would like to take the data
from table one and put it in table two.

This would be a monthly import - so table one will have Feb 06 data and
Table two will have Jan 06 data

When I import March data Table one will have march data and table two will
now have Feb data

I have attach the code I came up with thus far


Private Sub cmdImport_Click()
On Error GoTo ErrorCheck
Dim varFilename

varFilename = Me.txtInputFile
If IsNull(varFilename) Then
MsgBox "You must enter an input filename.", vbExclamation, "(1)
ec_facility - new"
Me.txtInputFile.SetFocus
Exit Sub
End If

DoCmd.DeleteObject acTable, "(1) ec_facility - new"

DoCmd.TransferSpreadsheet ac, , "(1) ec_facility - new", varFilename, True


MsgBox "Import successful", vbInformation, "(1) ec_facility - new"

Exit Sub

ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select


End Sub