[AccessD] Duplicates on Two Values

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

 

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

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com