Using DLookup/DCount to control duplicates

[cope]

Hi

Hopefully someone can help me out with a bit of code I am finding tricky to get right.

I am trying to put in a control measure which won't allow the user to accidentally add a client to areferral more than once. The simplified relationship structure reads:

tblClient 1-------n tblClientReferral n---------1 tblReferral

I am as a far as using a dlookup which correctly gives the error message, but only if it is the first client added to the tblClientReferral table. If the hypothetical situation arises in which the user adds a client, then adds a second client to the referral and accidentally adds the second clientn twice, the Dlookup is not picking this up.

I suppose the solution is using a dlookup which has the criteria saying if the referralID = 'this' AND the clientID = 'this' Then 'do this'. But as far as I am aware you cannt use AND in the criteria?? atleast, it hasn't worked when I tried. A snippet of the vba code used is below:

Dim objClient As Object
Set objClient = Forms![frmAddClient]![cboClientID]
 
If (DLookup("clientID", "tblClientReferral", _
    "referralID = Forms![frmReferral]![referralID]")) = objClient Then
    MsgBox "Client already added"
    GoTo ExitSub
Else

 

[pr2-eugin]

You can use AND in a DLookUp.. Check the General usage of DLookUp..

 

[CJ_London]

you can use AND in a DLookup criteria - but your existing criteria is not quite right

you have

"referralID = Forms![frmReferral]![referralID]"

it should be

"referralID = " & Forms![frmReferral]![referralID]

to include the AND it would be something like this

"referralID = " & Forms![frmReferral]![referralID] & " AND ClientID = " & Forms![frmAddClient]![cboClientID]

Not sure why you need to use an object - both forms would need to be open anyway for the above to work

 

[CJ_London]

Another thing you can do is use SQL to create a unique index across two fields which prevents the user adding a duplicate record - this will save you having the VBA at all.

ALTER TABLE tblClientReferral
ADD CONSTRAINT DupInd1 UNIQUE (clientID,ReferalID)

And to remove

 ALTER TABLE tblClientReferral
DROP INDEX DupInd1

If you want to try this, suggest make a copy of your table and run the above, changing names to suit. DupInd1 is just a name so you can choose something more meaningful if you require - just avoid spaces and non alphanumeric characters (but underscore is OK)

To do this in the table designer, click on the indexes tab under the design tab. My guess is you will have already got an index for ClientID, if not create one then go to the next line (you can insert one if required by rightclicking, leave the indexname column field blank and add the ReferralID - see attached

 

[cope]

to include the AND it would be something like this

Quote:
"referralID = " & Forms![frmReferral]![referralID] & " AND ClientID = " & Forms![frmAddClient]![cboClientID]

Not something like this, it would be exactly this. It was a DCount function that was needed, with the use of AND. I think I need to look in to the syntax used in DLookup/DCount as this is were I was tripped up.

I like the thinking with the SQL approach, a good suggestion and something I never considered.

CJ_London any chance you could develop on this comment:

Not sure why you need to use an object - both forms would need to be open anyway for the above to work

Something tells me I'm about to learn something new about declaring objects.

Thanks both for the assistance in solving the problem.

 

[CJ_London]

CJ_London any chance you could develop on this comment:


Quote:
Not sure why you need to use an object - both forms would need to be open anyway for the above to work

Nothing I can add really, Forms![frmAddClient]![cboClientID] is an object (a control object in the frmAddClient.Controls collection which in turn is in the Forms.Objects collection)

In the context in which you seem to be using it you can just as easily refer to the object directly, which you are doing when you assign it to objClient.

Doing it your way can save some typing in the sense that objClient has less characters than Forms![frmAddClient]![cboClientID] but you can just as easily use a long variable.

I would normally use objects to be passed as a variable to a function or sub e.g.

Sub DoSomething (ctrl as control)
    msgbox "The backcolor of this control is " & ctrl.backcolor
End Function

then to call

DoSomething(myControl)

There are plenty of other times you would use an object - such as when referring to an Excel or Outlook object for example. Or if you have a class module you would typically set an object to it e.g.

dim CT as object
Set CT=Crypt ' where Crypt is a class object