Stock Control via ComboBox Column on Subform

[LaughingDev]

Hi all,

Ive been working on a simple Access database system for a small business which buys/sells goods. Im currently trying to develop stock control on the CreateOrder form.

The database is similar to the Northwind example, it has 4 tables thus far: Customers, Products, Orders & OrderDetails. ATM the products table simply has a StockLevel field which is being updated by several queries, one upon completion of an order, one for making stock purchases, and one at cancellation of an order.

The CreateOrder form has a OrderDetails subform from which users are able to select the products using a combobox. The combobox uses lookup to show the ProductName(col1) and StockLevel(col5).

What I would like is to have the form not allow users to select Products for which the StockLevel is 0, a MsgBox on the afterupdate event would be ideal as well. However I am not sure whether this is possible to do and how I would go about doing so.

Any help or advice on this would be much appreciated. Thanks in advance.

 

[boblarson]

Well, for this you can use the Before Update of the combo box to check the StockLevel field.

Private Sub ControlNameHere_BeforeUpdate(Cancel As Integer)
If Me.StockLevel.Column(4) = 0 Then
   Msgbox "You can't select this as there is no stock", vbExclamation, "Error"
   Cancel = True
End Sub

 

[LaughingDev]

Thanks Bob, that helped me acheive the behaviour I was after. Nice catch on my column numbering as well, thats what I was doing wrong in code using 5 instead of 4.

What Ive got now is an AfterUpdate event which shows the MsgBox and then does an acCmdUndo on the combobox. This clears the combobox after letting the user know the product is out of stock so they can immediately continue adding other products.

Im hoping this way of doing it does not have any bad sideeffects, if so please let me know.

Thnx again.

 

[boblarson]

Sounds like you are on your way. You could also use

Me.ComboNameHere.Undo