Stopping an action (1 Viewer)

[nicwnacw]

I have a form which shop staff complete, I have placed a box on the form showing staff how much stock is held, this is linked to stockid so shows the stock level for the current item. I have worked out an Iif statement for saying not enough stock or stock Ok. This works well but I think I need to write it in code and attach it to the After Update event of the quantity field.

The problem is that I can't work out the code, can anyone help please?

 

[jfgambit]

On the after update event of teh quanity field add:

Dim qty as Variant

qty = DLookUp("[InStockAmount]","tblStock","[StockID] = '" & me.formfieldwithstocknumber & "'")

if qty < me.QuanityOrderedfield then
"Hey, we don't have that much product in stock!"
Else
End if

Obviously, the names need to be changed to the ones on your database...

HTH

 

[nicwnacw]

stop action

Thank you for that, but QuantityInStock is on a different table to sales transactions where the quantity field is, so will the Me. thingy work.

so far i have tried code to open stock from the afterupdate event on sales trans - that works. I then insert an if x<y, MsgBox ("blah blah")

I get various error messages, type mismatch, can't find control etc

 

[Pat Hartman]

The code needs to be in the BeforeUpdate event so that you can cancel the update if there is not enough inventory. I guess you don't do backorders.

 

[nicwnacw]

stop action

I adapted your code a little:


Private Sub quantity_AfterUpdate()
Dim qty As Variant

qty = DLookup("[quantity]", "Stock", "[StockID] = '" & Me.stockid & "'")

If qty < Forms![stock].QuantityInStock Then
"Not Enough Stock"
Else
End If

and I get a syntax error on the 'Not Enough Stock', I have tried it with and without brackets with the " inside and outside the brackets but Access won't play nice

 

[nicwnacw]

stop action

Have tried it on the Before Update (thanx for the suggestion), I don't get error messages, or the Not Enough Stock message. Will the If work on B4 update as there is no value in the qty field? and the if is based on X being bigger than Y

 

[Pat Hartman]

The code will NOT prevent bad data from being stored if it is in the AfterUpdate event. You need to move it and you need to include the "Cancel = True".