Limiting order amount based on available stock

[MangoFruit]

Hi all,

I have an inventory database with a few tables:

1 with product info
1 with customer info
1 with incoming stock
1 with outgoing stock (orders)

I also have 3 query's:

Total incoming stock per product
Total outgoing stock per product
Total current stock per product (based on the 2 above queries)

I'm trying to make a form through which orders can be placed, and I want to limit the amount that can be ordered based on how much stock there is.
I've been told I can use the 'before update' event on the form to achieve this, but to be honest I have no idea where to start. Do I use a Macro, an Expression, or do I need code?
Also, currently the record source for this form is the outgoing stock table, but I guess I'll need to add my 'current stock' query to the sources before this can work? And maybe even my other 2 queries because my 'stock' query is based on the other 2? But I can't really seem to get that to work either. Can someone point me in the right direction please?

Thanks!

 

[pr2-eugin]

You might need a bit of coding. The simple coding might go like this.

Private Sub totalQtyControl_BeforeUpdate(Cancel As Integer)
    Dim availCount As Long
    availCount = DCount("amountLeftField", "qryStockLevel", "productID = " & Me.productOrderedID)
    If availCount < Me.totalQtyControl Then
        MsgBox "There is not enough number of Products available for the chosen item." & vbCrLf & vbCrLf & _
                IIF(availCount = 0, "Stock count for Item is 0", "Please order " & availCount & " or less."), vbInformation
        Cancel = True
    End If
End Sub

 

[Mihail]

I'll try to explain this for you but forgive me if my English will not be enough in order to make my post understandable.

You have no need to keep the tables "incoming stock" and "outgoing stock".
A "Customer" is a customer if he buy. But, mathematically, is no difference between sell and buy.
So, a sell is a buy with the minus sign. (and vice-versa)

Following this logic, you can use an "Order" either for selling either for buying.
From the Stock view point, if you buy then you need to use the "+" sign (this action will increase the stock) and, if you sell, you should use the "-" sign because this action will decrease the stock.

So, you need only a table "Orders" where you will store the actions for products transfer.
Something like this:

tblOrders
ID_Order
OrderDate
ID_Product
ProductQuantity
OrderType

The most important part (for this discussion) is the "OrderType" field.
My advice is to store here "-1" for sells and "+1" for buys. (numeric values).

From now your queries will be a lot simplified:
qryBuys: Sum, for each product, where OrderType = 1
qrySells: Sum, for each product, where OrderType = -1
Stock: Sum, for EachProduct, where Quantity = ProductQuantity*OrderType

Of course, you should start buy "buying" each product => a fictive Order for "buy" in order to establish your initial stock for each product.

Good luck !

 

[MangoFruit]

Hi guys,

Thanks for your replies. I'm taking it one thing at a time so first I'm trying to redesign my database based on Mihails advise. I'm wondering though with this new way of keeping track of transactions: how can I still keep track of which customer places which order, and optionally which supplier delivered which goods? Previously I had an 'orders' table in which I simply kept track of the customer for each order and I could do the same for suppliers in my 'replenishments' table. Would it be a good option to have 2 optional fields in my transactions table, 1 for customerID and one for supplierID? The problem with making the fields optional would be that you can end up with orders or transactions for which there is no customerID/supplierID entered.

 

[Mihail]

MY table "Orders" is a simplified one, in order to teach you a "template".
Of course that you should modify this in order to keep tracks for customers/suppliers :

tblOrders
ID_Order
OrderDate
ID_Partner
ID_Product
ProductQuantity
OrderType

The "Partner" will be a "Customer" if OrderType = -1 or a "Supplier" if OrderType = +1.

Maybe you need more data for each Order, like OrderNumber etc. I don't know all your requirements.
So you should adapt "my" table to your needs.