Trouble Incrementing Indexed Fields

[KerryL]

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&quot.

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.

Kerry

 

[Tim1]

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,

Tim

 

[Aivars]

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&quot=0 then
Me.IndexedField =1
else
Me.IndexedField=DMax("IndexedField", "MyTable&quot+1
endif
End Sub

Aivars

 

[Tim1]

Hi Aivars,

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

 

[KerryL]

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.

Kerry

 

[KerryL]

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

Kerry