SQL in Access to automate the PO Number

[isladogs]

If you want to go down that route you could have 2 new fields in that table
for the ABC & 17/001 components

You could then use both of these as a composite primary key field (instead of your existing one)
OR concatenate (join) them to create the ABC17/001 primary key field

 

[luzz]

If you want to go down that route you could have 2 new fields in that table
for the ABC & 17/001 components

You could then use both of these as a composite primary key field (instead of your existing one)
OR concatenate (join) them to create the ABC17/001 primary key field

I cannot use this as my primary key as there the PO number consist many variables and the line will go on. So i am using ID as my primary key instead and i want to link the PO number to my main table.

I set the ID as my autonumber and the Po number as short text.
I have attached my file below.

 

[AccessBlaster]

Just out of curiosity can you explain the PO makeup. I never seen a PO with a front slash or special characters. See example below.

 

[luzz]

Just out of curiosity can you explain the PO makeup. I never seen a PO with a front slash or special characters. See example below.

Basically,
ABC represent the company
17/ represent the year which is 2017
001 represent the number that i would like it to be increase

 

[isladogs]

I cannot use this as my primary key as there the PO number consist many variables and the line will go on. So i am using ID as my primary key instead and i want to link the PO number to my main table.

I set the ID as my autonumber and the Po number as short text.
I have attached my file below.

Your db doesn't help as you've only included the one value ABC17/001

Basically,
ABC represent the company
17/ represent the year which is 2017
001 represent the number that i would like it to be increase

So use 3 fields Company, Year, PONumber and then concatenate these to form the ID field
It sounds easy enough....

 

[luzz]

Your db doesn't help as you've only included the one value ABC17/001

I want the PO to auto increase so that i will have to manually key in, thus i only key in one PO number

 

[isladogs]

I want the PO to auto increase so that i will have to manually key in, thus i only key in one PO number

You can use code to autoincrement that field.
Similarly you can get the date part from the system date ....

Its really not difficult

 

[luzz]

You can use code to autoincrement that field.
Similarly you can get the date part from the system date ....

Its really not difficult

How can i do that?

 

[Minty]

EDIT: This was in response to the last post on the previous page - sorry.
Yes correct - If I may ask why would you want such an awkward reference. Think of some of the consequences.

A simple example - If you wanted to store the PO as a pdf you couldn't use that reference as its filename , as it contains an illegal filename character. Therefore you would have to call it something else which means storing that as well as your awkward reference.

If your supplier is ABC you will have their information stored in the PO, if you stored the PO's in a folder per supplier code you don't need that reference in the number.

Each PO would have a creation date so the 17 being the year, becomes redundant as you can easily see that in your original table as the creation date of your stored PO.

Don't create considerable extra work for no real gain or reason.

 

[Minty]

How can i do that?

On a form in the on current event something like;

Dim strYourRef as String

strYourRef = Me.YourSupplierCode & format([Me.YourPoDate],"yy") & "/" & Me.YourAutonumber

Me.txtBoxtoDisplayYourCode = strYourRef

 

[luzz]

EDIT: This was in response to the last post on the previous page - sorry.
Yes correct - If I may ask why would you want such an awkward reference. Think of some of the consequences.

A simple example - If you wanted to store the PO as a pdf you couldn't use that reference as its filename , as it contains an illegal filename character. Therefore you would have to call it something else which means storing that as well as your awkward reference.

If your supplier is ABC you will have their information stored in the PO, if you stored the PO's in a folder per supplier code you don't need that reference in the number.

Each PO would have a creation date so the 17 being the year, becomes redundant as you can easily see that in your original table as the creation date of your stored PO.

Don't create considerable extra work for no real gain or reason.

That is the PO number that is created by my company, thus i cannot change it

 

[luzz]

On a form in the on current event something like;

Dim strYourRef as String

strYourRef = Me.YourSupplierCode & format([Me.YourPoDate],"yy") & "/" & Me.YourAutonumber

Me.txtBoxtoDisplayYourCode = strYourRef

Based on your code, i suppose i will need to separate the PO number into 3 fields in the table right?

 

[isladogs]

Building on both Minty's comments and my last email

Based on your code, i suppose i will need to separate the PO number into 3 fields in the table right?

Yes - though the awkward year part could be done on the fly

You need to be more specific about your PO number rules
Lets says you have 3 companies: ABC, DEF, XYZ

Also that the last PO orders for each were:
ABC17/325 (the most recent order done), DEF17/312 and XYZ is a new customer

DEF places a new order today - is the order number DEF17/326 or DEF17/313?

Next XYZ places a new order - is it XYZ17/001 or XYZ17/327?

Assume the final order for 2017 is DEF17/521
Move forward to 01/01/2018
ABC places the first order - is it ABC18/001 or ABC18/522?

What happens when you reach 999?

Sorry but this really is a badly structured PO system you're having to use ...

 

[luzz]

Building on both Minty's comments and my last email



Yes - though the awkward year part could be done on the fly

You need to be more specific about your PO number rules
Lets says you have 3 companies: ABC, DEF, XYZ

Also that the last PO orders for each were:
ABC17/325 (the most recent order done), DEF17/312 and XYZ is a new customer

DEF places a new order today - is the order number DEF17/326 or DEF17/313?

Next XYZ places a new order - is it XYZ17/001 or XYZ17/327?

Assume the final order for 2017 is DEF17/521
Move forward to 01/01/2018
ABC places the first order - is it ABC18/001 or ABC18/522?

What happens when you reach 999?

Sorry but this really is a badly structured PO system you're having to use ...

ABC will be standardize throughout even if it is a new PO
17/ will change when 2017 end so for instance, if 1-Jan- 2018 then the PO will be ABC18/
001 will be the only thing that i want it to increase.
If it reaches 999 then it will become ABC17/1000 and it will just keep going on till next year then reset to ABC18/000 and the whole PO number repeat again

 

[Minty]

You need to speak to your manager and explain that it is a rubbish numbering system.

It may have been lovely when the PO's where created on paper and stored in a folder in the filing cabinet, but as a database reference its completely useless and has a myriad of problems.

You should by now, be able to explain to the why it is no longer viable to use that system, and the large benefits of the new computerized PO system will make needing to store them that way completely redundant.

Sometimes you have to be firm, and not just do it that way "because we always have"...

If they purchased an of the shelf system it wouldn't do it - neither do you have to.

 

[isladogs]

You need to speak to your manager and explain that it is a rubbish numbering system.

It may have been lovely when the PO's where created on paper and stored in a folder in the filing cabinet, but as a database reference its completely useless and has a myriad of problems.

You should by now, be able to explain to the why it is no longer viable to use that system, and the large benefits of the new computerized PO system will make needing to store them that way completely redundant.

Sometimes you have to be firm, and not just do it that way "because we always have"...

If they purchased an of the shelf system it wouldn't do it - neither do you have to.

Exactly what I was getting at ....

But I expect you'll say you have to use it so something like this...

ID is an autonumber - fine

Have these fields in your table

Company Text (3)
YearCode Text(2)
OrderNo Number(Integer)
PONumber Text (15)

The following fields should be populated using code when a new order is saved:
Company
YearCode => Right(Date,2)
OrderNo: DMax("OrderNumber","MyTable")+1
PONumber: [Company] & [YearCode] & "/" & Format([OrderNo],"000")

 

[luzz]

Exactly what I was getting at ....

But I expect you'll say you have to use it so something like this...

ID is an autonumber - fine

Have these fields in your table

Company Text (3)
YearCode Text(2)
OrderNo Number(Integer)
PONumber Text (15)

The following fields should be populated using code when a new order is saved:
Company
YearCode => Right(Date,2)
OrderNo: DMax("OrderNumber","MyTable")+1
PONumber: [Company] & [YearCode] & "/" & Format([OrderNo],"000")

Hey, thankyou
I tried this on my access table and created a query based on the table. It will prompt me to enter in the value for the order number as well as running number. I am not sure if i am doing it correctly thus i have also attached my file above. Can you help me check? Thank you so much.

 

[Minty]

I have corrected your query, and uploaded it.

However I really recommend you stop what you are doing and take some basic access tutorials. https://www.access-programmers.co.uk/forums/showthread.php?t=210173

You were putting your calculations in the criteria of the query. This is a basic building block of how to build a query, that you don't understand.
Once you have done the tutorials and understood the basics I would then revisit your current work, and probably start again, with an better insight to what you are doing.

I won't contribute again until it is more obvious that you have grasped the basics, and aren't diving in to something you don't understand.

 

[AccessBlaster]

Since the company name in the example will always be ABC and the date is derived from the DATE there is no reason to type that info into a table or store it. Also the OP will need VBA in his before insert so that Dmax can have a starting point of his own choosing.

I just played with minty's query not the vba or Dmax

 

[Minty]

@AccessBlaster - It was by no means a real world solution. I wouldn't do it this way, I was just trying to demonstrate a technique the OP might pick up and learn from

I stored a query demonstrating using the Primary Key as the numeric part of the PO, as this would make much more sense, rather than the convoluted yearly resetting number they seems insistent on using.

And obviously you wouldn't store any of this separately, as you have said it can all be pulled from the PO header itself.