how to: show warning when Qty on hand is 0

[siamandm]

Hello All
if I have a query to calculate a quantity on hands from two query as below :

Screenshot 2024-04-22 094456.png

and I have a form that is based on another query, used for making orders. This form includes a combo box for selecting products. I would like to implement a feature where if the quantity of an item is equal to zero, either that item is not shown in the combo box or a warning message appears when the user selects that product.

Regards

[HansV]

In the Before Update event of the combo box:

Code: Select all

Private Sub ComboName_BeforeUpdate(Cancel As Integer)
    If DLookup("QtyOnHand", "StockQ", "ProductID=" & Me.ComboName) <= 0 Then
        MsgBox "This product is out of stock. Please select another one!", vbInformation
        Cancel = True
    End If

where ComboName is the name of the combo box.

[siamandm]

Thank you very much, it worked as usual :)
can we add another line to rest the combo box to " Select a product" when the user click Ok on the message box?
Regards

[HansV]

Unfortunately, you cannot set the value of the combo box in the BeforeUpdate event.

[siamandm]

thank you for the information