Replace System Error Message

[Ray]

I want to replace the system error message "Access was unable to make the
changes requested " etc etc when trying to enter a duplicate key with
something more user friendly.

Is it possible?

Thanks for any help.

 

[Ken Snell \(MVP\)]

Probably, but you'll need to tell us more details about what code you're
running or what you're doing when this error message occurs.

 

[Ray]

I have created a table with an "Indexed/No Duplicates" field called Member #.

I have created a form to add members. If I add a member number that already
exists I get the following error message"

"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"

I want something simpler like

"You have entered a number that is already assigned. Check the number and
try again"


I don't see an "on error" property for the individual field/control but only
for the entire form.

Is this enough information for you? I really need an answer to this.

Thanks in advance.

Ray

 

[Ken Snell \(MVP\)]

The typical way one handles this situation is to use the form's BeforeUpdate
event to test if a duplicate record would be created if the form saves the
data. You note that you're entering a [Member #] value. The code in the
BeforeUpdate event would look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("Member #", "NameOfYourTable") > 0 Then
MsgBox "Member # " & Me.NameOfControlHoldingMemberNumber.Value _
& " already exists in the database!", vbExclamation, "Duplicate
Member #"
Cancel = True
End If
End Sub

The above code will test to see if the member number already exists in the
table; if yes, it cancels the save of the data and tells the user. If it
doesn't, the form saves the data with no message.

Also, I note that you're using the # character in the name of a field in a
table. It and many other characters should not be used because the they have
special meanings for ACCESS and Jet. See this Knowledge Base article for
more information about characters that should not be used:

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763