Calculation question

  • Thread starter Thread starter JenK
  • Start date Start date
J

JenK

I have created a Purchase Order form, which works great but I want to take it
to the next Level.
I want to create a receipt form in which I am able to receive partial orders
(which for the most part is simple enough).
I want to know if there is a way to get the system to match up the
PODetailID's and so be able to subtract the qty received from the qty ordered
to give me the Back order qty?
Any ideas on how I can achieve this goal?
 
Jen,

There's two different ways you can do this, one 'cheap and dirty' and the
other a bit more complicated, but much more powerful.

The cheap and dirty way would be to add a field to your existing PODetail
table for QtyReceived. Then, use an unbound control to display the qty
backordered by subtracting QtyOrdered from QtyReceived. In the OnCurrent
event, enter this line

Me.ctlQtyBackordered = Me.ctlQtyOrdered - Me.ctlQtyRecieved

The trick here is to not let the user edit the QtyRecieved field, but rather
calculate it (say you have a QtyRecieved of 4, and they recieve another 6,
change QtyRecieved to 6 and save, then QtyRecieved becomes 6 instead of 10).
In this case, I would add another unbound control (QtyAdjust maybe?), and in
the form's BeforeUpdate event, enter this line of code:

Me.ctlQtyRecieved = Me.ctlQtyRecieved + Me.ctlQtyAdjust

That way, your QtyRecieved is saved as 10 rather than 6.

Just be sure to add in handling for null values (look up the Nz function for
further info), and keep in mind that unbound controls sometimes require that
you reference then .Text property rather than the plain old control value
(because unbound controls don't generally have a 'value' set, its just text
in the control).

The more complicated, but much more powerful way would be to create a
related table if PODetailReceipts (1-to-many relationship, one PODetail can
have many Receipts).

Then, you can run a sum query on related Reciepts. The major advantage to
doing it this way is that you can also retain information on where, when, who
(or anything else you want) entered the receipt. If you use the 'cheap and
dirty' method, the only information you can track is the qty.

If you want to play around with the second method, try using a subform on
your main PODetails form for the receipts (the wizard will do a fine job of
making sure that only receipts against whatever Detail line you are currently
on is shown)

hope this helps some...
--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Back
Top