dealing with a primary key violation- user tries to enter a value that already exists

[lsheetrit45]

All Access Genuises,

I Need some help here!

My excerpt of code below grabs two fields from the user (strOrder and strInspector) and writes these two fields to a table called tblAssignment. These two fields serve as a composite primary key.

If the value of these two fields exist in tblASsignment when the user tries to write them to a table, a debug error pops up.

I need help replaing that debug error with a custom message or an "Exit Sub"

Please please please provide detailed answers because I am fairly new to Access VBA. Thanks!!!!


Private Sub btnAssignInspector_Click()

Dim strInspector As String
Dim strOrder As String

Dim rs As Recordset
Dim db As Database


Set db = CurrentDb
Set rs = db.OpenRecordset("tblAssignment")

'code not incldued that promps the user for values for strInspector and 'strOrder

rs.AddNew
rs!POItem = strOrder
rs!InspectorID = strInspector

'*********This rs.Update line is the one that throws the debug error 'when there is a primary key violation*******

rs.Update
rs.Close

End Sub

[orange]

I suggest you check to see if the Values entered by the user already exist in the Table.
If the value exists, give a message and ask them to input a different value

If the value doesn't exist, do nothing or give a confirmation message.

You can do the check if the value exists using DCount.

Syntax and an example are here
http://www.techonthenet.com/access/functions/domain/dcount.php

Also, you may consider letting the user choose values from dropdown lists.
That could avoid any errors in spelling/typos.

Hope this helps

[lsheetrit45]

Orange, Thank you for your quick reply! (and the link!)

This is exactly what I needed!