Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trouble Incrementing Indexed Fields

Trouble Incrementing Indexed Fields

Trouble Incrementing Indexed Fields

I'm using a database tracks company credit card usage by employees. One form, called Staff Maintenance, allows me to add or edit employee data, which updates the employee record in the Staff Table.

The key field in the Staff Table is StaffID, which is supposed to increment by +1 each time a new employee is added via the Staff Maintenance form. However, when I attempt to add an employee I receive an error message stating that the key field cannot contain a null.

I tried setting the key field default value to "0" but then when I try and add a new record I receive an error message stating that I'm attempting to create a record using a duplicate index value (the "0").

How can I have Access automatically increment the key field (Staff ID) by +1 each time a new record is add via the Staff Maintenance form? (For instance, there are 1050 records in the table, so the next Staff ID should be 1051.)

Thanks in advance for any help.


RE: Trouble Incrementing Indexed Fields

Hi Kerry,

If you go into the table design screen for your employee table, you should be able to set the field to be autonumber, this will do what you require.

HOWEVER, you cannot modify an existing table to be autonumber, so what you need to do is:

copy your existing table into a new table
delete all the data from your old table
modify the fieldtype for the employee ID to be an autonumber
insert the data back into this table from the copy that you made

You will now have a table that will autonumber your employees, increasing by one each time.

Hope this helps,


RE: Trouble Incrementing Indexed Fields

Tim, you aren't right!
Autonumber is excellent until one user works with DB , but  lot of problems proceed if the system provide data export and import between different DB. In such case autonumber is absolutely unusable.

Kerry, solve your problem following:

Private Sub Form_BeforeInsert(Cancel As Integer)
    if Dcount("IndexedField", "MyTable")=0 then
        Me.IndexedField =1
        Me.IndexedField=DMax("IndexedField", "MyTable")+1
End Sub


RE: Trouble Incrementing Indexed Fields

Hi Aivars,

I will be sure to tell our system that it doesn't work !!!

RE: Trouble Incrementing Indexed Fields

I don't anticipate having to import or export data to/from different databases so I'm hoping the autonumber feature will work okay.  Thanks to both of you for your help.  I'm going to try and fix this problem today.


RE: Trouble Incrementing Indexed Fields

Reloading the table after changing the Field Type worked perfectly. Thanks Tim!


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close