checking for duplicate records.

[rookie_user1]

Hi i've a form where i can enter in many of the same product names and where products can have many shades but only one color of a kind can be in the shades list.

I need to make sure i can have products that can have the same shade but once the product name is different its ok.. so for example

prod1 can have red, blue, and black

prod2 can have red, blue, and black also...

But if i try to add black to either i get a message saying its already in ..


This is what i have so far.

Private Sub Text3_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("[Shadename]", "tblShades", "[Shadename] ='" _
& Me!Shadename & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!Shadename = ""
End If
End Sub

How can i modify it to check the product name but to also enuse that i can enter in the same shade once i have a different product name

My tables are like this is a m:m relationship..

Products
prodid pk
Productname


Shades
shadeid pk
shadename


prodshade
prodid pk
shadeid pk