Access MDB connected to SQL Server. Working with Inventory form.

Need to check a value of BookID (Text) field to prevent duplicates in
Books table.

The Inventory form already has several lines of code checking for
empty fields: BookID, Author, etc. when Ok button pushed.

Need to make sure that the new code (BookID field validation) is
bypassed when users modify old records. Otherwise users will be
getting ("BookID already exists") error any time they try to modify
any field of the Inventory form.

Trying:

-------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
If (Not IsNull(DLookup("[BookID]", "Books", "[BookID] ='" & Me!BookID
& "'"))) Then
MsgBox "BookID already exists", vbCritical, "Error"
DoCmd.GoToControl "BookID"
DoCmd.CancelEvent
End If
End Sub
-------------------------------------------------------------------

And it doesn't work.

Any help would be appreciated.
Please be as specific as possible.

Thanks in advance for your prompt respond.

Put your check in the afterupdate event for the field. Also, I would
use Dcount.
On 18 Apr 2004 15:40:12 -0700, lovevsmatrix (AT) yahoo (DOT) com (sp_1000) wrote: