Partial Order Calculations

[Jenn]

I have both a Purchase Orders database. I have created a receipts form that
allow me to track seperate receipts. I want to be able to receive partial
orders (which is simple enough) the problem that I am facing is that I want
to have a box on my receipts form that shows the qty of the product that is
on back order [BOQty]. I am unsure how to acheive this.
I think what I need is to have the qty received on a specific
[OrderDetailID] to be subtracted from the [QtyOrd]. How do I do this?

 

[BruceM]

Assuming each line item is a separate record in an OrderDetails table, just
add a QtyReceived field. Set its default value to 0, or use
Nz([QtyReceived],0) in calculations if the default value is Null so that you
are not trying to use Null in a calculation. Subtract QtyReceived from
QtyOrd. For instance, in an unbound text box:
= [QtyReceived] - [QtyOrdered]

 

[Ken Sheridan]

Lets assume that you have a table Receipts with columns OrderDetailID and
QtyRecd as the basis for your form. The OrderDetailID column will therefore
be a foreign key referencing the primary key of an OrderDetails table. You
can compute the outstanding quantity for the current item by summing the
QtyRecd values for the current OrderDetailID value and subtracting this from
the QtyOrd value in the row of OrderDetails with matching OrderDetailID
value. So the ControlSource property for an unbound text box on your
receipts form would be:

=DLookup("QtyOrd","OrderDetails", "OrderDetailID = " & [OrderDetailID) –
Nz(DSum("QtyRecd", "Receipts", "OrderDetailID = " & [OrderDetailID ]),0)

The above expression should be entered all as one line in the ControlSource
property; it will almost certainly have been split over several lines by your
newsreader.

Note that this will return the quantity outstanding prior to the current
receipts record being saved. To reflect the current record in the total you
can specifically save the record in the AfterUpdate event procedure of the
QtyRecd control and recalculate the form with:

' save current record
Me.Dirty = False
' recalculate form to reflect current receipt
Me.Recalc

This does assume that the OrderDetailID for the current record has been
entered prior to entering the quantity received. Ideally you should cater
for this not being the case by putting the following in the AfterUpdate event
procedures of both the OrderDetailID and QtyRecd controls:

If Not IsNull(Me.OrderDetailID) And Not IsNull(Me.QtyRecd) Then
' save current record
Me.Dirty = False
' recalculate form to reflect current receipt
Me.Recalc
End If

Ken Sheridan
Stafford, England