Create custom autonumber field

[KLR]

Hi everyone

I need to create a custom autonumber field in my table that is
sequential and based on another fields value.

I have a field which shows a dropdown of years. I now need a separate
number field that commences 40001 if the year selected is 2010, 50001
if 2011 is selected, 60001 if 2012 selected and so on. Thus when a
new record is created for 2010, the record numbering will start at
40001 incrementing by 1 each time a new record is created for 2010.

Is this feasible in Access? BTW this is not my chosen method for
numbering records but has been requested by my users and they will not
budge on this!

 

[Jeff Boyce]

Hopefully you (if not your users) are aware that calling it a "custom
autonumber" field doesn't make it equivalent to an Access Autonumber
field...

Provided that the users (who are NOT always right, but are always the users)
get something that looks like what they think they want, how you get there
(behind the curtain) isn't important (to them), right?

Arvin offered one approach.

Another approach would be to add together the current year and 37,990, then
add a sequence number. That sequence number would need to "re-start" at 1
each year, so your routine that generates the "next" sequence number would
need to find the previous maximum sequence number for the year, then add
one. I believe that approach is what Arvin was suggesting.

To get some ideas on how to build your sequence number procedure, search for
"custom autonumber" on-line, and follow the links Arvin provided.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KLR]

Hopefully you (if not your users) are aware that calling it a "custom
autonumber" field doesn't make it equivalent to an Access Autonumber
field...

Provided that the users (who are NOT always right, but are always the users)
get something that looks like what they think they want, how you get there
(behind the curtain) isn't important (to them), right?

Arvin offered one approach.

Another approach would be to add together the current year and 37,990, then
add a sequence number.  That sequence number would need to "re-start" at 1
each year, so your routine that generates the "next" sequence number would
need to find the previous maximum sequence number for the year, then add
one.  I believe that approach is what Arvin was suggesting.

To get some ideas on how to build your sequence number procedure, search for
"custom autonumber" on-line, and follow the links Arvin provided.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.









- Show quoted text -

Crumbs, thanks guys! Good suggestions and has given me lots to think
about.

 

[a a r o n . k e m p f]

SQL Server allows you to build a tsql function and use that for
defaults on fields

-Aaron

 

[Access Developer]

SQL Server allows you to build a tsql
function and use that for
defaults on fields

True. But, likely not applicable, as most SQL Server users who post here
identify their data store.

Larry

 

[a a r o n . k e m p f]

you can define the function IN ONE PLACE on the database server..
instead of 1,000 different copies of the same function