How do I display SQL constraint errors?

[isabelle2378]

Hi,


I am a SQL DBA and have not worked much with Access. I have an existing Access 2007 database that has linked tables to SQL 2005 tables. There is a constraint on a table to eliminate entering duplicate values but no error displays in the Access form when it is triggered.

I confirmed that the constraint is working on the SQL side but when I go into the Access form and enter a duplicate value, it just disappears as if it was accepted but no value was entered into the SQL database.

Thank you in advance.

Isabelle

[pbaldy]

That sounds odd because Access would normally report the error. Check the form's error event and make sure it isn't handling it that way.

[isabelle2378]

Hi Paul,
Thank u for your reply. When I go into the Design View on the button that takes me into the form, right click on the field and click on the event, this is what I see:

Private Sub cmdfrmBarcode_Click()
On Error GoTo Err_cmdfrmBarcode_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPrtMgr_Barcodes"

stLinkCriteria = "[ItemID]=" & Me![ItemID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdfrmBarcode_Click:
Exit Sub
Err_cmdfrmBarcode_Click:
MsgBox Err.Description
Resume Exit_cmdfrmBarcode_Click

If I click on the Button and it brings me to the 3 fields that I am trying to enter these duplicates into, I right click and show properties, there is nothing in the event tab.

Thanks,
Bea

[pbaldy]

You are undoubtedly more knowledgeable than I on SQL Server (being self-taught, there are huge gaps in my knowledge). I would normally prevent duplicates by having the field in question be a/the key field. Is that how you're doing it?