Auto Numbering

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig B. - 28 Dec 2004 15:21 GMT

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?

Reply to this Message

Rick B - 28 Dec 2004 15:28 GMT

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

Rick 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?

Reply to this Message

Kevin - 28 Jan 2005 21:31 GMT

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.

> Have you tried searching for your answer? This is asked and answered
> multiple times a day!
[quoted text clipped - 6 lines]
> > The starting number will be 05-001. Is there a formula I can write? Or is
> > there another way to achieve this?

Reply to this Message

John Nurick - 29 Jan 2005 17:07 GMT

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).

>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
[quoted text clipped - 10 lines]
>> > The starting number will be 05-001. Is there a formula I can write? Or is
>> > there another way to achieve this?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Rick Brandt - 28 Dec 2004 15:33 GMT

> 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")

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Scott Schindler - 29 Dec 2004 16:51 GMT

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
[quoted text clipped - 27 lines]
>
> =Format(RecordDate,"yy-") & Format(RecordID,"000")

Reply to this Message

Rick Brandt - 29 Dec 2004 17:02 GMT

> 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?

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.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message