How can I automate a default value?

  • Thread starter Thread starter Tony623
  • Start date Start date
T

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?
 
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
 
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
 
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
 
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?
Click to expand...

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.
 
Back
Top