Indexing-No Duplicates Problem

[Roger]

I have set my table index to no duplicates, and to ignore nulls,
My form works when I go to update, but what I would like to see happen is
when the duplicate serial number is entered it would tell me then that the
number
is a duplicate. Also can I change the message box, to say You Have Entered A
Duplicate Serial Number Rather than the standard access message, that
doesn't
really tell them what they have done wrong?
Thanks for any help
Roger

 

[Tom van Stiphout]

On Sun, 2 Dec 2007 20:22:00 -0800, Roger

Depending on details of your form, you can probably intercept this
Access error message in your Form_Error event, and change the message.

You can also pre-test the value against your underlying table, by
running a query in code (either using DLookup or a Querydef), for
example from the <control>_AfterUpdate event, where <control> is the
control bound to your unique field.

-Tom.

 

[Tom Wickerath]

Hi Roger,

I have sample code for implementing the first method in the Northwind sample
database, using the Form_Error event, that Tom van Stiphout indicates in his
reply. If you like to see it, check out page 14 of my "Access Links.doc"
Word document. You can download a zipped copy from here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

_________________________________________

On Sun, 2 Dec 2007 20:22:00 -0800, Roger

Depending on details of your form, you can probably intercept this
Access error message in your Form_Error event, and change the message.

You can also pre-test the value against your underlying table, by
running a query in code (either using DLookup or a Querydef), for
example from the <control>_AfterUpdate event, where <control> is the
control bound to your unique field.

-Tom.

_________________________________________

 

[Roger]

Thanks
It sounds like what I need,
I will try it tomorrow
Thanks again.

 

[Roger]

Thanks
That worked perfectly

Hi Roger,

I have sample code for implementing the first method in the Northwind sample
database, using the Form_Error event, that Tom van Stiphout indicates in his
reply. If you like to see it, check out page 14 of my "Access Links.doc"
Word document. You can download a zipped copy from here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

_________________________________________

On Sun, 2 Dec 2007 20:22:00 -0800, Roger

Depending on details of your form, you can probably intercept this
Access error message in your Form_Error event, and change the message.

You can also pre-test the value against your underlying table, by
running a query in code (either using DLookup or a Querydef), for
example from the <control>_AfterUpdate event, where <control> is the
control bound to your unique field.

-Tom.

_________________________________________

 

[Tom Wickerath]

Hi Roger,

I see you decided to not wait until tomorrow <smile>.

Glad you found this helpful!


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________