Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
AUTONUMBER PROBLEM - STRANGE 1
- Thread starter LBERNARDE
- Start date
Your autonumber field is very likely the primary key for your table.
Access (or any other RDB) will not allow duplicate primary keys (read about tables and their relationships).
If you try to enter a record that has the same primary key as an existing record in the same table you will get the error that you do.
Try entering a record and leave the autonumber field alone. If this doesn't work look at the relationships of your database and see if there are any other joined fields that could create a conflict.
Access (or any other RDB) will not allow duplicate primary keys (read about tables and their relationships).
If you try to enter a record that has the same primary key as an existing record in the same table you will get the error that you do.
Try entering a record and leave the autonumber field alone. If this doesn't work look at the relationships of your database and see if there are any other joined fields that could create a conflict.
- Thread starter
- #4
The compact and repair allowed me to enter one record, I left for the day, the ladies tried to enter more and got the same error. The id field is primary. When I enter the form, I enter in add mode and the field is actually not even visible because it normally just goes to the next number and all is well, now it seems to have regressed back to prior numbers that it thinks it should be on? I tried making a copy of the table and then appending all of the records. Once again I was able to enter one record and now the same error. I don't have any relationships established, so this shouldn't be the problem. I"m really in a jam here, because this stuff needs to get entered and I'm totally at a loss, never dealt with anything of the sort. Help!
-
1
- #5
LBERNARDE,
Do you have all of the most current patches for Access? Without the latest service pack (release?) the autonum feature sometimes craps out in Jet. Check the microsoft site for what you should have for your version.
Hope this helps.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
Do you have all of the most current patches for Access? Without the latest service pack (release?) the autonum feature sometimes craps out in Jet. Check the microsoft site for what you should have for your version.
Hope this helps.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
If you're in a massive hurry right now, you can undo the autonumbering and give each of your data entry people a number to start with (like 2000 for one and 4000 for another, etc.) and have them increment as they enter and then come back and look at the problem when you're not in such a crunch.
I'd check out the patches issue mentioned by Jeremy, and another thread about autonumbering in a multi-user environment... something posted by Cosmo (I'll come back when I find the thread again, unless Jeremy remembers which one it is??).
C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.![[yinyang]](/data/assets/smilies/yinyang.gif "[yinyang] [yinyang]")
I'd check out the patches issue mentioned by Jeremy, and another thread about autonumbering in a multi-user environment... something posted by Cosmo (I'll come back when I find the thread again, unless Jeremy remembers which one it is??).
C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.
I encountered a similar problem when I developed a database and then gave it to the users....worked fine for me, but in a multiuser environment, it crapped out.....
I ended up having to go back and write custo record numberer for the database....
send me an email and I can send you the sample....my address is in my sig block. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
I ended up having to go back and write custo record numberer for the database....
send me an email and I can send you the sample....my address is in my sig block. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
Robert,
Did you check into your Access and Jet patches?
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
Did you check into your Access and Jet patches?
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
- Thread starter
- #9
I found something out on the Microsoft Knowledgebase. Basically, I've been working on some append queries and I was trying to append an autonumber to an autonumber. When I would fix the database by copying the structure and then pasting the data, it would work until I messed up and tryed playing around with that append query. So I finally fixed it by copy structure and pasting, then fixed my append query to not try to append an autonumber to an autonumber, now it seems to be fine. Keeping my fingers crossed. This has run for years with no problems, that's why I couldn't believe it broke all of a sudden, of course it was something I was up to that caused it. Iwill check on patches though!
Thanks!!!
Thanks!!!
LBERNARDE,
Glad you found something. I seem to have replied to the wrong post with my question to Robert, but I am quite glad you found something, as there are a lot of posts on these forums questioning the integrity of autonumbers, and I really don't think there is a problem with autonumbers.
Best of luck.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
Glad you found something. I seem to have replied to the wrong post with my question to Robert, but I am quite glad you found something, as there are a lot of posts on these forums questioning the integrity of autonumbers, and I really don't think there is a problem with autonumbers.
Best of luck.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
Jeremy,
Just to answer your question real quick....
Yeah, I made sure we were up to date. It actually turned out mcuh better for the deaprtment with my custom numbering anyway. The database is used by our customer service reps, and with the custom numbering system, you can easily identify the service rep that took the call without even having to see the report....
But thanks for asking... Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
Just to answer your question real quick....
Yeah, I made sure we were up to date. It actually turned out mcuh better for the deaprtment with my custom numbering anyway. The database is used by our customer service reps, and with the custom numbering system, you can easily identify the service rep that took the call without even having to see the report....
But thanks for asking... Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
MichaelRed
Programmer
see faq700-184
Autonumber in Multiuser (data entry). Is not reliable.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
Autonumber in Multiuser (data entry). Is not reliable.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
MichaelRed,
OK, you and I disagree about this, but now that you're repeating it so emphatically in this thread, I will say it clearly--you are wrong about this. A properly designed Access database, with current patches for Access, will be able to generate autonumber with no problem. If you are having problems with duplicate autonumber it is most likely because you have not properly taken care of your Access environment. It is possible, though, that it is because your file has become corrupted and you have not corrected the situation.
But to tell people that autonumbers do not work in multiuser enviornments is both wrong and damaging.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
OK, you and I disagree about this, but now that you're repeating it so emphatically in this thread, I will say it clearly--you are wrong about this. A properly designed Access database, with current patches for Access, will be able to generate autonumber with no problem. If you are having problems with duplicate autonumber it is most likely because you have not properly taken care of your Access environment. It is possible, though, that it is because your file has become corrupted and you have not corrected the situation.
But to tell people that autonumbers do not work in multiuser enviornments is both wrong and damaging.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access databases since 1995.
MichaelRed
Programmer
Well,
I guess we'll just have to continue to disagree. I 'been there - done that' and base my statements on the observed behaviour, researching the subject, and building a 'fix' It has been a while (access '97?) but I have not seen any ballyhoo about the problem being fixed. The 'issue' arises from the simple fact that MS does not LOCK a table to obtain/generate an autonumber. This (obviously) implies or allows to seperate users to attempt to add records and obtain the same value. At least one of them should get an error from the duplicate value, and this is the most common occurance - but - I have aslo seen instances where other faults occur.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
I guess we'll just have to continue to disagree. I 'been there - done that' and base my statements on the observed behaviour, researching the subject, and building a 'fix' It has been a while (access '97?) but I have not seen any ballyhoo about the problem being fixed. The 'issue' arises from the simple fact that MS does not LOCK a table to obtain/generate an autonumber. This (obviously) implies or allows to seperate users to attempt to add records and obtain the same value. At least one of them should get an error from the duplicate value, and this is the most common occurance - but - I have aslo seen instances where other faults occur.
MichaelRed
m.red@att.net
There is never time to do it right but there is always time to do it over
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
