prevend dupes if

[deb]

How can I prevend dupes if ContactSubID =48 or 49 and Current=yes?

I have a form called f40ProjectMain PK is ProjectID
with a CONTINUOUS Subform is called f4ProjContacts Record source is
t41ContactsProj (PK is ProjectID, ContactSubID and ContactID). The field
called Current is a check box.

Each project can have many contacts. However, there can only be one current
Manager(ContactsSubID=48) and only one current ProjManager(ContactsSubID=49).
There can be other Manager(ContactsSubID=48) and
ProjManager(ContactsSubID=49) but only one that has current field set to yes.
The other ContactsSubID's do not use the current field since there can be as
many as user wants.

The t41ContactsProj table looks like this...

ProjectID.......ContactsSubID...........ContactID......Current
.... 1 ................... 48 ........................... 3 ...............yes
.... 1 ................... 23 ........................... 1 ...............
.... 2 ................... 48 ........................... 3 ............... yes
.... 2 ................... 48 ........................... 5 ...............
.... 3 ................... 49 ........................... 5 ............... yes
.... 3 ................... 49 ........................... 2 ...............
.... 3 ................... 48 ........................... 3 ............... yes

translated...
ProjectID.......ContactsSubID...........ContactID......Current
.... abc123 ........... Manager ................. A Smith .......yes
.... abc123 ........... Intern ..................... B Jones .......
.... def456 ............ Manager ................. A Smith ....... yes
.... def456 ............ Manager ................. C Allen ........
.... ghi789 ............ Proj Mgr .................. C Allen ........ yes
.... ghi789 ............ Proj Mgr................... E Mills ..........
.... ghi789 ............ Manager .................. A Smith ....... yes

Do not allow dupes for ProjectID, ContactsSubID, ContactID or Current if
ContactsSubID =48 or 49 and Current= yes.
Can have multiple ContactsSubID =48 or 49 and Current= null or ""
The current field is only applicable when ContactsSubID =48 or 49

How do I make this work. Tried indexes but I cannot specify ContactsSubID
=48 or 49 in the index.

I really need help!!
deb

 

[Jeff Boyce]

The folks who help here are volunteers. If you don't get an immediate
response, folks may be busy, or working, or may not know the answer or
understand the question.

Since you've posted this three times in fairly short order, you may consider
this urgent ... if you can't wait for the volunteers here, consider hiring
someone...

Good luck (please note: this is a recording ...)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Arvin Meyer [MVP]]

How can I prevend dupes if ContactSubID =48 or 49 and Current=yes?

Sometimes it may take a while to get an answer. Be patient. I did answer
your last post.

 

[June7]

Try this in Where condition of query, careful with the parens:

WHERE ((ContactsSubID = 48 Or ContactsSubID = 49) AND Current = -1) OR
(ContactsSubID <> 48 And ContactsSubID <> 49)

 

[deb]

I do not intend to create dupes. I have issues when trying to post. On
occasion it will not place my post and I must try again. I waited for 1/2
hour before posting the second try. I will try to wait longer next time.

sorry for the inconvenience!!