[AccessD] Duplicates on Two Values

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

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

Doesn't that check if Item Number and Item Description equal each other?

I assume (and we know what happens when I do that!) that she wants to
prevent
a duplicate entry if the item number and item description already
exist,but allow
an item number with a different description (or misspelled
description) to be entered.



On Wed, Nov 18, 2009 at 12:09 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:
> 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