Automatic Entry Numbering

  • Thread starter Thread starter Erin Lashbrook
  • Start date Start date
E

Erin Lashbrook

I am trying to create a field in a table where the ID tag
is automatically generated when an entry is created.

The way I have it set up, you use a form linked to the
table "Journal" to create a new entry. WHen the new entry
is submitted, I want the date field (CreationDate) to post
with the current date, and the ID field (ID) to
automatically be one number after the previous entry in
the table.

I am using the following code, which I think should work,
but for some reason it won't add past 1:

----
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![ID]) = True Then
Me![ID] = Nz(DMax("[ID]", "Journal", 0) + 1)
Me![CreationDate] = Now()
End If
End Sub
 
Before you blithely change a field to an Autonumber type you should go to
http://mbps.org/access and look up 'autonumber'. It will give valid reasons
for using an autonumber as well as instances and reasons you should not..
Many people don't realize that an autonumber field is NOT guaranteed to
provide an unbroken sequence.

While you're there, poke around. The site has a wealth of information,

hth
--
-Larry-
--

AJ Raiber said:
Turn your ID field into an Autonumber field. That will
cause it to pull up the next number automatically upon
entering a new record.

In the date field put "=Date()" in the Default Value and
it will assume the current date but still alow you to
change it if you need to. To keep it from being changed
changed the field to locked.

HTH.

AJ

-----Original Message-----
I am trying to create a field in a table where the ID tag
is automatically generated when an entry is created.

The way I have it set up, you use a form linked to the
table "Journal" to create a new entry. WHen the new entry
is submitted, I want the date field (CreationDate) to post
with the current date, and the ID field (ID) to
automatically be one number after the previous entry in
the table.

I am using the following code, which I think should work,
but for some reason it won't add past 1:

----
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![ID]) = True Then
Me![ID] = Nz(DMax("[ID]", "Journal", 0) + 1)
Me![CreationDate] = Now()
End If
End Sub
Click to expand...
Click to expand...
 
Back
Top