Asger Blond
ab-mi at post3.tele.dk
Thu Nov 19 14:31:08 CST 2009
My mistake, sure your code is working. But what I still don't understand is
what the NZ(<Textbox>, "") <> "" is supposed to do.
Consider following 3 scenarios:
1) Entering two different values in the two textboxes.
2) Entering two identical values in the two textboxes.
3) Deleting an entry in one of the textboxes.
Then the expression NZ(Me.TextBox1, "") <> "" And Me.TextBox1 = Me.TextBox2
will evaluate to the following respectively:
1) TRUE AND FALSE --> FALSE
2) TRUE AND TRUE --> TRUE
3) FALSE AND FALSE --> FALSE
Now remove the NZ part of your expression leaving only a test for
Me.TextBox1 = Me.TextBox2. You will get the exact same result.
So I'm still confused why you use the NZ part...
Asger
-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 19. november 2009 01:55
Til: Access Developers discussion and problem solving
Emne: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com