Don't allow negative values in Inventory 1

[acnovice]

Hi,

I have small inventory system using access. When I create an invoice, shipp q'ty is subtracted from inventory.
What I want to do is if q'ty is 0 or less than shipQty, give the warning message and not to allow to create an invoice until inventory has enough q'ty.

For an Invoice Creation: frmInvoice form with sub form sfrmInvoiceDetail which has ShipQ'ty and TPbaseNo(PartNumber).
Currently, ShipQ'ty is subtracted from Current in tblProduct without checking the q'ty .
Following is my code for info.

Private Sub Form_AfterUpdate()

On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblProduct SET [Current]=[Current]-[sfrmInvoiceDetail]![ShipQ'ty]" & _
" WHERE [TPbaseNo]=[sfrmInvoiceDetail]![TPbaseNo];"

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "There was an error updating"
Cancel = True
Resume ErrorHandlerExit:

End Sub

We input the Current q'ty from form InventoryOP and saved to tblProduct.

Any help will be appreciated.

 

[acnovice]

Hi TheAceMan1,

Thank you so much ... it works great.
BTW, What are you doing out there on Christmas day ?

You save my day ... thank you again and have a great holiday.

Everyone above... especially pointed out effeciency of the Inventory system... I will get back to you after holiday.
Thank you.

 

[TheAceMan1]

acnovice . . .

Glad its solved!

I do think you should give [blue]MichaelRed's[/blue] post more consideration . . . I agree with him!

This may solve the problem currently, but I see problems down the road! . . .

[blue]In any case . . . You take care! . . . Ya Hear! . . .[/blue]

See Ya! . . . . . .