Purchase order setup problem

[daneo2k3]

Hi im having real problems trying to setup a purchase order section of my database. I need a basic form that one member off staff fills in, it needs them to fill in the product number, description quantity, price on the form and i also need a box that calculates the quantity x price field, then when that form is complete, the MD of the company needs to be able to view the details of it and somehow authorize the order to be processed. so, the member of staff then, and only then, will be able to print or email the order to be processed. but there will need to be lots of purchase orders created each with an individual purchase order number, and the order has to be authorized before it can be printed or emailed? i know this may sound confusing but any help is much appreciatted as i am stuck! cheers dane

 

[Len Boorman]

few thoughts

1) Total cost maybe only needed by authoriser and when printing order.

2) In the table put an authorised flag (Yes/No) Default is No

MD opens all requests where authorised =No, (Also sees qty*price). If okay change No to Yes

3) Users print all orders where authorised =Yes.

Probably need a few other bits like a printed flag otherwise you will print all authorised every time. Again a Yes/No. Default No.

Will one person print all Authorised or each person print their own.

Print select query then has parameters where authorised =Yes and User =[Please Enter Name type of thing] and Printed =No.

Run an update query after printing
Update Printed =Yes where Authorised =Yes and User =[As before]

To make it a bit better you could have a combo box with users names so that could be a selectable parameter.

Do not forget that sometimes printers totally screw up a print so you will need some sort of reprint option

HTH

len B

 

[daneo2k3]

ok sounds a bit confusing as im only an access amateur, its only one person who prints the data out, i need it all to work on forms and using buttons, in other words real user friendly not to see the main access screen if you understand?

 

[Len Boorman]

That's okay

Main form with buttons to open various other forms

Form to add Order request
Form to Authorise request (behind password form)
Form to Print Orders

Enquiry form
Reprint Form

Start simple and build. Have you a form to add a purchase request. Do that bit first and then move on from there.

Len B

 

[daneo2k3]

i have a form that can be filled in but i need a new blank form to come up, when the user clicks on a button but at the moment when you click on the button the form comes up with the previous orders on it. also it saves the orders in the same table so i need some way of splitting the orders by the order numbers so they could be viewed after they have been printed out. cheers for your help dane

 

[Len Boorman]

Your problem is the way that you are opening the form.

On the on click property of the button you are using to open the form put the following

DoCmd.OpenForm "exact form Name", acNormal, , , acFormAdd
DoCmd.Maximize

This will open the form in add mode and also maximise it

if you use the tab key to go from field to field when yopu get to the end the data will be saved and you can enter another data set


Off home very soon. Back in the morning

Len B

 

[daneo2k3]

ok thanks only problem is each time you open it to enter a new purchase order its just going to add it to the old ones and not separate it i need a new form, with its individual purchase order number for each order made??

 

[Len Boorman]

There is no reason why you cannot add an order number to each new order or use an existing order number. You need to explain to me the rules under which an order number is allocated.

You do not need to separate new orders from old orders. Orders are orders. They may have states.

Unapproved
Approved
Printed
Not Printed.

Refer back to earlier conversation.

Can you send me a copy of your relationship diagram

Order systems are not simple and can be as complicated as you wish with delivery tracking and Invoice matching to mention just 2.

Len B

 

[daneo2k3]

No, what i mean about separating the orders is that all the products ordered are saved in one table so you cant tell what products are from which orders. The order numbers are just typed in by the person who authorises it. i have just two tables relating to the form one table, purchase orders has the following fields Item no (autonumber, primary key), Vendor name, part no, quantity, description, price, recieved (tick box). The other table has vendors name and addresses, so that in the purchase order the vendor name can be selected from a drop down list. The recieved box on purchase order form needs to be only visible on the form after its been authorised. How can i view the diferent purchase orders after they have been processed? cheers dane

 

[Len Boorman]

You say that the person that authorises types in the purchase order number. Into which table ?. The two tables that you mention and give the fields for do not mention order number.

I think that you need to re-examine you design. You are repeating Part Number and Part Description in a table. This indicates that the design is not normalised.

Is the addition of the purchase order number an indication of approval ?.

Len B

 

[daneo2k3]

Yes it is but its just a text field added onto the form header so i cant use that to group my orders according to the order number can i? i think i have got the design wrong. Do you have any suggestions??

 

[Len Boorman]

tbl_purchase_orders
Vendor_ID
Part no (Foreigh Key)
Quantity (Integer
Price (Currency)
Received (tick box)
P_O_Number (Text)
Printed (Tick box)
Request_Date (Date/Time) default set to Now()
maybe also
Purchase Requestor


There is no primary key for this table since it is purely a listing


tbl_Vendor
Vendor_ID (Autonumber) Primary Key
Vendor_Name Indexed No duplicates
addresses.....

Do you have two different vendors with same name. If you do the index will not work properly. Possible use Vandoe Name and first address line as unique index to avaod duplicating a vendor.

tbl_Part
Part_No (Primary key)
Part_Description


Think that these tables give all the data elements you need.

The various actions like
Add purchase request
Authorise Purchase
Receive Goods
Enquiry by Vendor
Enquiry by Part
Enquiry by P_O_Number

will all use subsets of this data

Len B

 

[neileg]

Mmmm...

If your orders can contain more than one item, then Len's structure is not enough. You will need a table that holds the purchase order header, and another to hold the individual lines in the order. These might look like this:

tblOrderHeader
OrdID PK autonumber
VendorID FK
OrdDate
OrderNo
etc

tblOrderLines
LineID PK autonumber
OrdID FK from tblOrderHeader
PartNo FK
Quantity
Price
etc

The Northwind sample database supplied with Access is all about orders and suppliers. You should have a look a this.

 

[daneo2k3]

ok ive got these but how do i get so that only the MD can authorize it and the member of staff can only print it out after its been authorised?
cheers dane

 

[Len Boorman]

Neil

Yup agree entirely. Was keeping that complication for later but good thing to get it on the agenda.

Dane

Tou mentioned earlier that the authoriser adds the order number so on your main form you have a button marked Authorise Purchases. This opens a password screen and putting in the correct password opens a continuous form where all purchase requests without a Purchase Order Number are shown. Authorisation is actioned by adding a PO number.

At this point in time the Printed field still says No (Default value when entering request)

So User can select all Requests where P_O_Number is not null and Purchase_Requestor = "Name"

Normally my practice is to then bring up a list of what has been selected and give an option to print. I bring the report up inprint preview mode and set the On Close Property of the report to do a number of things. Firstly it runs an update query that sets the Printed flag to Yes where Requestor ="Name" and Printed=No
and then closes the form that displayed the selection.

Check out Neil's suggestion as well.

Len

 

[neileg]

Yo're going to have to provide some sort of password system to ensure only the MD has access to the authorisation field. You could use Access security, but I would use a form to ask for a password and then open the form to add the authorisation. Don't allow any other access to that field.

 

[daneo2k3]

yer later i was going to setup some security on the database so different users have different levels off access.

 

[daneo2k3]

i have the basic tables set out now. how do i link them into a form im guessing i have to run a query on it as this will be how i get the total field (quantityxprice) but dont no how to get the query to work

 

[Len Boorman]

It is good practice to use a query as the source for all forms and reports.

Basically you are going to use Select queries for most of the forms. So select the query tab then New and then design view. select the tables you need and then the fields from the tables.

I assume you have already made the links in the Relationship display

Then start a New form and base the form on the query. You can then edit the form to substitute combo boxes where required.

The button on the Main Form to open the various forms will control the mode in which the form opens.

Len B

 

[daneo2k3]

right ive got the query done for the form. how do i change the setting though for the form which the MD sees so he can tick the tick boxes and enter a PO number? this help is really good thanks guys dane