How can I automate a default value?

[Tony623]

I have an ID number in a field. When new items are entered, I want this ID
number to automatically generate a new number in the sequence--that is, use
the last ID number + 1. How can I do this?

 

[Jeff Boyce]

Search on-line and/or check at mvps.org/access for "custom autonumber" for
examples of routines that do this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ghetto_banjo]

If you make that ID field of type Autonumber, it would do this
automatically.


However, if you don't want to use the autonumber for some reason, you
could do use a DLookup to find the maximum ID value already used and
increment by one. You want to run this code when a new record is
created:

me.IDField = Dlookup("Max([IDNumber])", "TableName") + 1

 

[Jeff Boyce]

MS Access Autonumbers are intended to provide unique row identification.

There's no guarantee they will be (or will remain) "sequential".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have an ID number in a field. When new items are entered, I want this ID
number to automatically generate a new number in the sequence--that is, use
the last ID number + 1. How can I do this?

The simplest way is to do data entry using a Form; in the Form's BeforeInsert
event put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ID = NZ(DMax("[ID]", "tablename")) + 1
End Sub

Somewhat more elaborate solutions may be needed if this is a multiuser
database.