Purchase Order Form and PO Numbers (1 Viewer)

I've a database for managing PO's.

I followed the attached to help with pre-defining the PO numbers

The PO_number field is an autonumber and the text box property "Format" has "17IT"000.
Whenever a new PO number is required it set's each PO number with a preceding value of 17IT000 and will increment.

This has been working great for 2017 PO's, but now I need 2018 PO numbers to commence with 18.

If I change the text box format value from 17IT000 to 18IT000, all existing PO's begin with 18, I need to keep them as 17IT001 etc etc.

Any Ideas ??

You need to store the '18' portion in your table. However, we need more information about how you want your future PO numbers to work. Specifically what happens to the number portion when a new year starts.

We get a lot of people here who think numbers are mystical magical entities. Each imbued with meaning and created specifically for a purpose. If you are one of them, and would like your 2018 PO numbers to reset to begin with 001, then you need to rethink your PO numbering system. You will need to no longer use an autonumber and instead build a custom function that looks at the prior PO number and Year and determines the next PO number to use.

If however you are fine with the number portion of your PO numbers starting right where they left off in 2017, then you can continue to use your system.

So, if the last PO of 2017 was 381. Do you want the first PO of 2018 to be 382 or 0 (or 1)? Or do you even care? Answer that and we can help you set up your PO numbering system for years to come.

You may get more insight on Autonumbers from this article.

gerryp said:

I've a database for managing PO's.

I followed the attached to help with pre-defining the PO numbers

The PO_number field is an autonumber and the text box property "Format" has "17IT"000.
Whenever a new PO number is required it set's each PO number with a preceding value of 17IT000 and will increment.

This has been working great for 2017 PO's, but now I need 2018 PO numbers to commence with 18.

If I change the text box format value from 17IT000 to 18IT000, all existing PO's begin with 18, I need to keep them as 17IT001 etc etc.

Any Ideas ??

Click to expand...

Preferentially I'd just use the autonumber or manually update the mask. A po is supposed to be arbitrary. They are not mystical [emoji6].

If you want to do it still, and as plog pointed out, need a little more info. How are you creating new po's currently? Via form or?

Sent from my SM-G950U using Tapatalk

plog said:

You need to store the '18' portion in your table. However, we need more information about how you want your future PO numbers to work. Specifically what happens to the number portion when a new year starts.

We get a lot of people here who think numbers are mystical magical entities. Each imbued with meaning and created specifically for a purpose. If you are one of them, and would like your 2018 PO numbers to reset to begin with 001, then you need to rethink your PO numbering system. You will need to no longer use an autonumber and instead build a custom function that looks at the prior PO number and Year and determines the next PO number to use.

If however you are fine with the number portion of your PO numbers starting right where they left off in 2017, then you can continue to use your system.

So, if the last PO of 2017 was 381. Do you want the first PO of 2018 to be 382 or 0 (or 1)? Or do you even care? Answer that and we can help you set up your PO numbering system for years to come.

Click to expand...

Many Thanks plog.

Ideally I'd rather the first PO of 2018 to be "18IT001" and increment accordingly.

Then you need to scrap your autonumber field.

First and foremost, make a backup of your database and work in a copy of it. I would create a new PO field in your PO table and copy the existing PO numbers (the ones generated from the autonumber) into it. Its datatype should be a number.

Next you will need a field to store the year portion of the PO. You may already have this. If you have a date field that you capture when a PO is created then you can use it. You would simply use the Year function on that date to extract the year portion. If you don't have such a date field I would consider adding one. If not you can simply add a number field to capture the Year. With these 2 fields you now have all the data needed to show a PO number and determine the 'next' one to use.

Finally, you will need to search this forum (or wait a few hours for someone who needs to be a code hero) for code to generate the next number in a numerical sequence. Search for terms like 'custom autonumber', 'custom id', etc. to find such code. It's been done many times on this forum and involves using a DMax to get the highest number currently in use.

Rock and roll version attached.

plog said:

...


Finally, you will need to search this forum (or wait a few hours for someone who needs to be a code hero) for code to generate the next number in a numerical sequence....

Click to expand...

Snarky are we I had the time and am a nice guy.

...and am a nice guy.

Click to expand...

Are you? What did gerry learn from you?

I see no explanation in your post nor instructions for using it and your code has absolutely no comments to instruct anyone as to what it's doing.

You provided that code for yourself not for gerry.

plog said:

Are you? What did gerry learn from you?

I see no explanation in your post nor instructions for using it and your code has absolutely no comments to instruct anyone as to what it's doing.

You provided that code for yourself not for gerry.

Click to expand...

Lol

Sent from my SM-G950U using Tapatalk