Increment a field when record is added

[Hfnet]

I have a table called HWItems, which I want to count stock going in and out of my store

So far I have created the field CurrentStock, and I can reduce this number if an item goes out, but I am finding it hard to increase it when an item is entered.

How can I make the relevant field equate to CurrentStock = CurrentStock + 1 Where HWItems.ID = Combo0.Value

Combo0 is the box in my input form which relates directly to HWItems.ID

 

[Darrylles]

Hi,

You haven't stated HOW you reduce the stock level. How do you? It must be simply a matter of doing the same with a positive value.

Anyway, why can't you paint the CurrentStock field on your form and increment it when the user adds one (or x) to it?

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[Hfnet]

In the Use Item form, I am using adoRec open recordset, of which the stock level is part of, but in the Add item of stock form, the item type is dynamically selected by the user in a combo box, so in the use item form I am able to use

CurrentDb.Execute "Update HWCategories Set CurrentStock=" & fID & " Where ID=" & adoRec!CategoryID

but in the add item form I am not using adoRec or opening any data.

What i need to do is, when the user chooses the item type in Combo0 and the combo loses focus, the stock level for that type is retrieved, then I can use the CurrentDb like above. I assume I need a Select statement, but not sure what it should read.

Something like

Select HWCategories.CurrentStock Where HWCategories.ID = Combo0.Value

maybe?

 

[willir]

Here is a twist...

I use the same UPDATE statment to update centrally important tables such as inventory via a function call instead of having multiple update points. Why? Easier maintenance. If I change the schema or need to add functionality, I only need to work with the one area instead of hunting through code.

Function AdjustInventory(lngOrderID As Long, booOrder as Boolean, strMsg As String) As Boolean

Dim rst As DAO.Recordset, dbs As DAO.Database
Dim strSQL As String, booPass As Boolean
Dim lngItemID As Long, intOldQty As Integer, intQty As Integer

booPass = True

If Nz(lngOrderID, 0) = 0 Then
    strMsg = "Invalid Order" & vbCrLf
    booPass = False
End If

If booPass Then
    
    Set dbs = CurrentDb()
    
    If booOrder Then
         strSQL = "SELECT ItemID, ItemQty from OrderDetailTbl WHERE OrderID = " & lngOrderID
    Else
        strSQL = "SELECT stItemID, ItemQty from ReceiptsTbl WHERE ReceiptID = " & lngOrderID
    End If
    
    Set rst = dbs.OpenRecordset(strSQL)
    'Turn warning prompt off and then on
    
    With rst

        .MoveFirst
        
        Do While Not .EOF
        
            lngItemID = !stItemID
            intQty = !ItemQty
[COLOR=blue]            If booOrder Then intQty = -1 * intQty[/color]
            intOldQty = DLookup("[ItemOnHand]", "stItemTbl", "[stItemID] = " & lngItemID)
    
            strSQL = "UPDATE stItemTbl SET ItemOnHand = " & (intOldQty + intQty) & " WHERE stItemID = " & lngItemID
            DoCmd.RunSQL strSQL
    
            .MoveNext
    
        Loop
    End With
    
    rst.Close
    dbs.Close
    AdjustInventory = True

Else

    AdjustInventory = False
    
End If

End Function

The only difference between an order and a receipt of goods is...[tt]
intQty = !ItemQty
If booOrder Then intQty = -1 * intQty
[/tt]

Richard