I have some code I've been using and it works fine but I need it to be more functional. The code is located in the Before Update event of a Quantity field.
It opens a recordset and update the Units In Stock field from a different table when someone orders products. If the user tries to order more quantity than is in stock an error message appears and prevents the Units In Stock for changing. But I had a thought! What if the user (after closing the recordset) realizes they ordered to much or not enough of the product? If you go back to the same record, you can change the Quantity but it does not update the Units In Stock. I'm posting the code below. It's written in DAO ( I know some would call this DOA ) but it's an older script that I haven't converted yet.
If anyone can figure out how to make Units In Stock update a second time on the same record, I'll put in a good word to Santa!!
Private Sub Quantity_BeforeUpdate(Cancel As Integer)
'This code opens a recordset in the Supplies Received table and edits/updates
'the amount of Units In Stock. The update only takes place when the Quantity
'ordered is less than the Units In Stock. If the Quantity ordered is
'greater than the Units In Stock, a Warning message appears and the entry is
'deleted without updating the Supplies Received table.
On Error GoTo Error_Handler
If Me.NewRecord Then
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SuppliesReceived", dbOpenTable)
Well, if they're adjusting down, you could trap that using:
If YourControl.value < YourControl.OldValue
If that's true then you could skip the code. This would work for adding quantity back into stock. if they're adjusting a quantity UP, and it exceeds onhand, I still don't see why you would let them append the changes.
I think I understand what you're trying to tell me. But I'm not sure where in my current code you would insert this condition. Could you clarify this for me.
If the user was adjusting the quantity up and the new quantity did exceed Units In Stock then I wouldn't want that update to occur but if the new quantity did not exceed the Unit In Stock then I would want the update to occur.
Oh man, a light bulb just went off!
User orders 5 pencils and current Units In Stock show 10 are available
leaving 5 pencils in stock.
User goes back to record to change Quantity to 7 (adding two more pencils)but Units In Stock now shows 5 are available.
I see what you mean. Bummer!
Is there a way to rollback to the previous Units In Stock based on a condition?
Ahhhh... I see what you mean. Perhaps you could lock already existing transactions and provide an "edit button" of sorts. If the transaction is in "edit" mode (set a boolean variable perhaps?), then your application knows this is pre-existing and to do the math accordingly.