How to check stock availbility

[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.

 

[Guest]

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

 

[PeteC]

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

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

Regards,

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

 

[P]

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

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