[AccessD] Duplicates on Two Values

"If the event-firing field is NULL then the event just won't fire."

Of course! (dope-slap) 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Wednesday, November 18, 2009 4:22 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Duplicates on Two Values

Rocky,
No, that's not necessary. If the event-firing field is NULL then the event
just won't fire. And if the event-firing field is NOT NULL and the other
field is NULL then the expression will evaluate to FALSE, making the entry
acceptable. Even if you delete an existing entry from one of the fields
making it NULL then both the expression NULL = NOT NULL and the expression
NULL = NULL will per definition evaluate to FALSE, making the entry
acceptable.
If NULLs are not wanted in a field then the best way to prevent this is by
making the field required in the table's design.
And as said I would prefer to enforce the non-duplicates constraint as a
table-level check constraint: In design view of the table I would use
View|Properties and set Validation Rule to [ItemName]<>[ItemDesc]
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Rocky Smolin
Sendt: 19. november 2009 00:31
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Duplicates on Two Values

Asger:

Should there also be a check before the Fieldx.Value = Fieldy.Value to see
if both fields have values (Not IsNull) and if both do not have values then
Exit Sub?

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Wednesday, November 18, 2009 12:10 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Duplicates on Two Values

Virginia,
Did you try Rocky's suggestion? Just tried it - works fine.
Private Sub Field1_BeforeUpdate(Cancel As Integer)
    If Field1.Value = Field2.Value Then
        MsgBox "Dublicate values for Field1 and Field2 not allowed"
        Cancel = True
    End If
End Sub
Private Sub Field2_BeforeUpdate(Cancel As Integer)
    If Field1.Value = Field2.Value Then
        MsgBox "Dublicate values for Field1 and Field2 not allowed"
        Cancel = True
    End If
End Sub
Don't know why this shouldn't do for you - perhaps I'm missing something?
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Rocky Smolin
Sendt: 18. november 2009 18:33
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] Duplicates on Two Values

In the before update event of each field check if field1 = field2.  If yes,
then message to user delete contents of the field being checked.  

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Wednesday, November 18, 2009 8:47 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Duplicates on Two Values

Number 3. ItemName can be repeated & so can the ItemDesc just not together
in the same record. 

 

For example, Paper comes in several different kinds, it can be Legal Paper,
Lined Paper, Construction Paper, etc. I just don't want them entering Legal
Paper twice. If it has already been entered, I would like it to take them to
that record, but at this point just letting them know Legal Paper has been
entered would be ok.

 

I am having a problem checking ItemName AND ItemDesc, especially since they
are text fields.

 

ItemName: Paper

ItemDesc: Legal

 

ItemName: Paper

ItemDesc: Lined

 

ItemName: Book

ItemDesc: Legal

 

ItemName: Book

ItemDesc: Lined 

 

********

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?

 

Rocky

 

--
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


-- 
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