Forcing a unique primary key

[rathi]

Hi everyone,

I want to make sure that the user does not enter duplicate primary key and as well as it is not empty.

How can I trap these errors?

Any help is greatly appreciated.

sincerely,

Rathi.J

[HunterMcCray]

Originally posted by rathi
Hi everyone,

I want to make sure that the user does not enter duplicate primary key and as well as it is not empty.

How can I trap these errors?

Any help is greatly appreciated.

sincerely,

Rathi.J

In ACCESS set your primary key. In Access Set Ignore Nulls = No.

In VB in the event that triggers the addnew method check for null field. Then Place an On Error Goto Statement before the insert code, in the Error Handler Use MsgBox to tell the user that the data entered is invalid. Exit the Sub or Function without adding a record.

Hunter

[monte96]

I find that not exposing the user to the primary key at all is the best thing to do. The primary key is for the developer to distinguish records apart behond the scenes. In access, you should enable autonumber as your primary key which will ALWAYS be unique. In SQL, set up the first field of the table as an Identity column which works nearly the same way -or- you could write a stored proc that queries a small table that maintains the next number to be issued as a key. (Mimicing the Identity column)

[rathi]

Originally posted by HunterMcCray
Originally posted by rathi
Hi everyone,

I want to make sure that the user does not enter duplicate primary key and as well as it is not empty.

How can I trap these errors?

Any help is greatly appreciated.

sincerely,

Rathi.J

In ACCESS set your primary key. In Access Set Ignore Nulls = No.

In VB in the event that triggers the addnew method check for null field. Then Place an On Error Goto Statement
before the insert code, in the Error Handler Use MsgBox to tell the user that the data entered is invalid. Exit the Sub or Function without adding a record.

Hunter

Hunter,

I already set up that unique key as the primary key in the Access dbase. So, when the user enters a duplicate value, vb program displays the usual database error.

I just want to display a more user friendly message, saying "You have entered a duplicate value".

I can not set Autonumber field for this primary key. Because this field will hold different values like,
PCBS1, PCBS2 etc.

Thanks for your time and help.

sincerely,
Rathi.J

[HunterMcCray]

All you have to do is trap the error and handle it. The easiest way of doing this is:

Private Sub <Name of Sub or Event that handles AddNew>

'Code that Validates Data, Should include a check to see
' if Key already exists in Table if it does then
' use msgBox to alert the user and exit AddNewSub

'Then as a double precautionary:

OnError Goto AddNewError:

'Code that adds new record

onerror goto 0

exit sub

AddNewError:
msgBox
exit Sub

End Sub

Hunter

[PaulLewis]

rathi, you ought to heed what monte96 has said.

You ought never leave a primary key updateable to the end user. You need to use the concept of "Unique Foreign Key" in your tables. This means that you can use the AutoNumber type for the primary key of each table and if you need to have one table link to another, use the Unique key (it will be a Long) to do it.

If you really don't want to do that, then HunterMcCray has given you a workable solution.

As a point to remember though, it is unlikely you would ever find a database in any business application that allowed users to enter the primary key field. Of course I'm only going on my experience in the project that I have worked on :)

Regards