How do i approach this?

[DataBug]

Say I have a piece of lumber in inventory that's 20 ft. long. I cut it into 2 pieces, one 13 ft. and the other 7 ft. Now I need to remove the 20 ft piece from inventory and replace it with the 2 pieces I just cut. Is there any way to automate this in Access? I'm have trouble visualizing and approach to this problem.

TIA

 

[edtab]

Assuming you'll cut your lumber into 2 pieces only, I'd approach it this way:

Create a form bound to your inventory table, place the following controls:

a) combobox to allow you to select the lumber to cut
b) text box to display the inventoryid
c) text box to display the description
d) text box to display the quantity on hand
e) text box to display the dimension (especially the length)
f) 2 text boxes - textbox 1 to receive input for the length of the first piece
and text box 2 to receive input for the length of the 2nd piece.

After you input the length of the 2 new pieces, you'd want to check to ensure that
the two lengths added equals the original length, otherwise, pop an error
message indicating this error and reinput the length of the two pieces until the added lengths equal
the original length, then run 2 insert statements and 1 delete statement
using sql.

- Create 2 SQL statements to insert the two new pieces into your inventory table
- Create 1 SQL statement to delete the old inventory (not recommended -- better to just mark it as being cut using a boolean field).

Come to think of it, you should also store the original inventoryid in the two new records you created so you can back track it.

Let me know if you need help with the SQL statements.

HTH

 

[Dreamweaver]

Linking It back is a must me thinks one reason is if the suppier of the 20 foot piece puts the price up then if you have a link to that products Parts then you could automate the updating of each parts price or at least get a warning that there could be a priceing problem.

Hope it helps

mick