SQL Server Performance Forum – Threads Archive

Field Validation

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.

---

Well, it’s not a SQL Server issue – but in Access I’d check the Me.NewRecord property in the Form_BeforeUpdate event: it evaluates to True on a new record, and to False on an existing record (also False once the BeforeUpdate event has passed on a new record). On an existing record, if users are allowed to change the BookID field, you can compare Me![BookID] against Me![BookID].OldValue (provided that you’re working with bound controls.). Not sure about the BeforeInsert event – never used that event, to be honest.

---

]]>