[AccessD] Duplicates on Two Values

It was simply Air code.
There should actually be a message displayed in prior to the Cancel =
true firing

1. If you don't make an entry, of course the event wont fire
2. It works, try it. Create a new form with two text boxes on it:

Private Sub Text0_BeforeUpdate(Cancel As Integer)
If Nz(Me.Text0, "") <> "" And Me.Text0 = Me.Text2 Then
    MsgBox "the two boxes cannot have the same value"
    Cancel = True
End If
End Sub

Private Sub Text2_BeforeUpdate(Cancel As Integer)
If Nz(Me.Text2, "") <> "" And Me.Text2 = Me.Text0 Then
    MsgBox "the two boxes cannot have the same value"
    Cancel = True
End If
End Sub

That is what the "And" is for ;)

This is for Rocky's condition #3 in his question to Virginia, which
doesn't apply anyway.







On Wed, Nov 18, 2009 at 3:39 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>