Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need a Duplicate Value Prompt Message (Access 2002 SP3)

    I am a novice to access. I am trying to receive a prompt message when a duplicate value is entered into a field. I have a table (Tiffany) and a form (Tiffany1) with a field (StoreID). The StoreID is the primary key. When a duplicate number is entered, it is not entered in the table. This is great, but no message prompted to let anyone know this unless they look. I also have a validation rule in the table (Like "S????"). I tried to enter a formula in the filed property of the form, but I receive a syntax error. The help menu in Access offered an example which I tried to apply as follows: =DLookUp("Tiffany","StoreID","StoreID = Forms!Tiffany1StoreID") Is Null

    Can someone help me?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a Duplicate Value Prompt Message (Access 2002 SP3)

    Welcome to Woody's Lounge.

    If you have made StoreID the primary key of the table, by clicking the Primary Key button in the toolbar, or in the Indexes window, users should get an error message when they try to save a record with a value of StoreID that already occurs in the table:
    <hr>The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.<hr>
    You do not need to set validation rules to prevent duplicate values.

    By the way, the correct syntax for the expression would be

    =IsNull(DLookUp("StoreID","Tiffany","StoreID = Forms!Tiffany1StoreID"))

    Note that the field name is the first argument, and the table name the second argument. To test if a value is null in an expression, use the IsNull function.

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a Duplicate Value Prompt Message (Access 2002 SP3)

    Hans,

    The reason I was using the validation rule was to have instant message occur if the Store ID is duplicated. I tried the new formula, but it still does not work. Now, my validation text show even when the value is unique. I also checked my storeid. It is the primary key, but no message occurs when I close the form. I tried to attach the file in a zip, but file is too big.

    Paul

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a Duplicate Value Prompt Message (Access 2002 SP3)

    See <post#=401925>post 401925</post#> for instructions on how to attach a database to a post.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a Duplicate Value Prompt Message (Access 2002 SP3)

    I have attached the file.

  6. #6
    New Lounger
    Join Date
    Oct 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a Duplicate Value Prompt Message (Access 2002 SP3)

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Thanks Hans. It works great!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a Duplicate Value Prompt Message (Access 2002 SP3)

    I don't think the validation is going to work. There was an error in it, but whatever I tried, I kept getting the validation message. I would use the Before Update event of the StoreID text box or of the form as a whole instead, for example:

    Private Sub StoreID_BeforeUpdate(Cancel As Integer)
    If DCount("*", "Tiffany", "StoreID = Forms!Tiffany1!StoreID") > 0 Then
    MsgBox "Duplicate StoreID. Please enter another StoreID.", vbExclamation
    Cancel = True
    End If
    End Sub

    Remarks:

    You have four indexes on StoreID. Each index takes up space in the database, so you should avoid creating multiple indexes on the same field.
    There is a Word OLE object on your form that causes error messages on my system.
    A picture as background for a form takes up a lot of space.

    I have attached the database with a modified version of the form.

Posting Permissions

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