Automatic Row Numbering W/O AutoNumber

[pushrodengine via AccessMonster.com]

Is there a way to make an automatic database table row numbering system
without using AutoNumber?

I’m trying to make an automatic row numbering system with a certain format
that automatically increases for every record entered.

Ex. The row numbering will be in this format “0000-00â€. The first 4 digits
will be the current row number in sequential order, after will be “-“, and
the last two digits will be the current year “07â€.

Thanks

 

[Douglas J. Steele]

Recognize that you're trying to stuff 2 separate pieces of information into
a single field, something that's actually a violation of relational database
theory.

You should store two separate fields and, in fact, if you're capturing
something like creation date, you shouldn't be storing the year redundantly.

In the BeforeInsert event of your form, put logic to determine the next
number to use:

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!Id = Nz(DMax("Id", "MyTable", "WhatYear = " & Year(Date)), 0) + 1

End Sub

You'd then create a query that concatenates the 2 fields together, and use
the query wherever you would otherwise have used the table:

MyId: Id & "-" & Right(CStr(WhatYear),2)

 

[Roger Carlson]

First of all, there is no way to do your own "autonumbering" in the table
itself. You'd have to use a form. On my website
(www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this.

Secondly, there is no real advantage (and several disadvantages) to storing
record identifiers like this. Having only 4 digits will limit you to 9999
records per year and while that might sound like a lot now, someday it might
not. Also, storing the number with that format will make it a lot more
difficult to calculate. You'd be better off just storing the four digit
portion and a real date value. Then anytime you needed that number, you
could concatenate the last two digits to of the date to the other field.

Thirdly, you might want to ask yourself exactly WHY you need a record
identifier in this format. Is it an Order number of some sort?
Historically, order numbers (and PO numbers) were printed sequentially to
make sure that one was not stolen from the middle of the printed pad. It
was also used for filing purposes. But think. Is this REALLY necessary in
a computer based system? Something to think about.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[pushrodengine via AccessMonster.com]

Is there a way to Automatically number the row in a 4 digit format "0000"
without the "-yy" on the end and without using AutoNumber function?

Recognize that you're trying to stuff 2 separate pieces of information into
a single field, something that's actually a violation of relational database
theory.

You should store two separate fields and, in fact, if you're capturing
something like creation date, you shouldn't be storing the year redundantly.

In the BeforeInsert event of your form, put logic to determine the next
number to use:

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!Id = Nz(DMax("Id", "MyTable", "WhatYear = " & Year(Date)), 0) + 1

End Sub

You'd then create a query that concatenates the 2 fields together, and use
the query wherever you would otherwise have used the table:

MyId: Id & "-" & Right(CStr(WhatYear),2)

Is there a way to make an automatic database table row numbering system
without using AutoNumber?

[quoted text clipped - 7 lines]

Thanks

 

[BruceM]

Doug's solution assumes you will be restarting the numbering each year.
Also, you can format the number to include leading zeros. From Doug's
example:
MyId: Format(Id,"0000") & "-" & Right(CStr(WhatYear),2)

 

[pushrodengine via AccessMonster.com]

Where do I enter: MyId: Format(Id,"0000") & "-" & Right(CStr(WhatYear),2) ?

Thanks

Doug's solution assumes you will be restarting the numbering each year.
Also, you can format the number to include leading zeros. From Doug's
example:
MyId: Format(Id,"0000") & "-" & Right(CStr(WhatYear),2)

Is there a way to make an automatic database table row numbering system
without using AutoNumber?

[quoted text clipped - 7 lines]

Thanks

 

[pietlinden]

Where do I enter: MyId: Format(Id,"0000") & "-" & Right(CStr(WhatYear),2) ?

Thanks

Doug's solution assumes you will be restarting the numbering each year.
Also, you can format the number to include leading zeros. From Doug's
example:
MyId: Format(Id,"0000") & "-" & Right(CStr(WhatYear),2)

Is there a way to make an automatic database table row numbering system
without using AutoNumber?

[quoted text clipped - 7 lines]

Thanks

set it in the form.