Inventory

[Alvin]

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

 

[Jackie L]

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]

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.

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

 

[Alvin]

I do have a stripped down version with very little info in it. where do I
send it to. My email is
(e-mail address removed)
Thank you so much Jackie, I am still working trying to get it to work.
Alvin

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.

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

 

[Alvin]

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.

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.

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