Inventory sales order updating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables. One with sold items and another with on hand inventory. I
want to apply on hand inventory against sold qty in an order by oldest order.
If I apply inventory, I need to reduce on hand inventory and move to next
record and do the same. I would appreciate if anyone had any suggestions how
to set this up.
 
If there is a way to do this with queries, it is beyond my ability with
queries; however, it would not be that difficult in VBA. I don't have time
to write the code for you, but here is the basic flow:

create a query for your sold items table that will order it by order date in
descending order and put a parameter in whatever field the product code is
you want to apply.

Then write a sub to do the work.

open the On Hand table

do while NOT OnHand.EOF
call the query to SoldItems with the Product code as Parameter(0)
do while NOT SoldItems.EOf
If the number of sold items <= on hand items then
subtract sold items from on hand items
sold items = 0
update on hand items
update sold items
sold items.movenext
else
subtract on hand items from sold items
on hand items = 0
update on hand items
update sold items
exit loop 'No more on hand for this product
endif
Loop ' Sold Items
sold items.close
on hand.movenext
loop
Done!
 
Where do I execute this code from. Also, I am not clear with product code
field, do you mean item number field? Thank you for your input and patience,
I am quite the novice.
 
Yes, the Item Number, sorry used the wrong term. Where you put it depends on
how and when you will use it. You could put a command button on a form and
do the coding in the Click Event for the command button. If you are going to
be executing the procedure from more than one place, put it in a module.
 
is the parameter in the item number field used as a counter to move to next
item when loop is finished with first run?
 
No, the looping is controled by the the recordset hitting end of file. Since
the inner loop is controlled by the sold items being filtered by the item
code, all the rows in the sold items recordset will be for the current item
code. When you change item codes in the on hand table, close the sold items
recordset, and reopen it with a new parameter (the new item code), it will be
all the sold item rows for the new item code.
 
Back
Top