Hi experts,
I'm trying to insert records from tbl_A to tbl_B. Each record in tbl_B contains a unique caseID. I want to prompt an error message if any of the records in tbl_A that I want to insert into tbl_B already exist in tbl_B. However, I want the message to show which record(s) are those already existed ones by revealing their caseID(s), which means the error message that I use need to contain a parameter instead of pure strings. Moreover, I want to give two options for user to choose as either to proceed or to cancel. For the moment I can only come up with the following codes that display a simple message upon occurance and exits the function:
Sub UpdateTableB()
Dim sGetSQL As String
On Error GoTo Duplicate_Error
sGetSQL = "INSERT INTO tbl_B(caseID, fld_1, fld_2, fld_3, fld_4, etc ) " _
& "SELECT Mid(someStringThatContains
CaseID, 58, 8), fld_1, fld_2, fld_3, fld_4 " _
& "FROM tbl_A " _
& "WHERE fld_4 IS NOT NULL"
CurrentDb.Execute sGetSQL, dbFailOnError
Duplicate_Exit:
Exit Sub
Duplicate_Error:
Select Case Err.Number
Case 3022
MsgBox "The case already exists. Do you still want to proceed?"
End Select
GoTo Duplicate_Exit
End Sub
Can anyone help me to modify the codes so as to achieve my objectives?
Thank you very much!
Start Free Trial
