Creating an Autonumber with a Prefix for different categories while including the Year and Month
Posted on 2006-07-23
I've found possible solutions for problems similar to mine, but not enough to get me where I need to go. What I am looking for is an Incrementing Autonumber including the Month, Year, and a Letter prefix at the beginning of the Autonumber based on the users choice.
For example: A06-05-001, A06-05-002....A06-05-018, A06-05-019
B06-05-001, B06-05-002....B06-05-025, B06-05-026
C06-05-001, C06-05-002....C06-05-056, C06-05-057
"A" = Apples (Category), "B" Bananas (Category), "C" Cherries (Category)
"06" = Current year
"05" = Current month
"001" = Counter incrementing for each category
I want to be able to have the user choose a category from a drop down list in a form so it can apply a prefix to the Autonumber including the year and month. I will need to have each category have its own increment so that if a user chooses category "C", the counter will automatically check the last number used for Category "C" and not the others, and apply the next sequential number. The tricky part for this is I am trying to have the counters reset every month for all categories.
I don’t mind using VBA if it will accomplish this task. Hope this makes sense. Thanks in advance for your help.