Autonumber generating duplicate numbers HELP

[Lisa Perry]

Hello.



There is a table in my Database that generates an autonumber for each new exam that is scheduled and it is creating the same autonumber over and over again…I looked at the properties of the table and it does allow for duplicates and will not allow me to change that to Not allow – how can I correct this so it creates a new number and not an existing one? Help please.


Thank you.

[JoeM]

Is it set up as a "Autonumber" Data Type?
Is it set to be the Primary Key?
If you look at the Properties of this field, what are the following Properties set to?
- Field Size
- New Values
- Indexed
Are you letting the system determine the number, or are you trying to calculate it?
What value is it "stuck" at?

[rpeare]

I'm guessing your field is set to number with a default value rather than an autonumber field. post a screen shot of the table in question (design view) with the field in question highlighted and perhaps a table view of that same table (hide any sensitive information)

[Lisa Perry]

Hello,

Thank you for getting back to me.

Yes, it is an Autonumber Data type

No it was not set as the primary key in the table, no field was...this is a database I inherited.

This is what is in the Properties for the Booking ID- AutoNumber:

Field Size - Long Integer
New Values - Increment
Format - blank
Caption - blank
Indexed - Yes (Duplicates OK) and it won't allow me to change this
Smart Tags - blank
Text Align - General


I am letting the system determine the number ... it keeps giving me random numbers that already exist in the table - the last number is 3775 so you think the next one would be 3776 but it keeps give me duplicate numbers like 3766 (I have three of these) and 3758 (I have three of these) and many more. I have no idea what the reason is...when data is entered - the user enters through a main form that houses the client data, and they create a new booking ID (exam date for that client) by adding a record to the sub-form which feeds into this table. If I go straight into the table and add a new record, the numbering appears to be correct.

Not sure this helps any.

Thanks again for any help.

[JoeM]

Indexed - Yes (Duplicates OK) and it won't allow me to change this

Maybe because duplicate exist in the table. Try cleaning up/removing all the duplicates, then changing this property.
If you are able to do that, then try making this field the Primary Key?

Regarding your Form entry, are you sure that you are entering new records, and not just updating existing ones over and over again?
Is there any VBA code attached to this form?
Is the Autonumber field being shown on the Form?

[Lisa Perry]

Yes that is probably correct. I will remove those records and then try to change that record to a primary key and not allow for duplicates and then reenter the other data - probably my best bet. I looked at the form properties and don't see anything unusual going on ... under event - code correct? Thanks for the help.

[Lisa Perry]

I just removed all the duplicates and changed the Booking ID to Primary and saved the table. Now, when I go back to the form to input the data I took out - it is still repeating numbers ... the difference is now it won't let me save the data because it sees a duplicate ID number - this is the message....

The changes you requested to the table were not successful because of they would create a duplicate value....change the data in the field or fields that contain duplicate data, remove index or redefine the index...I am lost - it is an autonumber - it does the same thing in the table-creates a duplicate number...I am stumped.

Thoughts?

And, thank you!

[JoeM]

The Form you are entering the data into, is it an an Entry Only form, or does it show existing records too?
What happens if you change to a new record entry form only (change Data Entry property on Form to "Yes")?

You may be better off just trying to recreate this Form from scratch. There may be something about it that is causing issues.
In order for us to debug it any further, I think we would probably need a copy of the database.

[rpeare]

I'd suggest making a copy of the database, putting some bogus information into it to show the problem then zip it and upload it, without actually seeing your database and it's mechanisms it's very hard to tell.

[Lisa Perry]

Hello,

Yes, let me make a copy and I will put it up, there is quite a bit of information in it so I need to change that - hopefully by the end of today. Thank you for your help with this...

Lisa

[Lisa Perry]

Yes it is an Entry form - and it does show all past records for that client...

Question, the same things happens in the table itself ... so wouldn't the problem be in the table and not in the form?

[JoeM]

Yes it is an Entry form - and it does show all past records for that client...

I think you are getting a little tripped up by the lingo. A "Data Entry" form only allows new data to be entered. You cannot view/edit existing data in it if it is set up to be "Data Entry", per the property I mentioned.

Question, the same things happens in the table itself ... so wouldn't the problem be in the table and not in the form?

Yes, that would seem to suggest that the issue is with the table itself. As was discussed, I think we would need to see the table. I think we have gone about as far as we can without being able to see the table for ourselves.

Shot in the dark, but have you tried "Compacting and Repairing" the database and see if that makes any difference? You could have a corruption issue going (and that may or may not fix it).

[Lisa Perry]

I did misunderstand, my apologies...it is not a data entry only form. You can see all the client history on this form.

I will try the compact and repair and let you know. I will put a copy of the DB up later today if I have time to change the data so you can see - again, thank you for all the help.

[Lisa Perry]

I did misunderstand, my apologies...it is not a data entry only form. You can see all the client history on this form.

I will try the compact and repair and let you know. I will put a copy of the DB up later today if I have time to change the data so you can see - again, thank you for all the help.

I tried compact and repair it did not work. I decided to create a new database and export the table that I created yesterday (without the duplicates and Booking ID as primary key) along with the forms and other tables it uses...well, it works like a charm - no duplicates! So there has to be something in that DB that is doing it. Like I said I inherited this DB and it has alot of objects we never use in it (queries, reports, forms) ... I am going to put everyhthing we currently use into the new Database and see how it goes...thoughts?

[JoeM]

I decided to create a new database and export the table that I created yesterday (without the duplicates and Booking ID as primary key) along with the forms and other tables it uses...well, it works like a charm - no duplicates! So there has to be something in that DB that is doing it.

Very often, that is a sign of database corruption.

Usually step 1 in dealing with corrupted databases is to run the Compact & Repair. If that doesn't work, step 2 is to import all the objects to a new database like you did.