Allowing only one yes for a field, per supplier

[jdgreen]

Here's the issue. We have suppliers identified by codes. We might have multiple contacts for that supplier. I have created a field designating one contact as the PrimaryContact. In order to populate a report later with this contact only one contact per supplier can be designated as the primary contact. So for instance supplier A50 has 9 contacts, only one of those can be the primary. Any help would be greatly appreciated. Thanks.

 

[BlackKnight]

Hi,

I assume you have a way of selecting the manufacturer on your form and then it shows in a subform the associated contacts. I assume you have put a bound checkbox in thwe subform to indicate a primary contact or not.

So...in that checkbox's BeforeUpdate event put this code:

Private Sub ynPrimary_BeforeUpdate(Cancel As Integer)
'Clear all primary contacts for the given manufacturer
'before updating the new primary contact.

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblContact SET ynPrimary=No WHERE lngMfgID=" & lngMfgID

End Sub

Have a good one!
BK

 

[Junior1544]

I'm going to make a couple of assumtions here... let's see how close i am to getting this right.

you have the database setup with contractor info in one table(compony name, address and stuff like that)...

you have contract info in a seperate table that is related to the first.

you made a check box field in the contract info table to designate if the current contract is a primary contract... sounds good... but, there can be only one contract per company, so i would suggest putting the info in the contractor info table...

just an idea... think about it... I also might be way off in your design... I just hope to have given you an idea or two...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

we run into each other again bk junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[BlackKnight]

Hi Juinior1544,

Heheh...birds of a feather flock together or great minds think alike either one is a propos <bg>

Re your message to JDGreen, well, I think he definitely wants more than one contact per mfg but only one can be the primary meaning one contact is the go to person but the others, well, they are only contacted when the go to person is out sick. <g>

Have a good one!
BK