Table validation on junction table or form-level?

[p2p]

Hi,

I have a junction table creating a many-to-many relationship between Clients
and Campaigns. I want Clients to be able to belong to several different
Campaigns and I want each Campaign to have any or all Clients. But, I only
want the user to be able to add a ClientID to a CampaignID once.

I have another table, called tblCampaignDetails, which tracks status and
notes for each ClientID's participation in a CampaignID. This table has a
one-to-one relationship with the Clients_Campaigns junction table mentioned
above.

Finally, I have a form that allows the user to add a client to a campaign
and enter notes and status for that ClientID with respect to that CampaignID.
When this form is opened, the CampaignID is added to the record through
OpenArgs and then the user is required to select a client.

Ideally, I'd like a form-level rule on the BeforeUpdate of the ClientID
field that checks to see whether the ClientID the user has chosen has already
been added to this Campaign. If it has, I'd like to use a MsgBox to either
direct the user to those notes or choose another ClientID.

So, with that very long-winded explanation, what code do I need to check
whether a ClientID has already been added to a particular CampaignID? I've
been trying variations of DCount, but can't figure out how to count only the
number of ClientIDs where the CampaignID is the same.



Thanks in advance,

Brad

 

[Tom van Stiphout]

On Tue, 11 Dec 2007 04:36:00 -0800, p2p

First off you create a unique index (most likely the PK) over those
two fields in the junction table. Now dups can't occur anymore.
If you want to pretest, you can use a DCount like this:
DCount("ClientID", "tblClientCampaigns", "ClientID=" & Me.txtClientID
& " AND CampaignID=" & Me.txtCampaignID)
If you use this in the Form_BeforeUpdate you could write:
Cancel = (DCount(...) > 0)
If Cancel then MsgBox("Yo! Can't do dups!")

Personally I would not pretest but let the error occur. If needed,
intercept it at the form_error event and convert to a nicer message.

-Tom.