Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: Test for duplicate index values

    I am creating a data input form where two of the fields are non duplicates indexes (not my key). When entering data on the form, the duplicate status is not tested until the user attempts to move to a new record at which point, they get the generic error "cannot move to the specified record" (if using the next button on the form) or "changes cannot be made... duplicate index value..." if using the built in navigation buttons. Either way it does not inform the user which field is causing the issue, just that they cannot save their record. The users of this form are not familar with access and are easily confused when things like this happen.

    I would like to do one of two things.

    1) Verify that the field is not a duplicate value before continuing on to the next field in the form and provide them with a friendly and helpful error message

    2) Check for duplicate fields before the entire record is saved but inform the user which field caused the problem and provide them with a chance to fix it.

    The data table is rather large (200,000 + records) and growing, so I am trying to avoid running a query against the table every time they enter data. They enter many records a day and the speed is essential. At the end of the fiscal year, the data table is moved to a historical table to keep the size down but they still grow large during the year.

    Any thoughts?
    Steve.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you should be able to declare any index or multiple indexes as unique from table design. which will inform user at the system level if they try to enter duplicate values for that certain field.
    ghozy.

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    They are declared as unique in the table design. The issue is how the user is informed of the attempt to add duplicate values for a field. There are two fields on the form that cannot have duplicate records. It does not inform the user of the duplicates until they attempt to save the record at which time the warning does not specify which field the duplicate attempt came from.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I believe you can trap the index error in Form's On Error event and display a more user friendly message instead of complicated system message.
    ghozy.

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Unhappy

    I tried doing that, 2 issues. The form on error event does not trigger with the button I provided to go to the next record (essential because without it the user will have to stop typing and use the mouse to change to next entry). Also, the error trapped still does not say which field caused it, just that duplicate index values would be created.

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    the only other way I can think of is checking the duplicates. if these two fields are indexed, process wouldn't take that long I believe. which it is what access doing behind the scenes when checking unique indexes.
    ghozy.

Posting Permissions

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