Auto Numbering or Typing my own Number

[Mark10101010]

How do I make an ID number display the next avalible number when you get
ready to make a new entry and then have the ability to overwrite it to create
my own without having duplicates?

Anyhelp with this would be greatly appreciated.

Thanks,

Mark

 

[Allen Browne]

If you use a Number (not AutoNumber) field, you could use the BeforeInsert
event of the form to look up the maximum value so far, and assign one more.
Something like this:
Me.ID = Nz(DMax("ID", "Table1"),0) + 1

The trouble is that 2 users adding records at the same time *will* be given
the same number sometimes, because of the delay between whe
Form_BeforeInsert occurs and when the record is saved. But then, if you are
giving the users the chance to choose their own number, you already have the
problem that they could choose an existing number.

 

[BruceM]

To add to what Allen has written, here is a link to a method for creating
numbers in a multi-user environment:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
I expect you could customize the error handling to generate an error message
if the user tries to use a number that is already in use, and ask the user
to try again. It's hard to see why you would want the user to guess about a
unique number, but I expect error handling could address that.