change access error message

[Guest]

I have a textbox in a form to enter new values to a primary key field. When
a user enters an existing value in this textbox, Access displays the
following error:

"The changes you requested to the table were not successful b/c 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."

However, the error isn't displayed until the form is saved, so the user does
not know which field needs the value changed. Is there a way to change this
to a more user friendly error that specifies which field has duplicate
values?

One approach I tried was writing a macro for the control OnLostFocus, but I
can't figure out the code to compare it's value to the list of values in the
table's primary key field.

Thank you for your help!

 

[Guest]

You can use the DLookup() Function to determine whether the key already
exists. If the Key value is not in the table, DLookup will return Null.

If Not IsNull(DLookup("[KeyFieldName]", "TableName","[KeyFieldName] = '" _
& Me.MyTextBox & "'") Then
MsgBox Me.MyTextBox & " Already exists".......
End If

 

[Guest]

When using this code I get the message "Compile Error: Method or Data Member
Not Found" and VB highlights the .MyTextBox code. How can I fix this? Also,
what does [KeyFieldName] = ' mean or do?
Thanks!

You can use the DLookup() Function to determine whether the key already
exists. If the Key value is not in the table, DLookup will return Null.

If Not IsNull(DLookup("[KeyFieldName]", "TableName","[KeyFieldName] = '" _
& Me.MyTextBox & "'") Then
MsgBox Me.MyTextBox & " Already exists".......
End If

I have a textbox in a form to enter new values to a primary key field. When
a user enters an existing value in this textbox, Access displays the
following error:

"The changes you requested to the table were not successful b/c 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."

However, the error isn't displayed until the form is saved, so the user does
not know which field needs the value changed. Is there a way to change this
to a more user friendly error that specifies which field has duplicate
values?

One approach I tried was writing a macro for the control OnLostFocus, but I
can't figure out the code to compare it's value to the list of values in the
table's primary key field.

Thank you for your help!

 

[Guest]

You are using what I posted as literal. They are meant as place holder.
MyTextBox should actually be the name of the text box you enter your primary
key into.
[KeyFieldName] should be the name of the field your text box in bound to
(your primary key)

When using this code I get the message "Compile Error: Method or Data Member
Not Found" and VB highlights the .MyTextBox code. How can I fix this? Also,
what does [KeyFieldName] = ' mean or do?
Thanks!

You can use the DLookup() Function to determine whether the key already
exists. If the Key value is not in the table, DLookup will return Null.

If Not IsNull(DLookup("[KeyFieldName]", "TableName","[KeyFieldName] = '" _
& Me.MyTextBox & "'") Then
MsgBox Me.MyTextBox & " Already exists".......
End If

I have a textbox in a form to enter new values to a primary key field. When
a user enters an existing value in this textbox, Access displays the
following error:

"The changes you requested to the table were not successful b/c 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."

However, the error isn't displayed until the form is saved, so the user does
not know which field needs the value changed. Is there a way to change this
to a more user friendly error that specifies which field has duplicate
values?

One approach I tried was writing a macro for the control OnLostFocus, but I
can't figure out the code to compare it's value to the list of values in the
table's primary key field.

Thank you for your help!