[AccessD] Duplicates on Two Values

I would use either the before or after update events to check for duplicate
data.

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 7:43 AM
To: accessD at databaseadvisors.com
Subject: [AccessD] Duplicates on Two Values

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