Custom Error for Primary Key Violation

  • Thread starter Thread starter cleech
  • Start date Start date
C

cleech

Hello All:
I'm using an append query to add data to a tables primary key field.
I would like to give the user a different message than the regular Yes/
NO/Help message. I'm looking to have a message telling the user that
the record already exists and prompt to create a new one.
Logic would be
-Append "Bob" to table
-"Bob" Already exists
-Would you like to create "Bob1"

Below is a sample of the code I'm trying to use. I'm leaving the
commented out lines for illustration.

Function InstTestPlan(Product As String, AD As String)
On Error GoTo bail

'DoCmd.SetWarnings 0


strTplan = "AD" & AD & " " & Product

strSQLstmt = "INSERT INTO dbo_tblTestPlan ( strTestPlan,
dtmDateCreated ) " & _
"SELECT " & "'" & strTplan & "'" & ",'" & Now() &
"';"

DoCmd.RunSQL strSQLstmt

'DoCmd.SetWarnings -1

bail:
MsgBox Err.Number

' If Err.Number = 2501 Then
' MsgBox "Test Plan already Exists.", vbCritical
' End If

End Function


Thanks in advance for any help you might be able to provide.
Cleech
 
You could check for a duplicate and display a message before you run your
query;

Function InstTestPlan(Product As String, AD As String)
On Error GoTo bail

Dim strTplan, strMsg As String

strTplan = "AD" & AD & " " & Product

strMsg = "This ID already exists in the database." & vbcrlf
strMsg = strMsg & "Do you want to duplicate it?"

If DLookup("strTestPlan", "dbo_tblTestPlan", "strTestPlan=""" & strTplan &
"""")>0 Then
If Msgbox(strMsg, vbYesNo + vbExclamation, "Duplicate Data")=vbNo Then
Exit Sub
End If
End If

'DoCmd.SetWarnings 0

strSQLstmt = "INSERT INTO dbo_tblTestPlan ( strTestPlan,
dtmDateCreated ) " & _
"SELECT " & "'" & strTplan & "'" & ",'" & Now() &
"';"

DoCmd.RunSQL strSQLstmt

'DoCmd.SetWarnings -1

bail:
MsgBox Err.Number

' If Err.Number = 2501 Then
' MsgBox "Test Plan already Exists.", vbCritical
' End If

End Function
 
Back
Top