[AccessD] Duplicates on Two Values

Virginia

1) to ensure that no two distinct records have the same data in the two 
fields
     ID    ItemName    ItemDesc
     1    red    blue
     2    red    blue
    set a unique index using both fields in the index ...this will gen an 
error when record two is entered
    use the error to gen a custom dialog

2) to ensure that a distinct record does not have the same data in both 
fields set a table level validation check constraint
    [ItemName] <> [ItemDesc]


zero code and it works in the background however the data in those two 
fields are edited

hth

William




--------------------------------------------------
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
Sent: Wednesday, November 18, 2009 4:44 PM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Duplicates on Two Values

> Do NOT use the two fields as a PK!
>
> Just create an additional  unique index in the table on the two fields.
>
> -- 
> Stuart
>
> On 18 Nov 2009 at 9:43, Hollis, Virginia wrote:
>
>> I need to prevent duplicates based on two text field entries. I asked
>> something like this awhile back & want to use something similar on
>> another form except base it on two fields. The ItemName & ItemDesc
>> (these are Text fields). I set the ItemName & ItemDesc as primary keys -
>> thought that would work. But I still can't get it to say - 'hey you
>> already entered this item, you can't add it again'. Also, should they be
>> primary keys or is there a better way for that too?
>>
>>
>>
>>   Dim OXK As String
>>
>>    Dim stLinkCriteria As String
>>
>>    Dim rsc As DAO.Recordset
>>
>>
>>
>>     Set rsc = Me.RecordsetClone
>>
>>
>>
>> If Not IsNull(Me.ItemName) Then
>>
>>     OXK = Me.ItemName.Value (Need to have ItemDesc too)
>>
>>     stLinkCriteria = "[ItemName]=" & "'" & OXK & "'"
>>
>>     'Check for duplicate
>>
>>    If DCount("ItemName", "tbl_ItemMain", (AND ItemDesc) _
>>
>>              stLinkCriteria) > 0 Then
>>
>>         'Undo duplicate entry
>>
>>        Me.Undo
>>
>>         'Message box warning of duplication
>>
>>       If MsgBox("Item " _
>>
>>             & OXK & " has already been entered." _
>>
>>             & vbCr & vbCr & "Would you like to view to the record?", _
>>
>>                    vbYesNo, "Duplicate Record") = vbYes Then
>>
>>               rsc.FindFirst stLinkCriteria
>>
>>              Me.Bookmark = rsc.Bookmark
>>
>>                 Else
>>
>>             Exit Sub
>>
>>     End If
>>
>>         End If
>>
>> End If
>>
>> Set rsc = Nothing
>>
>> -- 
>> 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
>