Customized Error Message

[Mike C.]

Hello.

I am currently working on a database that contains a
subform that conatains a combo box (in datasheet view)
which allows the user to choose a group associated with a
person's contact info. I have it set up so that they can
add multiple groups. When a group is chosen to which the
person already belongs I get the error message:

"The changes you requested to the table were not
successful because they would create duplicate values in
the index, primary key, or relationship. Change the data
in the field or fields that contain duplicate data, remove
teh index, or redefine the index to permit duplicate
entries and try again." (whew!)

This is exactly the type of error I want, however, not so
wordy. How can I generate my own custom error message
that simply tells the user "Contact already belongs to
this group. Please choose another."

Thanks in advance,

m.

 

[Mike C.]

Actually...I figured it out. I just put the following
code in the "on error" event for the subform.

Private Sub Form_Error(DataErr As Integer, Response As
Integer)
MsgBox "Contact is already associated with " & "'" &
[Type].Text & "'"
Response = acDataErrContinue
DoCmd.RunCommand acCmdUndo
DoCmd.CancelEvent

End Sub

Thanks.

m.

 

[Tim Ferguson]

How can I generate my own custom error message
that simply tells the user "Contact already belongs to
this group. Please choose another."

You have to anticipate the error before saving the changes. Pomething like
this in the subform form's BeforeUpdate event

If DCount("*", "TheAppropriateTable", _
"PersonID=" & Me!txtPersonID & _
" AND GroupCode = """ & Me!txtGroupCode & """") > 0 Then

' the combination already exits
MsgBox "Silly bugger"

' prevent the record update
Cancel = True

' and do something else
Me!txtGroupCode.SetFocus

Else
' it's okay, continue validation here...

End If



Hope that helps


Tim F

 

[Tim Ferguson]

This is exactly the type of error I want, however, not so
wordy.

(sorry to follow up my own follow up)
Better solution is to prevent the user trying the duplicates. Set the
rowsource of the combo to something like this, so used ones don't appear in
the list:

SELECT Groups.GroupCode
FROM Groups
WHERE Groups.GroupCode NOT IN
( SELECT UserAssignations.GroupCode
FROM UserAssignations
WHERE UserAssignations.UserID = Forms!MainForm!txtUserID
)
ORDER BY GroupCode

There is probably an INNER JOIN version, but it's been a long day...

Tim F