AutoNumber

[mrathi]

Hi, I have a field in my table that is set to AutoNumber and the index is set to "NO".
I add rows to the table through a form. However, when i try to add rows, sometimes, the autonumber field duplicates the values. Can I set the autonumber field in the code. I tried getting the max function and then adding 1, but I get an wrror of "type mismatch" or "invalid collection of items". Any help will be appreciated.

Thanks

 

[popalu]

Open the table in design and change the NO value to Yes (No Duplicates). This will only allow unique numbers in the table, so no duplicates. This is done automatically and does not need any user intervention.

Access is a relational database so a deleted number cannot be used again to retain the referentail integrity of the database. (Think that sounds right)

 

[mrathi]

If i do that, then when my form tries to add rows, I get the following error:

The changes you requested to the table were not successful bcoz they would create duplicate values in the index. change the data in the field, remove the index...

Please help

 

[RoyVidar]

If you are using 2003, this is a known flaw/bug of the Autonumber. This Microsoft KB article describes it, and some workarounds BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003.

Roy-Vidar

 

[mrathi]

I am using Access 2000. however, i still followed the steps in the article, but does not help.

Thanks

 

[RoyVidar]

Then I'm suspecting you either have some corruption somewhere. Try the usual compact repair, Microsofts JetComp utility... This link Corrupt Microsoft Access MDBs FAQ provides a lot of information on corruption, links to how to resolve, and the mentioned JetComp utility.

Else I suspect your situation might be one of the situations where Autonumbers can't be used. See for instance MichaelRed's faq faq700-184 for one way of dealing with it. If the autonumbers won't work, I suspect DMax will prove unreliable too.

Roy-Vidar