Help! Need custom error message and rid of Access's message

[Tammy B.]

Ok, I have a form that will NOT allow duplicate values (I have this propoerty set for that field in the TABLE design to Indexed (yes, no duplicates). Therefore, Access will NOT allow my users to type in the same number for any record.

I do NOT want Access's message. I want my own message to pop up and tell the user to enter in another, different value.

My Table name is Derby Dog Table.
My Field name (that needs to be unique and is set to Indexed, no duplicates) is Derby Dog Number.

Please someone tell me how to do this,.....in plain, simple English! I have searched high and low and can't find any help.

Thanks so much!!!

 

[Naresh Nichani MVP]

Hi:

Take a look at this --

http://dbforums.com/arch/42/2002/6/377711

Naeresh Nichani
Microsoft Access MVP

Ok, I have a form that will NOT allow duplicate values (I have this

propoerty set for that field in the TABLE design to Indexed (yes, no
duplicates). Therefore, Access will NOT allow my users to type in the same
number for any record.

I do NOT want Access's message. I want my own message to pop up and tell

the user to enter in another, different value.

My Table name is Derby Dog Table.
My Field name (that needs to be unique and is set to Indexed, no

duplicates) is Derby Dog Number.

Please someone tell me how to do this,.....in plain, simple English! I

have searched high and low and can't find any help.

 

[john]

Hallo! I am from Greece and I have been using Microsoft Access since 1997. I think this product is one of the best Microsoft product! My name is John.
The solution to your question is to use Microsoft Visual Basic for Applications (VBA) and trap event errors. To do that, use the "On Error Goto" reserved words to a procedure or function.

*** Example ***
Private Sub Demo()
On Error Goto Error_Msg

Exit_Error
Exit Sub

Error_Msg:
MsgBox "There was an error....",vbOkOnly+vbExclamation,"Error"
' you can also use the Err.Number and Err.Description
Resume Exit_Error

You can also use the validation rules and validation text of the properties sheet of a field
end sub

 

[Tom Wickerath]

There are two KB articles that show alternate methods of doing this. Both of these
methods use domain aggregate functions, and are called via macros in the BeforeUpdate
event procedure for the Customers textbox. It is fairly easy to convert these macros to
VBA code, which is something that I recommend doing so that you can include error
handling.

How to Check for Duplicate Values in Primary Key Fields:
http://support.microsoft.com/?id=209487 uses the DLookup function.

http://support.microsoft.com/?id=102527 used the DCount function.

These examples check for duplicates in the primary key fields, but they should work
equally well in your indexed field. Using the BeforeUpdate event procedure allows you to
provide immediate notification to the user that a duplicate value exist, as soon as they
tab to or click into a new field. The method outlined in the dbforums archive fires an
error message when you attempt to commit the record. You may want your user to know much
earlier, before they spend their time filling out several fields.

http://www.mvps.org/access/general/gen0018.htm


Tom
_______________________________________


Hi:

Take a look at this --

http://dbforums.com/arch/42/2002/6/377711
Note that you will have to make a slight adjustment to the usage of these domain aggregate
functions, if your Derby Dog Number field is numeric (as I suspect that it is). Check out
the following document for an excellent example of using DLookup:


Naeresh Nichani
Microsoft Access MVP
_______________________________________


Ok, I have a form that will NOT allow duplicate values (I have this
propoerty set for that field in the TABLE design to Indexed (yes, no
duplicates). Therefore, Access will NOT allow my users to type in the same
number for any record.

I do NOT want Access's message. I want my own message to pop up and tell
the user to enter in another, different value.

My Table name is Derby Dog Table.
My Field name (that needs to be unique and is set to Indexed, no
duplicates) is Derby Dog Number.

Please someone tell me how to do this,.....in plain, simple English! I
have searched high and low and can't find any help.

Thanks so much!!!