[AccessD] Duplicates on Two Values

I assumed Virgina meant a 4th option, ItemName+ItemDesc cannot be used
if they already exist in the system

Is this correct:

ItemNo  ItemDesc
12345    Battery (allowed as long as 12345+Battery do not already exist)
 2255     Battery (allowed as long as 2255+Battery do not already exist)
12345    Hammer (allowed as long as 12345+Hammer do not already exist)
4567     4567 (not allowed as per 3) below)
cable    cable (Not allowed)
12345   Battery (Not allowed a 2nd time)
12345   Battrey (Allowed as long as 2255+Battrey do not already exist)


If it is simply the 3rd option below, Why couldn't you put the
following in the BeforeUpdate events of txtItemNo and txtItemDesc:


Private Sub txtItemNo_BeforeUpdate(Cancel As Integer)
   If NZ(me.txtItemNo,"") <> "" And me.txtItemNo = me.txtItemDesc
then cancel = true
End Sub

Private Sub txtItemDesc_BeforeUpdate(Cancel As Integer)
   If NZ(me.txtItemDesc,"") <> "" And me.txtItemDesc = me.txtItemNo
then cancel = true
End Sub

This is why I brought up the idea of the PKID in another post.

If this is an entry screen for entering the item no and description
into the sytem, then I would believe not allowing the item number and
description more than once as a pair is correct.

If this is an order form, or something similar, then the ItemNo should
be entered, then in the After Update open a recordset and populate the
PKID and Description.



On Wed, Nov 18, 2009 at 1:00 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:
> David,
> Your assumption (that you may be wrong) is quite right ;-)
> In a previous posting Rocky asked:
>        Is it the case that you have three checks to make?
>        1) Is ItemName already being used in an existing record?
>        2) Is ItemDesc already being used in an existing record?
>        3) Is ItemName = ItemDesc in the current record?
> And Virginia answered:
>        Number 3. ItemName can be repeated & so can the ItemDesc just not
>        together in the same record.
>
> This kind of constraint just isn't as case for a KEY constraint (neither a
> PK constraint nor a UNIQUE constraint). It can only be enforced using either
> 1) a TABLE-LEVEL CHECK constraint (perhaps the best solution because it's
> enforced no matter where you are making your entry), or 2) a PROCEDURAL
> constraint (as suggested by Rocky and me).
> Asger