primary key violation and custom message

[Ann]

I have a command button which generates password and
append to userpass table.

Because one user can only have one password, I set
username as primary key in the table.

So when somebody click generate password button, if the
password already exists, it will show the message:

cannot append the table...due to validation rule
violation.

This is fine, but I want to custom the message a little
bit like: this user already have a password.
instead of using the Access original message?

How can I do that?

Thanks

 

[Dev Ashish]

cannot append the table...due to validation rule
violation.

This is fine, but I want to custom the message a little
bit like: this user already have a password.
instead of using the Access original message?

Use the form's OnError event. In such a case, Access will pass the code
execution to this routine with DataErr argument set to 3022, the
description to which is:

The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.

You can display your own message and set the response argument
appropriately.

-- Dev

 

[Tim Ferguson]

So when somebody click generate password button, if the
password already exists, it will show the message:

cannot append the table...due to validation rule
violation.

This is fine, but I want to custom the message a little

I take it you mean that the username should not already exist. There is no
reason that two people should not have the same password, unless there is
evidence that they are colluding somehow.

The easist way to check for the existence of a key in a table is just to
look for it:

strLookFor = "UserName = """ & Me!txtUserName & """"
If DCount("*","UserTable", strLookFor) > 0 Then
' UserName already in use

Else
' Okay, it's not already there

End If

The other thing that is a bit odd is the reference to a ValidationRule. If
you want to ensure that the UserName is unique, then use a Unique Index (or
the primary key), and that will produce a Key Violation error. If you have
a ValidationRule enforced somewhere in the table, you need to check what it
is and whether it is something that you really mean.

Hope that helps


Tim F