Best way to intercept ODBC primary key violation error on recrd in

D

Doug Goodenough

Hello,

I have a subform that is used to add records to a linked SQL Server table.
Of course when duplicate key values are entered on the form the ODBC error
message comes up saying the record cannot be inserted due to PK violations.
This is not a friendly message for the business users to see. I am trying to
find a way in a event proc to intercept this problem before the odbc error
message is displayed.

I tried this code in the Before Insert event of the sub form.....

If DCount("dx", "dbo_dx", "[dx] = """ &
Forms![F_Main]![SF_HSR].Form![HSR_DX].Form![dx_cmb] & """") > 0 Then
MsgBox "This DX code has already been selected for this HSR.",
vbCritical, MSGTITLE
DoCmd.CancelEvent
End If

But when I debug it seems the value just entered in the key field is not
available yet in the event as when I trace it the values is always null. I
am sure this is a common problem but it has me stumped........what are some
good ways to handle this?

Thanks very much,
Doug
 
Top