prevent dupes if

[deb]

I have a form called f40projectMain(PK ProjectID) that has a CONTINUOUS
subform called f4ProjContacts. The form f4ProjContacts uses record source
that is the table t41ContactsProj(PK ProjectID and ContactSubID and
ContactID).

How can i check for dupes if ContactSubID= 48 or 49 and Current field =yes
per ProjectID?
There can be multiple ContactSubID= 48 or 49 if Current field =null or ""
or there can me multiple other ContactSubID's.
The Current field only pertains to ContactSubID 48 or 49 all other
ContactSubID's the Current field will be null or "".

ProjectID................ContactSubID.......ContactID...Curren
...1...............................48........................3...............ye
...1...............................49........................4...............yes
...1...............................49........................6..............
...1...............................48........................2...............yes this is a dupe (see below
...2...............................48........................4...............yes
...2...............................30........................3...............
...2...............................30........................6...............

this is a dupe since there is already a ContactSubID=48 that has Current
=yes for that particular project.
Translated...
ProjectID................ContactSubID.......ContactID...Current
...abc123......................Manager..............Ann...............yes
...abc123......................ProjMgr................Bob...............yes
...abc123......................ProjMgr................Jim...............null
or ""
...abc123......................Manager...............Sue...............yes
this is a dupe
...def345.......................Manager..............Bob................ye
...def345.......................Intern..................Ann...............null or "
...def345.......................Intern..................Jim...............null or ""

I really need your help!!

 

[Tom Wickerath]

Hi Deb,

You should be able to disallow a duplicate record, by adding a multi-field
index to the table, with Unique = Yes. In table design view:

Access 2003 and earlier: Click on View | Indexes
Access 2007: Click on the Indexes button in the Show/Hide group of the ribbon

Add an Index Name and the required fields. For example:

Index Name Field Name
ContactProjectCurrent ContactSubID
ProjectID
Current


Leave the name field empty in the next rows, so that this index applies to
the combination of fields. Set Unique = Yes.

Note: I'm not exactly sure if you want/need the ProjectID field in this
index, as you indicated "PK ProjectID" (the PK suggesting primary key) but
you also indicated "per ProjectID". If ProjectID is truly a primary key, then
you cannot have duplicates in this field, ever. Is this field part of a
multi-field primary key (in which case, it is not a primary key by itself)?

Violation of a Unique Index will generate a really ugly looking error
message. I show you how to use code in a form to trap for this error, and
present a more user-friendly error message. See the link in my signature for
my web page. Download the zipped "Access Links" document. Search the document
for 3022 (that's the error number generated).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________