Query to enter data not less than certain data in another table(field)

[chriss_ko]

Help me with this plzz...
I have a query called "Stock" containing field like (Item, Description, product_qty)
another is a table called "Sales" with fields like (Sales_ID, Item, Sales_description, sales_Qty, date_Of_Sale) Item field on sales is a foreign key.

Now what i want is how can i make when the user enters new record, in field "Sales_Qty" the data entered here to be less than product_Qty.

NB; this is because you can not sell more than what you have.
?????

 

[chriss_ko]

i mean it should not be greater than Product_Qty
i.e Sales_Qty to be less than Product_Qty

 

[JHB]

I suppose you make entries through a form.
On controls where you enter/select item and quantity, you must create an event on "Before update" where you check if the number is larger than what you have in stock.
If the amount is bigger then show a message and set the Cancel = True in the "Before update" event.

Here is some sample code.

Private Sub Item_BeforeUpdate(Cancel As Integer)
  Dim dbs As DAO.Database, rst As Recordset
 
  Set dbs = CurrentDb
  If Me.sales_Qty > 0 Then
    Set rst = dbs.OpenRecordset("SELECT Item, product_qty FROM Stock " _
    & "WHERE Item='" & Me.Item & "'")
    If rst![product_qty] < Me.sales_Qty Then
      MsgBox ("The sales quantity is more as the stock")
      Cancel = True
    End If
  End If
End Sub
 
Private Sub sales_Qty_BeforeUpdate(Cancel As Integer)
  Dim dbs As DAO.Database, rst As Recordset
 
  Set dbs = CurrentDb
  If Me.Item <> "" Then
    Set rst = dbs.OpenRecordset("SELECT Item, product_qty FROM Stock " _
    & "WHERE Item='" & Me.Item & "'")
    If rst![product_qty] < Me.sales_Qty Then
      MsgBox ("The sales quantity is more as the stock")
      Cancel = True
    End If
  End If
End Sub

 

[chriss_ko]

Thank you a lot it has worked,
I appreciate.

 

[JHB]

You're welcome, luck with it.