Field Validation

sp_100
04-19-2004, 01:59 PM
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.

loquin
04-20-2004, 03:55 PM
Wouldn't it be easier to have the database check for uniqueness??? i.e. make the BookID field be either the Primary Key or a unique key in the table?

sp_100
04-20-2004, 06:37 PM
Thank you for reply!

BookID is a PK on Books table.
And SQL server ignores duplicates. But, unfortunately, users are not aware of all this.

When BookID and the rest of the fields on the form are populated, and OK button pushed, nothing happens.
If at this point record selector button on the bottom of the form clicked (to go back to an existing record or to open an empty form for a new record input), an error pups up:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_Books'. Cannot insert duplicate key in object 'Books'. (#2627)
[Microsoft][ODBC SQL Server Driver][SQL Server]This statement has been terminated. (#3621)

I am looking for a code that would generate:

MsgBox "BookID already exists", vbCritical, "Error"

when OK button pushed, if BooksID already in Books table, would let the user to change BookID, keeping the rest of the fields untouched (populated).
Or, if the BookID is new, would let it go (no messages, just save the record).

Thanks again.

MKoslof
04-20-2004, 07:17 PM
Before running any addnew or insert command, you could check the database and see if the record already exists. You could use a SELECT Count(*) sql statement and check against the primary key field. If the recordcount is 1, a value already exists..prompt the user and exit the operation. If the recordcount is 0, continue with the code.

wengwashere
04-20-2004, 07:20 PM
I suggest you do some error handling... trap the error number (Err.Number) of the error raised and then pop the Error Message once youve seen it.

Btw, is your primary key a user input?

sp_100
04-20-2004, 07:50 PM
I suggest you do some error handling... trap the error number (Err.Number) of the error raised and then pop the Error Message once youve seen it.

Btw, is your primary key a user input?


Yes, BookID (PK, text) entered by users.
Would you be more specific (code, etc.) with 'error handling', please?

I did:

------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2627
MsgBox "Company ID: " & " & acno.Value & " & " already been entered in the database. Please select other Company ID", vbCritical, "Error"
Response = acDataErrContinue

Case 3621
MsgBox "Company ID: " & " & acno.Value & " & " already been entered in the database. Please select other Company ID", vbCritical, "Error"
Response = acDataErrContinue

Case Else
Response = acDataErrDisplay

End Select
End Sub
---------------------------------------------

But nothing happened.

Thank you.

sp_100
04-20-2004, 08:10 PM
I also did:

----------------------------
Private Sub BookID_LostFocus()
If Me.NewRecord Then
If Not IsNull(DLookup("[BookID]", "Books", "[BookID] ='" & Me.BookID & "'")) Then
MsgBox "BookID already exists", vbCritical, "Error"
Me.BookID.SetFocus
End If
End If
End Sub
----------------------------

Then any BookID is rejected as "BookID already exists"

Thanks.

sp_100
04-20-2004, 08:13 PM
But I would like to evaluate the BookID (pop the message) on OK button click.

wengwashere
04-20-2004, 08:17 PM
who calls that function?

is it something like this:

On Error Goto ErrorHandler
'some codes here...

ErrorHandler:
call Form_Error(Err.Number, Response)


if not, then thats a way of trapping an error and doing whatever you wanna do when you catch them. If an error is encountered after the On Error Statement, it will be then thrown to the ErrorHandler part... thus... calling the Form_Error that i assumed, you created.

MKoslof
04-21-2004, 09:26 AM
You should really consider the method I have suggested. Why even trap an error if you can prevent it in the first place. You have a unique identifier. So, before inserting a new records, check the table and see if this record already exists. If it does, prompt the user and back out of the routine. No error trapping needed in this case.

wengwashere
04-21-2004, 06:54 PM
You should really consider the method I have suggested. Why even trap an error if you can prevent it in the first place. You have a unique identifier. So, before inserting a new records, check the table and see if this record already exists. If it does, prompt the user and back out of the routine. No error trapping needed in this case.

I agree with MKoslof's and loquin's suggestion. You should consider those first to be sure that there are no duplicate values inserted in ur table.

I just explained to you the error handling since you asked me to elaborate my suggestion. Try to surf around the forums for more stuffs on error handlings :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum