Help Please.... Update table from subform

[weilerdo]

Hi All, I have an inventory DB that I am trying to setup. I have 2 table "tbl_parts" which holds my barcode_num, description, stock_level ( qty on hand ) etc. The second table is "tbl_stock_level" which has barcode_num, trans_date, qty, emp_int. I have a form frm_parts_used that when the user opens it they will select there name from the drop down and go to the part number field which is "barcode_num" in the sub form "frm_trans_sub" and put in the partnumber which populates the remaining fields. What I can't seem to do is then somehow get it to subtract the qty from the stock_level in the tbl_parts table. I was thinking an update query at the end of them entering the parts used but I know I can't do an update query if I have an expression in my query what is totaling the parts. I hope I have explained this somewhat clearly. I am attaching a copy of the database so that you can see what I am talking out easier ( I hope )... Thank you in advance for looking at this with me.

 

[WayneRyan]

Weilerdo,

You can use the BeforeUpdate event of the quantity field:

DoCmd.RunSQL "Update tbl_stock_level " & _
             "Set    qty = qty - " & Me.qty & " " & _
             "Where  barcode_num = '" & Me.barcode_num & "'"

You could also use a DLookUp to retrieve the current quantity
on-hand. If it is less than the qty on your form, don't allow
the transaction.

hth,
Wayne

 

[weilerdo]

Hey WayneRyan, I tried that and it is not reducing the quantity on hand in the "tbl_parts" table. If Im reading your code correctly that is checking to see that there is a quantity on hand correct. What I need is it to reduce my quanitity in "tbl_parts" by the number that I am removing.


Thanks

 

[WayneRyan]

Weilerdo,

Oops, specified the wrong table.

DoCmd.RunSQL "Update tbl_parts " & _
             "Set    stock_level = stock_level - " & Me.qty & " " & _
             "Where  barcode_num = '" & Me.barcode_num & "'"

I didn't put a whole lot of thought into the answer. I see a lot of
problems with this method. What if someone makes a mistake and enters
a quantity of 5, when they really meant 7? They can't just change the
number because they'll effectively be using 12!

Ideally, you shouldn't have a "stored" balance, you should calculate
it when you need to. The balance is a simple QtyReceived - QtySold.
Then you don't have to work extra to keep things in "sync".

Additionally, how many do you order for replenishment? You don't
keep track of any part of the ordering process.

Wayne

 

[weilerdo]

Hey WayneRyan,

Your right I wasn't even thinking on those lines. I will be tracking the received parts as well so I will do it that way. Thanks for all your help... its one of those you can't see the forest because of the tree's things. LOL

Don