Data Entry Form and Auto Number ID

[frank]

Hello,
My database has a Users Table and I am making a New User Form.
The Primary Key is UserID and it is an AutoNumber field.
I would like to have the form open on a new record with a new UserID.
The form opens on a new record OK, but it opens with a duplicate ID in
the User ID field.
Is there something I need to do so the new record will open with the
Auto Number properly incremented?
It is a simple bound form with dataentry set to true.

My table already has UserID values up to 555.
Each time I debug my form to add a new record, the Auto number field
increments from my last debug session but it is still low enough to
cause a duplicate value in the table.
For example, I have debugged 10 times.
The next time I test the form, a new record will be created with ID
11.
The next time with ID 12, etc...
These are still duplicates because the table already has UserIDs up to
555.
It should be pretty easy to make a Data Entry form with an Auto Number
PK, right?
Thanks

 

[John W. Vinson]

Hello,
My database has a Users Table and I am making a New User Form.
The Primary Key is UserID and it is an AutoNumber field.
I would like to have the form open on a new record with a new UserID.
The form opens on a new record OK, but it opens with a duplicate ID in
the User ID field.
Is there something I need to do so the new record will open with the
Auto Number properly incremented?
It is a simple bound form with dataentry set to true.

My table already has UserID values up to 555.
Each time I debug my form to add a new record, the Auto number field
increments from my last debug session but it is still low enough to
cause a duplicate value in the table.
For example, I have debugged 10 times.
The next time I test the form, a new record will be created with ID
11.
The next time with ID 12, etc...
These are still duplicates because the table already has UserIDs up to
555.
It should be pretty easy to make a Data Entry form with an Auto Number
PK, right?
Thanks

How are you "debugging" the form? The autonumber is assigned by the table (the
database engine), not by the form, so unless your debug is running some code
which is interfering, this should work.

What version of Access are you using? Is it up to date with all service
patches? There have been bugs in Access that cause reassignment of used
autonumbers, but I believe that all versions now have this bug fixed (if you
have the fixes installed of course!)
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[frank]

How are you "debugging" the form? The autonumber is assigned by the table(the
database engine), not by the form, so unless your debug is running some code
which is interfering, this should work.

What version of Access are you using? Is it up to date with all service
patches? There have been bugs in Access that cause reassignment of used
autonumbers, but I believe that all versions now have this bug fixed (if you
have the fixes installed of course!)
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

SOLVED
You're quite right that it has nothing to do with the form.
If I open the table in datasheet view and add a new record it also
created a duplicate ID.
I am using 2003 SP3.

By debugging I just mean that I am switching to Form View which
creates a new record.
I deleted all the records in the table and loaded it with SQL again.
It began at the lower index it had been using.
It is now working properly.
I'm not sure how it got our of synch, but clearing and reloading the
table solved it.

Thanks for your assistance.