Numbering records in seqence

[Guest]

I would like to learn how to number records in sequence. I know that auto
number will do this, but that is not its purpose. And it does not always
work. I am not proficient in visual basic, although I can cut and paste and
somewhat understand what some of the code does.

 

[Rick Brandt]

I would like to learn how to number records in sequence. I know that auto
number will do this, but that is not its purpose. And it does not always
work. I am not proficient in visual basic, although I can cut and paste and
somewhat understand what some of the code does.

Example if you insert records using a form and have a numeric field named [ID].
Using the Form's BeforeUpdate event.

If IsNull(Me![ID]) Then
Me![ID] = Nz(DMax("[ID]", "TableName"),0) + 1
End If

This would be reliable for a moderate number of users entering records
simultaneously. Beyond that a separate "Next Value" table can be used. Just
ask here if you think you need that method (which is a bit more involved).