Creating a Msg Box for a certain condition

[Sue Compelling]

Morning Gurus

I want to be able to prevent my users from creating duplicate Area
Coordinators for a region and have got half way there but don’t know how to
finish.

I have been able to create a query that tells me where a duplicate exists
but I now want to be able to have the query run AfterUpdate of the
Contact_Type and if the ContactID is in this query I then want to be able to
bring up a Msg Box that states “Duplicate Record etc, etcâ€

Or else I just want it to continue

My query to determine the duplicate Coordinator is:
SELECT tblContacts.ContactID, [firstname] & " " & [lastname] AS CombNane,
tblRegions.Region
FROM (qryDuplicateRegions INNER JOIN tblRegions ON
qryDuplicateRegions.Region = tblRegions.Region) INNER JOIN tblContacts ON
tblRegions.RegionID = tblContacts.RegionFK
WHERE (((tblContacts.ContactID)=[forms]![frmcontacts]![contactid]) AND
((tblRegions.Region)=[qryduplicateregions].[region]) AND
((tblContacts.ContactStatus)="closed") AND ((tblContacts.ContactType) Like
"*coordinator*"));

TIA

 

[Piet Linden]

or you could create a unique index on the two fields... Open the table
in design mode. Under the View menu, choose Indexes, and put the
index name under the left column and then the two field names in the
Field Name column (one under the other), and specify a sort order.

 

[Sue Compelling]

Hi Piet

Thanks - I have just discovered union queries (didn't know they existed
prior to two days ago) but I still need help with the following part of my
problem:

I want to be able to have my query "qryDuplicateCoordsAlert" run AfterUpdate
of the Contact_Type if the ContactID is in it. And then I want to be able to
bring up a Msg Box that states “Duplicate Record etc, etc†or else I just
want it to continue if it is not in there.

How do I write this to make it happen?

TIA