Chamge Default Diplicate Record Message

[KevinM]

In MS Access 2003 my tables are linked via a SQL Server.
I'd like to change the default long-winded 'ODBC etc etc 'error message when a duplicate PK is added to a table to a more user friendly 'plain english' message.
Can this be done is Access, VBA or even on the SQL server? (which I have acces to).

Thanks

 

[tehNellie]

From an Access point of view, you might well be better off trapping the Error in VBA when you call your StoredProcedure. You can have your Stored Procedure return "custom" Error messages using RAISEERROR, but you should still trap and handle them properly in the VBA.

very brief sort of example

On error goto errhandle

exec spinsertrecord "some data" -- causing the error

errhandle:

If err.number = Whatever the number is for "you tried to insert a duplicate" Then
  msgbox "you tried to enter a duplicate record"
else
  msgbox "something else went wrong: " & err.number & vbcrlf & err.message
endif

exit sub

 

[KevinM]

Thanks tehNellie

I'll go down the VBA route.

Just having problems in the placement of the error message.
At what event proc should it be placed in the form?

Many Thanks

 

[tehNellie]

In the example above:

On error goto errhandle determines what VBA should do when it encounters an error, in this case I want it to goto errhandle which contains some code do something with the error rather than stick the default message to screen (on error goto 0) or ignore it (on error resume next).

errhandle:: This is the start of your error handling block it goes in your sub(), normally at the bottom of your code.

If err.number = Whatever the number is for "you tried to insert a duplicate" Then
  msgbox "you tried to enter a duplicate record"
else
  msgbox "something else went wrong: " & err.number & vbcrlf & err.message
endif

exit sub

This bit determines what you do with an error. Every error has a number (err.number) and default text (err.description). The IF block checks for the error number returned by SQL server/ODBC (don't know what it is off the top of my head) and if it's the duplicate row/value error then it displays a more friendly message, otherwise it displays the standard err.number and what should be err.description which is the standard, normally cryptic error message and the standard option to end or debug the code.

The exit sub then tells it to exit the sub() you're currently in although you could tell it to Resume next if you're sure you just want to carry on.

So you'd normally have something along the lines of:

Sub example()
  ON ERROR GOTO errhandle

  [i]some vba[/i]

  errhandle:
    IF err.number = [i]the error you want to handle[/i] then
      msgbox "You tried to enter a Duplicate value"
      Resume next ' I'm going to let the sub carry on here, maybe it's not an important error, the Code will go to the next line in [i]some vba[/i] above and try to carry on.
    else
      Msgbx "something else bad that I don't really care about happened"
      Exit Sub ' But here I'm going to terminate the code
    Endif
    
end sub

You can be quite selective in how you handle your errors. You might only want to go to errhandle when a duplicate row is inserted and present a nicer message to the user and leave VBA to do the rest.

 

[KevinM]

Thanks for your help on this.

The error message is not the usual 'MS Access debug' type, it's an 'ODBC Error dialog box'. The err number is 3146 'ODBC Call Failed' but it's still not trapping it.

Sub example()
On Error GoTo errhandle


errhandle:
If Err.Number = 3146 Then
MsgBox "You tried to enter a Duplicate value"
Resume Next
Else
Msgbx "something else bad that I don't really care about happened"
Exit Sub ' But here I'm going to terminate the code
End If

End Sub

I'm putting this in the form's general procedures.

 

[tehNellie]

OK, to be "extra sure" assign err.number to a variable immediately after your insert command/Stored Procedure triggers. If I get a few more minutes today I'll try and replicate the problem, you calling a command directly to the server or just locally inserting to the linked table in Access?

  'your insert statement with a duplicate value to cause the error to occur
  Command.execute

  'now we immediately assign the error number to a variable
  intErr = err.number



debug.print intErr

 

[KevinM]

I'm just using a standard MS Access form and no SQL or vb code to to the INSERT.
I'm just relying in the built in mechanisms which is where i'm having a problem to capture the error.
I am comfortable using SQL server and TRIGGERS etc but this custom message box is only necassary for the MS access front end form.

 

[tehNellie]

OK, that's probably why you're having problems trapping the error.

You might be better off having the user populate the information on the form and hit a "Submit" button, or similar with VBA code attached to that button triggering the INSERT and able to handle any errors.

 

[KevinM]

Thanks tehNellie for all your help.