Checking for duplicates

[HJAMES]

My Access application is a front end for an Oracle database. My LAB_NO field is a key field so I can't have duplicates. I get an error message from Oracle when I try to save the new (duplicate) record. I want to check for duplicate right away. I know I can use a sql select statement but then I don't know what to do next. Can you help?

Thanks,
Helen

 

[charityg]

If you are refering to validating on a data entry form, I find it easier to use a recordsetclone and search for the entered value. So on BeforeUpdate event of the field

dim rst as recordset
set rst=me.recordsetclone

rst.findfirst "[fldName]=" & me!fldName

if rst.nomatch=false then
msgbox "Duplicate value."
Cancel = True
Me![fldName].Undo
endif

Hope that does it for you.

Charity

 

[HJAMES]

Thanks for you answer but I can't make this work. I never get a false condition. What am I doing wrong?