Field Validation

[Guest]

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 & "'"))) The
MsgBox "BookID already exists", vbCritical, "Error
DoCmd.GoToControl "BookID
DoCmd.CancelEven
End I
End Su
------------------------------------------------------------------

And it doesn’t work

Any help would be appreciated
Please be as specific as possible

Thanks in advance for your prompt respond.

 

[Nikos Yannacopoulos]

Enclose your validation code in a check for new record like:

If Me.NewRecord Then
...validation code
End If

so it will not run on existing records.

HTH,
Nikos

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.

 

[Guest]

Thank you for reply!

It didn't work.
The code doesn't want to recognize new records.

-----Original Message-----
Enclose your validation code in a check for new record like:

If Me.NewRecord Then
...validation code
End If

so it will not run on existing records.

HTH,
Nikos

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.

.