How do I delete an amount from my parts table.

[Guest]

I have created a form for tracking equipment repairs, in it you have to
select parts from the parts table that you used to repair the equipment. How
do I auto delete one part for the inventory? Example, I have 15 screws, I
replaced one I now have 14 screws. I cant figure out how to make the form
auto delete one screw. HELP!

 

[Arvin Meyer [MVP]]

Add a column named Discontinued to the Parts table which will have a Yes/No
(Boolean) field data type. Write a query which has True or -1 as a criteria:

SELECT *
FROM tblParts
WHERE Discontinued = False;

Instead of the tblParts, use the new qryParts as the record source for your
form, and add a checkbox for the new Discontinued field. Open the form and
find the record that you want to hide. Check the Discontinued field. When
you reopen the form, you'll no longer see that record.

 

[Guest]

Personally, I'd write code in the form's AFTER UPDATE event that would
programmatically open the appropriate item record in the Inventory table, and
subtract the QuantityUsed (or whatever you call it) from the QuanitityOnHand
column.

 

[Bob Quintal]

=?Utf-8?B?UGFtLVFudG0=?= <[email protected]>
wrote in

I have created a form for tracking equipment repairs, in it
you have to select parts from the parts table that you used to
repair the equipment. How do I auto delete one part for the
inventory? Example, I have 15 screws, I replaced one I now
have 14 screws. I cant figure out how to make the form auto
delete one screw. HELP!

The way the big Manufacturing Requirements programs do this is
to have a separate row for each inventory transaction in the
inventory table. A code of + 1 is used to indicate parts into
inventory, and - 1 parts out.

buy and stock 4 parts, a new row is added to the table, with the
Part ID, date, possibly price, and a code that says stocked.
Use 7 of that part number and you create a new row that says on
date x, used 7 parts.

The total inventory quantity is calculated in a query as the sum
of the parts in each row times the code value.

This way you get a lot of useful information about how often you
use parts, so you can calculate things like economic order
quantity, price trends, etc.

And form design becomes so simple. You just add a new row for
each part you use.