Custom Display Duplicate Message

[Sprinks]

In a continuous subform, the first control is a combo box bound to a Project
Number field. I would like to allow only one row per project number for each
main form record, and so set a combination unique index in the table
underlying the subform.

It displays a warning that it cannot create the record, but only after
attempting to move off the record. At this point, the user may have entered
a significant amount of data. I would like to:

- Inhibit the Access message
- Display a custom message as soon as the user has entered the duplicate value
- Give the user the option of going to the existing record & deleting the
new one, or re-entering the value

Can anyone point me in the right direction?

Thank you.
Sprinks

 

[Damian S]

Hi Sprinks,

Have the check performed in the after update or on click event of the combo
box. Have it perform a dcount on the ProjectID and if it returns bigger than
0 there is already one there. No need to exclude the current record as it
hasn't been written to the table yet (obviously, otherwise it would return
the error itself).

Hope this helps.

Damian.