Error Checking

[ben3001]

Hi people i have A database that keeps records of a stock and transactions in a database.

The current stock of an item is updated when a sales transaction is entered.

I need some error checking because at present it is possible to "sell" more of an item than there is currently in stock!

where do i start?

 

[spasticus]

replace all the code on your Transactions form to this:

Option Compare Database

Private Sub TransactionQuantity_AfterUpdate()
    InStock = DLookup("QuantityInStock", "tblProducts", "[StockId]=" & [StockId] & "")
    SPrice = DLookup("SellingPrice", "tblProducts", "[StockId]=" & [StockId] & "")
    
    If TransactionQuantity > InStock Then
    'you might want to change this bit
        MsgBox "There's only " & InStock & " in stock."
        Me![TransactionQuantity] = InStock
    End If
    
    TransactionAmount = TransactionQuantity * SPrice
    DoCmd.OpenForm "frmProducts"
    Forms![frmProducts].QuantityInStock = Forms![frmProducts].QuantityInStock - Me![TransactionQuantity]
    DoCmd.Close acForm, "frmProducts", acSaveYes
End Sub

where the code opens the products to update the amount in stock i would use an update query but that works just as well.

 

[ben3001]

Spasticus you are a legend... Thanks for that.

Now since i posted this what i have been working on hasnt really been working at all..

For example when you click sale, it counts as a sale therefore the quantity comes off the amount of stock i have.

Ive been trying to do the reverse but for the refund thing. Any ideas on how to do that?

So if i select refund from the list instead of taking away the stock it adds it.

 

[spasticus]

change the code to this and it will remove stock for a sale and add it for a refund. i doubt its exactly what you want but you could work out the rest from the code.

Private Sub TransactionQuantity_AfterUpdate()
InStock = DLookup("QuantityInStock", "tblProducts", "[StockId]=" & [StockId] & "")
SPrice = DLookup("SellingPrice", "tblProducts", "[StockId]=" & [StockId] & "")

DoCmd.OpenForm "frmProducts"

If List12.Value = "Sale" Then
If TransactionQuantity > InStock Then
MsgBox "There's only " & InStock & " in stock."
Me![TransactionQuantity] = InStock
End If

Forms![frmProducts].QuantityInStock = Forms![frmProducts].QuantityInStock - Me![TransactionQuantity]
ElseIf List12.Value = "Refund" Then

Forms![frmProducts].QuantityInStock = Forms![frmProducts].QuantityInStock + Me![TransactionQuantity]
Me![TransactionQuantity] = 0
End If

TransactionAmount = TransactionQuantity * SPrice

DoCmd.Close acForm, "frmProducts", acSaveYes
End Sub

 

[ben3001]

This code does seem to do anything when i click refund, it makes the transaction quantity 0

 

[spasticus]

look at the products form

 

[ben3001]

The only product it seems to be updating is the 1st record

 

[The_Doc_Man]

The FIRST issue is that you never do inventory this way. Search this forum for INVENTORY and STOCK LEVEL as topics to see what has been written on this subject. When you update a table with the current stock inventory, you are storing a computed value. Big-time NO NO. Among other things, if your system crashes or hiccups during that update, your inventory is wrong. Second, if you have a shared database and two users make updates to the same stock item at the same time, you run the risk of destructive interference between the two users. This is very common.

In theory, in any inventory system you have transactions that add stock by having purchased it, add stock through returns, remove stock by having sold it, remove stock due to theft or damage or prior miscounts. These transactions need a FEW things in common, such as quantity and part number. They don't need everything to be exactly alike.

Your inventory levels for any item then can be tracked via a two layer query.

Layer on bottom:

SELECT StockNo, Quantity AS ItemCount FROM StockPurchases UNION
SELECT StockNo, -Sold AS ItemCount FROM StockSales UNION
SELECT StockNo, Returns AS ItemCount FROM StockReturns UNION
SELECT StockNo, -MissingItems AS ItemCount FROM StockShrinkage;

Layer on top

SELECT StockNo, Sum(ItemCount) from BottomQuery GROUP BY StockNO;

The entries for each StockNo in the top query are the instantaneous item counts for each stock number.

If you look through the forum you will see many posts (including some of mine) that describe how to archive from this table when it gets too big.