How replace default message when duplicate entry is made in continuous form

I have a continuous form that is used for entering part orders.  The field that the user select a part from is a combobox.  The field in the orders table is set as Indexed (No Duplicates).

If the user selects a part that has already been selected in another record in the continuous form they get a canned error message that reads "The changes you requested to the table were not successful because they would create duplicate values in the index........" and so forth.

I want to replace that error message with my own message.  How can I do this?
SteveL13Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<
I want to replace that error message with my own message.  How can I do this?
>>

 Use the forms OnError event.   Any errors the form encounters will fire this event.   Two variables are passed in; the error that occurred (dataerr) and response.

 Response is what you set to tell Access what to do.   If set to acDataErrContinue, Access will ignore the error and skip displaying the message box.

 This allows you to put up your own message.

acDataErrDisplay is the default action that your seeing now.

Jim.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Use the BeforeUpdate or BeforeInsert event to check that the part doesn't exist.

    Cancel = Not IsNull(DLookup( ... ))

/gustav
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
use the beforeupdate event of the combo

private sub combo_beforeupdate(cancel as integer)
if dcount("*","tableName", "fieldname='" & me.combo & ''")> 0 then
  msgbox "duplicate"
  cancel=true
end if
end sub
0
 
SteveL13Author Commented:
All three very useful tips.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.