VBA: Is it possible to check for a duplicate entry in a table before insert? (1 Viewer)

Hi, this is my first post here, and like most folks, i've got a problem. I'm trying to develop a database of components for my company; all components are identified by a 10 digit primary key and have two atributes. I used some VBA code to handle insertion, let the user pick from some drop down tables, and prevent direct access to the table. For the insertion itself I'm trying to use an sql statement.

wire_csTBL is the table, it has 3 columns.

mysqlstmt = "INSERT INTO wire_csTBL ([KeyCode_wire_csTBL], [SortCode_wire_csTBL], [SpecCode_wire_csTBL]) VALUES ('" & newKeyCode & "', '" & newSortCode & "', '" & newSpecCode & "');"
DoCmd.RunSQL mysqlstmt

This works fine, but I can't seem to catch the error from an existing record with the same primary key until after SQL has created a popup box. If the user clicks yes error handler is skipped, if he clicks no, it goes to my error handler.

What I'm really after is a way to get a Boolen response on an SQL query, but I'm not sure if this is possible, on the other hand, being able to just dodge the SQL error box and go straight to my error handler would also work.

Anyone have any ideas?

Hello Argonak
Do a DCount (you could also do a DLookup) on the PK value to be inserted first, if this returns 0 then the value doesn't exist.

IF (DCount(newKeyCode,wire_csTBL )=0) then
"Run your insert statement"
Else
"Do something else"
END IF

Bryan

Thankyou very much, I'll try that.

EDIT: It worked great, thankyou!