[AccessD] Duplicates on Two Values

David,
Just curious: what's your using NZ in the BeforeUpdate event supposed to do?
The NZ function is checking for NULL, and:
1. If you don't make an entry (leaving the field NULL or bypassing an
existing value) then per definition the BeforeUpdate event won't fire.
2. If you make an entry (making the field NOT NULL) or delete an existing
entry (making the field NULL) then the event will fire but per definition
the expression NZ(Field,"")<>"" will always evaluate to FALSE making the
whole IF condition FALSE.
Net sum, as I see it: Your BeforeUpdate procedure won't do anything
whatsoever howsoever.
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 18. november 2009 22:27
Til: Access Developers discussion and problem solving
Emne: Re: [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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com