Auto incrementing a record counting field

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have converted an existing database to Access which
includes a field with a record number. The record number
in the field the not the same as the number of records
entered into the table as many records had been deleted
in the past.

Without creating a new field that would use the
autonumber feature, how do I get the existing field to
increment as additional records are entered in the table?

Any help is appreciated.

thanks
Steve
 
Steve said:
I have converted an existing database to Access which
includes a field with a record number. The record number
in the field the not the same as the number of records
entered into the table as many records had been deleted
in the past.

Without creating a new field that would use the
autonumber feature, how do I get the existing field to
increment as additional records are entered in the table?
Click to expand...

A fairly simple way is to use code in the BeforeUpdate event of the form used to
insert records.

If IsNull(Me![Record Number]) = True Then
Me![Record Number] = Nz(DMax("[Record Number]", "YourTableName"), 0) + 1
End If

The reason I like to use BeforeUpdate is because it fires a split second before the
record is saved so the chance of multiple users doing data entry grabbing the same
value is greatly reduced. The reason for the Null test is that BeforeUpdate can fire
many times over the life of a record and you only want to assign a number the first
time. Finally, the reason for the Nz() is only so the very first record created in a
new table will get a value of 1. In your case you might be able to dispense with
that since you are starting with a populated table.
 
Back
Top