conditional autonumber? (1 Viewer)

Hello,

In my workplace I have to work with Access and it is quite new to me, so would like to ask for your help.

I would like to add a field to my table where I could have autonumbering. The tricky part is, that I really need that the autonumbering would start again from one each year, and would really like to store it in my table, as later it will be needed for a calculated field. Now I know, that I could enter the numbers manually, however we would like to reduce the possibility of creating a place for errors. I have played with the idea of creating a VBA function for it, however I can not figure out how can I apply any costum function to create a calculated field in a table (if it is possible at all).

Thank you in advance

use DMax()+1 to generate the autonumber.
since it resets every year, you also need a year field (number) to your table.
dmax function is not available in macro so you need to build a function to generate.
you also need a form for your table so you can call the function.

add code to your form's BeforeInsert() event:

Most of the time the need for this "yearly reset" is historic and not needed if you have any date field that you can reference in your top level record data.

Have a search on here for "resetting yearly numbering" and you will find a plethora of examples and some of the issues surrounding implementing it.

Hi. Are you sure you need to store this number? Is it not possible to find this number just using a query?

Thank you!

I am so happy that it is working, that I can not thank you enough!

Hi. Glad to hear you got it sorted out. Good luck with your project.

ReginaF,

A suggestion, use an actual autonumber in your table AND have your custom number for display. You would use the real autonumber (never seen by users) to link together child records and such.