[AccessD] Duplicates on Two Values

Virginia,

In the BeforeUpdate of each field, call a routine that validates both fields.  If these are primary key fields (in which case they really shouldn't be entered by the user, right?) they are required but may not have been entered yet, so only validate if both values are entered.

Charlotte Foust 

-----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