Duplicate entry message

[QB]

I'm not sure if this is a programming question, but I would like to know if
there is a method to replace the default access message when the user tries
to enter a duplicate value in a field which has been set with an index=no
duplicates?

In my specifc case, I have a continuous form where the user selects from a
combo box a value. The value can only be assigned once. Right now if they
make the selection a second time an ugly message pops up that most users
simply do not understand. I would like to be able to replace it with a
simple 'plain english' message that the user could actually understand, so
they may resolve the problem and move on.

Thank you,

Qb

 

[RD]

I'm not sure if this is a programming question, but I would like to know if
there is a method to replace the default access message when the user tries
to enter a duplicate value in a field which has been set with an index=no
duplicates?

In my specifc case, I have a continuous form where the user selects from a
combo box a value. The value can only be assigned once. Right now if they
make the selection a second time an ugly message pops up that most users
simply do not understand. I would like to be able to replace it with a
simple 'plain english' message that the user could actually understand, so
they may resolve the problem and move on.

Thank you,

Qb

There is probably a way to do what you ask however, before we get into
that, what exactly are you trying to accomplish? Ordinarily one would
use a combo box for values that are used over and over again and you
want to make sure the entries are the same (e.g. a list of cities or a
list of statuses). Having a combo box containing values that are
unique, intended to only be used once, doesn't make a lot of sense to
me.

RD

 

[Marshall Barton]

I'm not sure if this is a programming question, but I would like to know if
there is a method to replace the default access message when the user tries
to enter a duplicate value in a field which has been set with an index=no
duplicates?

In my specifc case, I have a continuous form where the user selects from a
combo box a value. The value can only be assigned once. Right now if they
make the selection a second time an ugly message pops up that most users
simply do not understand. I would like to be able to replace it with a
simple 'plain english' message that the user could actually understand, so
they may resolve the problem and move on.

Try using the form's Error event to catch the error and
display a message box.

OTOH, more work but it would be much better to not give
users the choice of selecting a used value. This way you
would never get that error. Try to find a way to filter out
the used values in the combo box's row source query.

 

[Daryl S]

QB -

You could go one better for your users and set the recordsource of the
combobox to exclude those that are already taken. That prevents them from
making the mistake in the first place.

 

[QB]

Hard to explain my scenario in detail, but a cbo is required in this instance
so the user can see the list of choices. The idea that Marshal and Daryl
mentioned of only displaying remaining choices - rather than all the choices,
so filtering the cbo, is one that I will look into further.

Thank you for the help.

 

[Linq Adams via AccessMonster.com]

I agree that the idea of limiting the choices to those not already selected
is really the user-friendly way to go, but here's how you would replace
Access' default error message with one of your own, as Marsh suggested:


Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim Message As String
If DataErr = 3022 Then 'Duplicate value entered
Message = "You Have Entered a Duplicate Value For The Field: " &
Me.ActiveControl.Name
Response = MsgBox(Message, vbExclamation, "Duplicate Value Entered")
Response = acDataErrContinue
End If
End Sub