Change Check Box when stock value is 0

[AccessRobo]

Please help.
When the stock in my sales database is 0 I want a check box to get automatically ticked. I use the check box results to create a list of in stock products, but also just want to know how to do this. I have put the code in my after update event, in the stock control on my form. Forms name is frmOrderDetailSubform. The actual Stock field is numeric. I called the control txtStock. The check box control I called chkPurchaseDetailSoldOut and the field name is PurchaseDetailSoldOut. I have tried putting the code in the check box event, after update. I have tried changing to true and false, -1 or 0, & Yes and No. Here is my best attempt so far. Thank you for any help.

Private Sub Stock_AfterUpdate()

If Me.txtStock.Value = 0 Then Me.chkPurchaseDetailSoldOut = 0
Else
Me.chkPurchaseDetailSoldOut = -1
End If
End Sub

 

[Privateer]

It might be easier to put the if statement in a query and make the query the recordsource for the form. Try this, create a query off the table with all the fields. Then create a new field in the query by entering
InStock: iif(isnull(StockField)=True,True,False) in a blank "column"
Then on your form, edit the check box and make the control source of the check box the field InStock. The nice part about this is when a number is entered into the stock field, the query evaluates and the check box changes. One last thing, this makes the check box dynamic, so you might want to set locked equal to true because its status is now dependant on the stock field, meaning you don't want someone unchecking it if there is a number in that field. Hope this helps.

 

[AccessRobo]

Thank you for the reply. I will go and work on what you suggest.

 

[AccessRobo]

Dear Privateer
Thank you, I changed your formula to suit my needs. I learnt some things from what you said, about changing the control source and the IIf formula. Thank you for your time. Craig