Form to allow "Part Delivery" of an order

[Cowlers]

Hi All,

I have a form which contains the Order Header and a subform which is a continuous form and contains the Order Detail. This is working fine to allow users to enter orders onto the system. I've also got a "Complete" button which allows the user to complete an order, i.e. this closes off the order and runs a append query to update/downdate stock depending upon the type of order they are raising. My problem is that from time to time we have to be able to accept a "Part Order". This is where we take partial delivery of an order so the complete button will not do what we need it to do as it only allows the user to complete the whole order at once. What I would like to do is to allow the user to enter the qty for each line that is on a particular delivery and then to allow them to press a button to accept this delivery, in doing so this would then call up the append queries but with the partial qtys instead of the full order qtys and depending upon whether the qtys result in the whole order being completed or not would change the job status to reflect this. What I am struggling with is how to allow the user to enter these qtys without automatically updating the table linked to the form as I don't want the underlying table to be updated with these values until the user has pressed the button to accept part-delivery. If the part-delivery turns out to be all we will be receiving I want to be able to "downdate" the order qty to reflect what has already been delivered (we are dealing with an extruder so depending on what they can get out of a billet of material this is a common issue). I also want to be able to go the other way and if we are over delivered (for the same reason as above) that I can automatically update the order qty this way as well.

So far I believe I need an additional column for my order Lines table to show "DeliveredToDate" so that I have a record of the status of the order and if it is a partially completed or a fully completed order.

Please could you provide me with some pointers about how I should approach this problem? If you need any further information please do not hesitate to ask!

Best regards,

Dan

 

[Cowlers]

I think breaking down my problem that I need to try to find a way that I can have an additional field in my subform for which the values are only stored temporarily while the main form record is active. I can see that temporary tables are something that can be done but I need to be able to have a temporary table as part of the query that forms my subform. If I could do this then when I press my button to "Part deliver" my order I can then run the code to update the new column in my order lines based upon these temporary values.

Is this even possible or am I going about this in completely the wrong way??

Many thanks,

Dan

 

[gemma-the-husky]

it isn't a form you need (well it is, but that isn't the problem)

If your system needs to allow for partial deliveries, then you need another table (probably 2) to manage the deliveries. This needs some careful data analysis I think.

so you get

customer
order
orderlines

and then

ordercalloff
ordercallofflines


so for each order, you will have 1 or more call offs, and each call off will have 1 or more lines.


----
you might be able to get round this by accumulating a "delivered so far" total against each order line - but if you want it traceable, the data structure needs changing.

 

[jdraw]

See the free data model and business rules for Customers and Partial Deliveries/Shipments

 

[Cowlers]

Thank you very much for your reply!

The way I have it working at the moment is that there is a button against the order that "completes" the order, this runs an append query to a "Stock" table which lists all the stock transactions. If I create a new table for calloff as you suggest would I include this in the subform in the "orders" area so that against each line you can enter the qty or would I need to create a call-off "order" from my order form which would pull the data from the original order and build it that way?

I am just trying to get my head around what I need to do to get this working from the system I already have...

Many thanks,

Dan

 

[Cowlers]

Thanks JDraw,

I understand how it would work now, I will get playing and come back if I need any further assistance, many thanks!

Dan

 

[Cowlers]

Ok, I'm 90% there I think,

I have a button on my Orders form which brings up the Deliveries form, what I want to happen is that on my deliveries form there is a subform which automatically populates with all the lines from the original order and then an empty box at the end which will be the Delivery Lines qty. This will allow the user to enter the qty delivered for each delivery. I can do this if I don't link the Delivery Head to the Delivery lines (subform) but I don't know how to get it so that when I add a new Delivery record the full list of lines is still there.

I need to link the Delivery Header with the delivery lines in the form as otherwise I can't bring up the records of what was entered for each Delivery at a later date, I also need to link the Order ID from the Orders form with the Delivery Lines as otherwise when I open the form I will see the lines for every order in the system instead of just the active one.

Please could you help me work out what I need to do to show both?

Many thanks,

Dan

 

[Cowlers]

Ok, I think I've resolved this. Instead of calling up all records automatically I am instead putting the records into a drop-down on the subform so that the user selects the relevant order line and then enters the figure for the particular delivery. I need to refine this a little to stop the user from being able to select the order line if it has already been selected and also to prevent the user from selecting the line if it has already been completed but am sure I'll get there in the end!

Many thanks,

Dan