Test Qty on Sales Form to make sure QtyOnHand is >0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Sales Form (frmSalesForm) that has a continuous subform
(frmSalesSub) to enter each product sold.

I also have a query (qryOnHandbyPurchase) that calculates QtyOnHand by
subtracting QtySold and QtyReturned from QtyPurchased.

When a sales person enters a sale, I need the database to test the QtySold
on frmSalesSub to make sure they are not entering more items than are
available to sell.

Suggestions? AfterUpdate Code?
 
I have a Sales Form (frmSalesForm) that has a continuous subform
(frmSalesSub) to enter each product sold.

I also have a query (qryOnHandbyPurchase) that calculates QtyOnHand by
subtracting QtySold and QtyReturned from QtyPurchased.

When a sales person enters a sale, I need the database to test the QtySold
on frmSalesSub to make sure they are not entering more items than are
available to sell.

Suggestions? AfterUpdate Code?
Click to expand...

Very close: BeforeUpdate, which can be cancelled. I'd suggest using
the Form BeforeUpdate event of frmSalesSub, something like

Private Sub frmSalesSub_BeforeUpdate(Cancel as Integer)
If Me!QtySold > DLookUp("[QtyOnHand]", "qryOnHandbyPurchase", _
"[ProductID] = " & Me!ProductID) Then
MsgBox "Hey dude, we're 86 on this widget", vbOKOnly
Cancel = True
End If
End Sub


John W. Vinson[MVP]
 
John,

Thanks for the help. The only problem is that I am getting a "data type
mismatch in criteria expression" error. The ProductID field is a text
field. I seem to remember reading that syntax had to be different in the
case of a text field? If so, how would I change your code to avoid the error?
--
Thanks,
Brent


John Vinson said:
I have a Sales Form (frmSalesForm) that has a continuous subform
(frmSalesSub) to enter each product sold.

I also have a query (qryOnHandbyPurchase) that calculates QtyOnHand by
subtracting QtySold and QtyReturned from QtyPurchased.

When a sales person enters a sale, I need the database to test the QtySold
on frmSalesSub to make sure they are not entering more items than are
available to sell.

Suggestions? AfterUpdate Code?
Click to expand...

Very close: BeforeUpdate, which can be cancelled. I'd suggest using
the Form BeforeUpdate event of frmSalesSub, something like

Private Sub frmSalesSub_BeforeUpdate(Cancel as Integer)
If Me!QtySold > DLookUp("[QtyOnHand]", "qryOnHandbyPurchase", _
"[ProductID] = " & Me!ProductID) Then
MsgBox "Hey dude, we're 86 on this widget", vbOKOnly
Cancel = True
End If
End Sub


John W. Vinson[MVP]
Click to expand...
 
John,

Thanks for the help. The only problem is that I am getting a "data type
mismatch in criteria expression" error. The ProductID field is a text
field. I seem to remember reading that syntax had to be different in the
case of a text field? If so, how would I change your code to avoid the error?
Click to expand...

Just add quotemarks around the criterion:

If Me!QtySold > DLookUp("[QtyOnHand]", "qryOnHandbyPurchase", _
"[ProductID] = '" & Me!ProductID & "'") Then

If the product field might contain an apostrophe (i.e. if you're
selling "Sloan's Linament") use " instead; to include a doublequote
within a doublequote delimited string, double the doublequote (how's
that for doubletalk!):

If Me!QtySold > DLookUp("[QtyOnHand]", "qryOnHandbyPurchase", _
"[ProductID] = """ & Me!ProductID & """") Then

John W. Vinson[MVP]
 
Back
Top