Hi All - I hope someone can help. I found some code via the internet and modified it slightly and need some help with additional modifications. I've tried various internet searches and searching on this forum, but am appearently not searching the correct words/phrases to come up with what I am looking for.
I have 2 databases, db1 is my local database and db2 is my archive database.
db1 has a form with 4 combo boxes and a button with code that takes the info from the combo boxes and uses it to name a table that is exported from db1 into db2. The code then deletes the records from db1. The problem is that if people accidently name the tables the same, it will just copy over the existing archived copy.
So, I need the code to check to see if the table name already exists in db2 (the archive database). If that name already exists - it needs to pop up with a warning and NOT export the table. Below is the current code that I have. Any help would be greatly appreciated.
Sub New_Table()
Dim MBox As String, YBox As String, NBox AsString, IBox As String
On Error GoTo Err_ErrorHandler
strM = Forms("frm_Export").MBox.Value
strY = Forms("frm_Export").YBox.Value
strN = Forms("frm_Export").NBox.Value
strI = Forms("frm_Export").IBox.Value
strT = strM & "_" & strY &"_" & strN & "_" & strI
If IsNull(strM) Then
MsgBox"You must enter data in ALL fields to name the table you arearchiving!", , "Error"
Exit Sub
Else
DoCmd.TransferDatabase acExport, "Microsoft Access", "L:\Path\To\Archived\Database.accdb",acTable, "tbl_MyFindingsTable", "tbl_" & strT
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [tbl_MyFindingsTable]"
DoCmd.SetWarnings True
MsgBox"The table has been archived."
End If
Exit_ErrorHandler:
strT =vbNullString
Exit Sub
Err_ErrorHandler:
MsgBoxErr.Description, vbExclamation, "Error #" & Err.Number
ResumeExit_ErrorHandler
End Sub