Error Message – Duplicate Values (2003 (11.8204.8202))

Topic

New Reply

I have users who has been using this database for 2 months. I personnally used it around May 10-13 to drop in quite a bit of data and had no problems. We are now encountering the attached error message. I don’t understand why it is just occuring. Atttached is the error message w/ the field names and field properties for your review and any suggestions. Thanks!!

Reply | Quote

Replies

Do you have a query or VBA code that assigns a specific value to the AutoNumber field VisitID?
If not, does the table have another unique index than VisitID?

Reply | Quote

No. But to make doubly sure exactly where should I look to determine this? I have tblDailys07_08; tblEmployee; and a lookup table for Code

Reply | Quote

Open the main table in design view. If you don’t see the Indexes window, select View | Indexes or click the Indexes button on the Table Design toolbar.
You will see at least one index. If you click in an index in the list, you can inspect its properties in the lower pane of the Indexes window. Unique is one of the properties; if it is set to Yes, the index doesn’t allow duplicate values.

Reply | Quote

Attached is what I’m finding.

Reply | Quote

Thanks – that excludes the possibility of an additional unique index.

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

1) See attached. It appears I kept getting the indexing error message because it was recycling the VisitID (19150 – 19161). Each time I got the error message I would exit the database and then go back in. When I tried again it would recycle the next VisitID number even though it is supposed to be unique and an autonumber.

Once I got pass VisitID 19161 I didn’t get the message again.

Any ideas as to why did a field w/ data type of autonumber recycle the autonumber especially when it is the primary key?

2) In trying to figure out the indexing issue I notice that someone in erroy has input future dates ( 08/08/08 and 05/08/09) as the date of visit. How do I check this upon input and flash an error message to person keying in the data?

Reply | Quote

1) The Jet Engine sometimes becomes confused about autonumbers. If it happens again, you can reset the AutoNumber seed, for example using a utility by Mark Liquorman – see Liquorman Utilities (shareware).

2) Open the table in design view.
Click in the VisitDate field.
Click in the Validation Rule property in the lower half of the window.
Enter the following rule: <=Date()
Click in the Validation Message property.
Enter a message to be displayed if the user enters an invalid date, for example The visit date cannot be in the future.
Save and close the table. Access will offer to check existing records.
The validation rule will fire when the user enters or modifies a visit date.

Reply | Quote

Thanks!!!

What does the ampersand lt in the validation rule you shared rule mean? I enterred and tested it. However, when I tried to correct the date Icontinue to get the same error message.

Reply | Quote

If you read my reply in the e-mail notification, the expression may have been mangled. I have attached a picture of what it should look like. It means: less than or equal to the current date, i.e. on or before today.

Reply | Quote