How to check stock availbility

  • Thread starter Thread starter PeteC
  • Start date Start date
P

PeteC

Hi,

I'm working on a small stock system which is handled by telesales operators.

A customer will ring in, and give their 'shopping list' of required items.

The Customer record has a subform of ordered items, showing the name and
number ordered of each.

The problem with the system at present is that the operators simply enter
the items into the subform, and there isn't any checking of stock. I need to
add some validation, so that when they enter the item name and quantity, the
current stock level is checked. If the stock level is below zero, I need the
operator to be prompted to either cancel the line, select an alternative, or
put it on backorder.

Where is the best place to enter this checking code? I'm tempted by the
'Before Update' code block, but I'm not sure how to manipulate it. Any and
all pointers welcomed!

TIA,

Pete.
 
The Before Update event is the best place, I think. You could do a DLookUp
on the instock quantity and compare it with the ordered quantity.

If Nz(DLookUp("[QtyAvailable]","InventoryTable","[StockCode] = '" _
& Me.txtItemOrdered & "'"),0) < Me.txtOrderQty Then
MsgBox "Insufficient Quantity On Hand"
Cancel = True
End If
 
Klatuu said:
The Before Update event is the best place, I think. You could do a
DLookUp on the instock quantity and compare it with the ordered
quantity.

If Nz(DLookUp("[QtyAvailable]","InventoryTable","[StockCode] = '" _
& Me.txtItemOrdered & "'"),0) < Me.txtOrderQty Then
MsgBox "Insufficient Quantity On Hand"
Cancel = True
End If
Click to expand...

Many thanks. I'll implement this over the weekend.

Regards,

Pete.
--
Peter Connolly
http://www.acutecomputing.co.uk
Derby
UK
Skype ID: acutecomputing
 
Klatuu said:
The Before Update event is the best place, I think. You could do a
DLookUp on the instock quantity and compare it with the ordered
quantity.

If Nz(DLookUp("[QtyAvailable]","InventoryTable","[StockCode] = '" _
& Me.txtItemOrdered & "'"),0) < Me.txtOrderQty Then
MsgBox "Insufficient Quantity On Hand"
Cancel = True
End If
Click to expand...


Hi,

The above code works, but only to reject the entry of the txtOrderQty.

How would I get more control on this - ideally I'd abandon the entire row,
then display a dialog box offering the options of cancel, select an
alternative product, or change the quantity requested.

TIA,

Pete.
--
Peter Connolly
http://www.acutecomputing.co.uk
Derby
UK
Skype ID: acutecomputing
 
Back
Top