By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Custom Autonumber

P: 2
i am beginner in microsoft access 2007 and i want to make a custom id feild in some tables.

please make a vba code if possible

I have a table Purchase order , sale order, inventory, sale ...... an so on.
My question is i want to make a custom id .with following format:
Id=PCO-yy-0000. So that, a typical number might look like this Id=PCO-14-0001.

Now. all the tables which i have two common feilds. Id , IdType.
Id = which i want my custom autonumber.
IdType = "PCO" default value in Purchase order, Table.
"SCO" default value in Sale order , Table.
"XXX" default value in any table what i want.

My question is what is the best way to automatically increment this number so that the yy numbers change with the calendar and the last four digits increment by one in every new record. And then, how do I reset the last four digits on the change of calendar year?

Thank you very much for any assistance!
Mar 4 '14 #1

✓ answered by NeoPa

In case it makes it easier to follow I've copied something here which I used elsewhere :
  1. Identify the common part of the key.
  2. Use this sub-string to find the maximum value already stored that matches that sub-string.
  3. Using Nz() & DMax(), which allows us to specify the desired value to be used in the case that no records currently match the criteria, get the current maximum numeric value.
  4. Increment (add 1 to) this.
  5. Use the sub-string and this incremented value to formulate a new key value.

It's similar to my earlier post but fleshes out the details a little. It's done in words rather than code as it's important to get an understanding of what and why, which can often be overlooked with a simple code solution.

This approach can, and often is, utilised in SQL-only solutions.

Share this Question
Share on Google+
5 Replies


P: 8
Do you want the 4 digits to increment seperately for PCOs and SCOs, or for every order?
Mar 4 '14 #2

NeoPa
Expert Mod 15k+
P: 31,494
Fundamentally, you start with the first part of the string that you know you want from the type and the year. With this string (EG. 'PCO-14-'.) you search for the maximum matching value and extract the numeric portion at the end. Using DMax() will give you what you need and it will also return Null if no current records match the pattern. Use Nz() to replace a Null value with zero.

Now you have the required value increment it, format it to the correct number of places then append it to your search string.
Mar 4 '14 #3

NeoPa
Expert Mod 15k+
P: 31,494
In case it makes it easier to follow I've copied something here which I used elsewhere :
  1. Identify the common part of the key.
  2. Use this sub-string to find the maximum value already stored that matches that sub-string.
  3. Using Nz() & DMax(), which allows us to specify the desired value to be used in the case that no records currently match the criteria, get the current maximum numeric value.
  4. Increment (add 1 to) this.
  5. Use the sub-string and this incremented value to formulate a new key value.

It's similar to my earlier post but fleshes out the details a little. It's done in words rather than code as it's important to get an understanding of what and why, which can often be overlooked with a simple code solution.

This approach can, and often is, utilised in SQL-only solutions.
Mar 5 '14 #4

P: 2
Yes. I want seperately increment id for every new order.
Thanks for reply.
Mar 5 '14 #5

NeoPa
Expert Mod 15k+
P: 31,494
I've reset the Best Answer as it was not correct and seems to have been chosen on the basis that it was the only one coded for you. This is not an acceptable basis to select a post as Best Answer.

We here are not a coding service and requests for code are not generally left in the thread. We can, and often do, help with coding, but this generally only happens when you have shown that you've already attempted it yourself first.

Try that, and see what response you get.
Mar 5 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.