[AccessD] Duplicates on Two Values

Stuart McLachlan stuart at lexacorp.com.pg
Wed Nov 18 15:44:30 CST 2009


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





More information about the AccessD mailing list