RE: Inventory

Tech-Archive recommends: Fix windows errors by optimizing your registry

Jackie,
After all is ansered do I just click on yes for Did this post answer the
Question for you to get credit?
Or is there anything else I need to do.

"Jackie L" wrote:

> Alvin,
> This is quite an undertaking but I will try to help.
>
> First, make sure that your combo box to "produce" the final product has an
> OK/Cancel so that the user has a way out.
>
> You can do an update query to adjust the quantity value in your inventory
> table. If you create the query with the QtyOnHand then update that field to
> [QtyOnHand]-([txtQty]*[QtyperParent]) where QtyPerParent is the quantity
> needed for one finished product. In your example you had one to one but your
> bill should probably include the ability to have a qty for each bill item.
> By using your TransactionID as the criteria in your update query, you can
> link to the Bill of Materials table to release those items from inventory.
> In order to reset your quantity to produce, do another update query to set
> the value to 0 or you can do it in code.
>
> Me.txtQty = 0
>
> after the other queries are run.
>
> There is so much more involved here. What I would be tempted to do is set
> up a Production table with the date, quantity and ItemID so that you could
> recreate history if needed. Then you would not need to reset the txtQty, you
> would just use the quantity input in the Production table. I would also have
> the records "flagged" that they were posted and include that in the process
> with either an update query or code similar to above
> Me.Posted = true
>
> So that records cannot be double posted.
>
> You could also create the production transactions for the bill items so that
> you could track the quantities in and out by production date, otherwise there
> will be no trail.
>
> I know I have barely touched on this process. If you have a stripped down
> version of your database, I would be happy to look at it.
>
>
>
>
>
>
>
> "Alvin" wrote:
>
> > I have a form called Assembly
> > It has a subform called Kits
> > The Assembly form shows my product and the Kits form is where
> > I assign child parts for the products for Assembly, (PP###).
> > The values in the subform " Kits" for the most part won't change but they
> > could.
> > Example is listed below. The KitID is NOT an Auto Number because it is the
> > ID Number for the PP Product that is to be Assembled.
> > I know this is long but here it is.
> > PartID is an Auto Number for each of the child parts.
> > Qty Needed is what it will take to build 1 Product called "PP202"
> > I have a TextBox Named txtQty.
> > Now here is the big question.
> > If I want to build 20 PP202's how can I enter 20 in txtQty on my main Form
> > and click a
> > command Button and it take away from my inventory and make txtQty empty the
> > next Time I Load the form?
> > I need it to subtract from my Inventory Transactions table per the example
> > below
> >
> > 20 red tape
> > 80 .004 grey Latex
> > 20 Brown Tape
> > 20 Aluminum Frames
> >
> > My Inventory Table is named "Inventory Transactions" The Auto Number for it
> > is TransactionID and
> > In it I also have a UnitsSold & UnitsNeeded colum. I'm not sure
> > which one to use and Have no Idea how to get the command button to send the
> > Qty for each Part ID.
> > I have about 75 Different PP Products with child Parts. Please help and
> > thanks in advance.
> >
> > KitID ProductName Part Name Part ID Qty Needed
> >
> > 1 PP202 Red Tape 1 1
> > 1 PP202 .004 grey Latex 2 4
> > 1 PP202 Brown Tape 3
> > 1 1 PP202 Aluminum Frames 4 1
> >
> >
.

Quantcast