SQL in Access to automate the PO Number

[luzz]

Hi guys!

I have been doing my research online on how do i use SQL in MS Access to automate my PO number but to avail.

Do you guys have any idea on how do i use SQL to automate the PO number?
Please help!!
Thank you so much!

 

[Minty]

If you don't mind possible gaps in the number sequence if anything is deleted, use an autonumber.

If you have to go the route of creating some sort of PO number that is more "synthetic" then search on here - there are links below this thread at the bottom of the forum to similar posts.

 

[luzz]

I want the system to load back to the PO number and locate what is the last PO number then when user click save, it will save the new PO number

 

[Minty]

That doesn't make sense, if you are on the last PO number saving it will simply save that record (the last PO).

If you go to a new record with an autonumber as your PO number, that will automatically be generated once data is entered into the new record.

 

[luzz]

Thank you for your reply. But how do i get started on the autonumber by using SQL or other method?

 

[Minty]

To add an autonumber :https://support.office.com/en-gb/ar...mary-key-96ba683b-7f86-4354-aa25-3849f5e5b641

To "seed" it with a specific starting value ; http://access.mvps.org/access/tables/tbl0005.htm

 

[AccessBlaster]

Can we see an example of your purchase orders? Is it a combination of letters and numbers?

 

[luzz]

My PO number looks like this

eg:

ABC17/001

 

[luzz]

To add an autonumber :https://support.office.com/en-gb/ar...mary-key-96ba683b-7f86-4354-aa25-3849f5e5b641

To "seed" it with a specific starting value ; http://access.mvps.org/access/tables/tbl0005.htm

I tried the Autonumber in the field, but it will not work in my case as i need to start the autonumber at 300+ and not start from 0

 

[Minty]

Did you read the second link ? That explains how to start your number with a value you choose.

 

[luzz]

Did you read the second link ? That explains how to start your number with a value you choose.

Yes, i did. But i dont really understand what it mean

 

[Minty]

It's pretty straight forwards.

Basically you create a copy of your existing table - lets call it tblSeed. You delete all but one record - then in design mode change the autonumber field to a normal number field.

Edit that records ID field to the number you want the autonumber to start at -1.

Create an append query to add the single record in tblSeed to your existing data.
Run the append query - your existing tables autonumber will now be set to value from tblSeed.

Add a new record you should see the new ID at the value you want.

Delete the record you used to perform the seed.

 

[luzz]

It's pretty straight forwards.

Basically you create a copy of your existing table - lets call it tblSeed. You delete all but one record - then in design mode change the autonumber field to a normal number field.

Edit that records ID field to the number you want the autonumber to start at -1.

Create an append query to add the single record in tblSeed to your existing data.
Run the append query - your existing tables autonumber will now be set to value from tblSeed.

Add a new record you should see the new ID at the value you want.

Delete the record you used to perform the seed.

Can I add this into my form?

 

[Minty]

Why would you want to do that?

This is designed as a one off operation to set the Autonumber to a value you want, once you have finished your development.

I think one of us is missing the point of your question here, or not understanding what the primary key / autonumber field is for...

 

[luzz]

Why would you want to do that?

This is designed as a one off operation to set the Autonumber to a value you want, once you have finished your development.

I think one of us is missing the point of your question here, or not understanding what the primary key / autonumber field is for...

I want to show the PO number on my report when i print it, and the PO number must be running so that when user wants to print a report, it should always shows them a difference number. How can i do that?

 

[Minty]

Once you have set up an autonumber field it will, as its name suggests automatically give you a new unique number for each record. Simply display it on your report.

Printing a report won't change the number for the record you are printing?

 

[luzz]

Once you have set up an autonumber field it will, as its name suggests automatically give you a new unique number for each record. Simply display it on your report.

Printing a report won't change the number for the record you are printing?

Hmm lets say I have already created a table for the PO number and i have set it to autonumber fields.

Next, i want the PO number to acts as a field whereby i can search the PO number and it will show all the data that are in this PO in the subform.

Lastly, when i print the report, it will show the PO number on the report.

I hope i did not confuse you. Thank you so much for your help and patience.

 

[Minty]

The PO autonumber is exactly the same as any other piece of field / data in your table, except once created it can't be edited.

You can search on it. You can create a grouped query to pull in all lines on that PO, you can print it on a report.

This is all very rudimentary database functions. I'm not sure I understand your question.

 

[AccessBlaster]

Luzz:

See if this link helps, read Marks answer.

I had a similar situation constructing an Asset Number. You may have to store your data (PO Number) as separate components in a table. The components are then reassemble for human consumption.

HTH

 

[luzz]

Luzz:

See if this link helps, read Marks answer.

I had a similar situation constructing an Asset Number. You may have to store your data (PO Number) as separate components in a table. The components are then reassemble for human consumption.

HTH

for eg; my PO number is ABC17/001
store PO number as separate components in a table means that i have to store ABC in a table then 17/001 in another table?