Re: GENERATE ERROR FOR UNIQUE FIELD ON FORM

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 02/08/05 

Date: Tue, 08 Feb 2005 21:36:41 +1300

Sammie,

Here's one approach, for code on the BeforeUpdate event of the textboxes
on the "other" form...

  If DCount("*","YourTable","FieldValue='" & Me.ControlValue & "'")>0 Then
        MsgBox "You've already got one of those"
        Cancel = True
  End If
(assumes a text data type) 

-- 
Steve Schapel, Microsoft Access MVP
Sammie wrote:
> I have a form used to enter new records in response to a not-in-list 
> event.  It contains 2 fields which do not allow duplicates.  Niether of 
> these two fields is the primary key.  The problem is that the user 
> doesn't see that they have entered a duplicate until after they have 
> closed the form and they receive an error on the not-in-list event.  How 
> can Access prevent the user from entering a duplicate when they enter 
> the field, so they know exactly why it is not an acceptable entry?  I'm 
> having trouble writing error handling code on my not-in-list procedure, 
> when I think it's the duplicates on the other form that are causing the 
> problem.
> 
> Thanks.
> Sammie

Relevant Pages

  • RE: finding non-duplicated records
    ... Go into Design view. ... Dave Hargis, Microsoft Access MVP ... use the field name or names that would determine if there are duplicates. ... using Access 2003, and I tried using the Find Duplicates query wizard, but it ...
    (microsoft.public.access.queries)
  • RE: finding non-duplicated records
    ... Actually what also worked was "Find unmatched query ... "Klatuu" wrote: ... Dave Hargis, Microsoft Access MVP ... use the field name or names that would determine if there are duplicates. ...
    (microsoft.public.access.queries)
  • Re: Allowing Duplicates
    ... field in the Field Name column beside that, and choose a sort order (it's ... >> Doug Steele, Microsoft Access MVP ... >> (no e-mails, please!) ... >>> to have duplicates because we all enter data in for the same days. ...
    (microsoft.public.access.formscoding)
  • RE: finding non-duplicated records
    ... "Klatuu" wrote: ... use the field name or names that would determine if there are duplicates. ... Dave Hargis, Microsoft Access MVP ... using Access 2003, and I tried using the Find Duplicates query wizard, but it ...
    (microsoft.public.access.queries)
  • GENERATE ERROR FOR UNIQUE FIELD ON FORM
    ... I have a form used to enter new records in response to a not-in-list ... It contains 2 fields which do not allow duplicates. ... having trouble writing error handling code on my not-in-list procedure, ...
    (microsoft.public.access.forms)