Auto Numbering

[Craig B.]

I am creating a data base and want to add a formula to my table so that when
a form is filled out an automatic number is assigned to each new document.
The starting number will be 05-001. Is there a formula I can write? Or is
there another way to achieve this?

 

[Rick B]

Have you tried searching for your answer? This is asked and answered
multiple times a day!


Rick B

 

[Rick Brandt]

I am creating a data base and want to add a formula to my table so
that when a form is filled out an automatic number is assigned to
each new document. The starting number will be 05-001. Is there a
formula I can write? Or is there another way to achieve this?

Assuming that the "05" portion is the year it would be best to store this in two
fields. One that holds the record creation date [RecordDate], and one that
holds the ordinal value [RecordID]. Then it becomes relatively easy (in a form)
to assign the appropriate values.

For RecordDate you simply set the default value property to either Date() or
Now() depending on whether you want to capture the time as well as the date. I
would recommend Now() because even if you don't think you need to know the exact
time of record creation, that requirement could change down the road and you
will already have it. This default value could be set at the table level.

For RecordID you need a code routine that will find the highest existing ordinal
value for the records created in the current year and then add one to it. I
would use the BeforeUpdate event of the form with the following code...

If IsNull(Me.RecordID) = True Then
Me.RecordID = Nz(DMax("RecordID", "TableName", "Year(RecordDate) =
Year(Date)"), 0) + 1
End If

You need to make sure that RecordID has no default setting in either the table
design or the form.

Now, *for display* on your forms and reports you use an expression of...

=Format(RecordDate,"yy-") & Format(RecordID,"000")

 

[Scott Schindler]

Ok, how do you USE a BeforeUpdate event? I see the before update line when I
look at the code. How do I use it?

I am creating a data base and want to add a formula to my table so
that when a form is filled out an automatic number is assigned to
each new document. The starting number will be 05-001. Is there a
formula I can write? Or is there another way to achieve this?

Assuming that the "05" portion is the year it would be best to store this in two
fields. One that holds the record creation date [RecordDate], and one that
holds the ordinal value [RecordID]. Then it becomes relatively easy (in a form)
to assign the appropriate values.

For RecordDate you simply set the default value property to either Date() or
Now() depending on whether you want to capture the time as well as the date. I
would recommend Now() because even if you don't think you need to know the exact
time of record creation, that requirement could change down the road and you
will already have it. This default value could be set at the table level.

For RecordID you need a code routine that will find the highest existing ordinal
value for the records created in the current year and then add one to it. I
would use the BeforeUpdate event of the form with the following code...

If IsNull(Me.RecordID) = True Then
Me.RecordID = Nz(DMax("RecordID", "TableName", "Year(RecordDate) =
Year(Date)"), 0) + 1
End If

You need to make sure that RecordID has no default setting in either the table
design or the form.

Now, *for display* on your forms and reports you use an expression of...

=Format(RecordDate,"yy-") & Format(RecordID,"000")

 

[Rick Brandt]

Ok, how do you USE a BeforeUpdate event? I see the before update
line when I look at the code. How do I use it?

"Rick Brandt" wrote:

You find the BeforeUpdate box in the [Events] tab of the property sheet and
enter "[Event Procedure]" in that box (without the quotes). Then you click on
the build button [...] to the right. That will take you to the VBA code editor
window. Access will have already created the lines that define the beginning
and end of the procedure. You simply place your code between those lines.

You have to make sure before doing the above that the property sheet is
displaying properties for the Form object and not for some control on the form
or some section of the form. Click on the small gray sqaure in the upper right
of the form to ensure this.

 

[Kevin]

Can anyone tell me if it is possible to insert auto numbering into an Excel
spreadsheet? I know this is possible with Access, however the people that
employ me do not feel that Access is neccessary at this time.

 

[John Nurick]

In Excel, you can do this by applying a custom number format to the
column. This should work:
"005-"000;;

Then type 1 in the first cell (e.g. A2) and use a formula to increment
it in subsequent cells (e.g. =A2+1).