[AccessD] Duplicates on Two Values

Hollis, Virginia hollisvj at pgdp.usec.com
Wed Nov 18 10:13:22 CST 2009


But what do I use on the before or after update to check both fields?
What I have below just checks one field.

 

They can enter the ItemName then after they enter the ItemDesc, it needs
to check if this is a duplicate entry. But it checks it based on both
fields, the name & the description.

 

*************

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

data.

 

Rocky

 

From: Hollis, Virginia 
Sent: Wednesday, November 18, 2009 9:43 AM
To: 'accessD at databaseadvisors.com'
Subject: 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




More information about the AccessD mailing list