Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Number field - check to be sure it's not a duplicate

    ACCESS '07 Form VBA -
    Hi - I'm looking for some help on ways to check data in a field to be sure it's not duplicated. This data entry form has a number field that is filled in by the user.

    few things -
    I would like the entry to be made and when tabbed out, access will check the table to see if it already exists.
    A.)If it does - msgbox - clear it and stay in that field for the user to enter again. These are absolutely positively unique numbers that should never be duplicated. Entries are done by a single user, never more than 1 at a time.
    B.)If it does not tab to the next field
    My issues are
    *what event is best considering it's not saved yet
    *how exactly to type a DCOUNT(or similair) syntax for this number field using AcctTable as the table and PTLU as the field data type Number - i.e. the " and & required.
    *how to cancel the statement if it doesn't exist so that the user can proceed
    *how to clear it if it does so that we can be in that field to enter again

    It is the 2nd to last field in the form. this is a tab through form with alot of required fields. My user tabs to the last field where I have OnExit to open a pop-up form that asks if they want to enter another or exit. I can't change that.

    Right now I do not have the PTLU field set to required in the table, but really should. It can neither be left blank, or duplicated.

    Appreciate any help available!
    Thank You

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Add a Unique index on the column and intercept the error (3022) when a try is made to insert a value that already exists.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This code will do it:
    Code:
    Private Sub PTLU_BeforeUpdate(Cancel As Integer)
       If DCount("*", "AcctTable", "PTLU = " & Me.PTLU) Then
         Cancel = True
         MsgBox "This PTLU Already Exists!Please re-enter!"
       End If
    End Sub
    You should not clear the Textbox; if you do the user may very well enter the same one again!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •