Purchase Order Auto Numbering

Hi people,

I'm creating a Purchase Order template in excel and would like it to have a new order number appear everytime you start a new order. Any ideas how I may go about this? I will be storing it as a template on a shared area so that users can open it from anywhere and then save the order to a central folder with all the purchase orders in. Is there a way of having it so when you click save it will automatically take you to a folder depending on the order number? Could be tricky i guess.

Many thanks for your help.

cmf

Hey,
Could you just clarify exactly how you want it to work?

Do you want it to automatically find out what order number to use, and if so where would it be looking?

Also are you asking for how the order number could determine where to save the thing? So an order number of 1....... would be saved in a folder called "1", 2...... in folder "2"......that kind of thing?

Michael.

I created a Purchase Order system that requires the technician to fill out a PR(Purchase Requisition) Form and submit it to the Maintenance Supervisor. He then reviews the order and approves it and sends an email to the Purchaser to place the order. It uses 3 files, a template, an excel database and a PO)Purchase Order) form. Of course it could be done with only one file. But this way it keeps people who should not be in the database file out.

I have included all of the files in a zipfile located at http://www.geocities.com/whispering_...est/PRTest.zip it is over the size limit of this forum. Create a folder called "C:\PRTest" and unzip them there.

Install the "WZTEMPLT.XLA" by going to a Tools- Add-ins and browse to its location and select it. This xla is from Microsoft and came with earlier version of MS Office, I think Office 97 or 2000

The tech opens the DP Purchase Req.xlt file and inserts the data. This file is read only Password "1", The file it opens will not be .xlt but an .xls file based upon the template. You can edit the location of the files in the Data/Template WEizard pull down. It is a 5 step wizard. Other than the file location and the routing slip, it is all set to go.

When he saves the file, it will bring up a message box to create a new record or continue without updating. Obvious answer. :-)

We also have some other requirements for our PO's so that is the use o f the Task Codes

Since the file is read only, when it comes time to save, just cancel it. Also the file has a notify button to notify the Supervisor that he has a PR to review. In case they forget, in step 5 of the Template wizard, you can add a Routing slip that will send the same information.

Edit the Notify macro for the email addresses and the message
The Supervisor gets the email with a copy of the PR as an attachement and a lik to the database file.

The supervisor opens the database and places his his name in the approved box. and clocks Notify Purchasing button.

When Finance opens the database, and clicks create PO, then a 3rd file is opened and sice our PO's are based upon some info including the date and the only difference between PO# on same date is the last digit, which is inserted manually.

Or finance uses a seperate PO Log to record some of theis information. But the PR Database could have the PO form as one of the sheets and reduce the number of actual files and then allow the Process sheet as the PO log and it is easy enough to add a column for the PO numbers and make them autosave, too.

Hope this helps a little

cmf,

Wondering if you had a chance to look at how I did my purchasing and was able to access the files from my website. If so, please reply, so I can delete them.

Bud