Subtract number in a textbox from the table field

[TheFoxy]

Hey,

I have a form Orders with a subform OrderItems. The Orders form is linked to the OrderItems subform by OrderID. The Orders form corresponds to an Orders table, and OrderItems form to an OrderItems table. (one to many join on OrderID).

Each OrderItems record represents one item that can be shipped in an order. It has a field 'NoInStock'. Now, on the subform I want to have a textbox that will allow the user to enter the number of those items sent in the order, and have the database subtract that number from the value of the 'NoInStock' field of that particular OrderItems record.

I thought of doing this with VBA using the 'LostFocus' event (so it would subtract when the user had finished editing), but really this isn't ideal, as the user could select/deselect the textbox and thereby subtract the value many times, or simply edit the field, and the number would be re-subtracted. The database would also pop up that edit records confirmation dialogue each time.

I want it to subtract the value when the user finishes with that particular page on the main form, and hence begins a new Order record, so that edits to the textboxes cannot mess the subtraction up.

Can this be done?

 

[randy700]

How about....

Add a "Process Order" button and perform the calculations there. You could also include code that pops up a message box if the user attempts to move to another screen without processing the order. Something like...
If Me.Dirty Then
if MsgBox("Save changes?", vbYesNo + vbQuestion) = vbYes Then
call the button click event here
End If
End If

You can also prevent the edit records confirmation from being displayed with...
DoCmd.SetWarnings False
run queries as necessary here
DoCmd.SetWarnings True



Randy

 

[TheFoxy]

That would work nicely for a plain form, but the problem is that the subform is where I need to do the calculation. I would want to calculate when the main form moves to the next page. But there is no way, as far as I can see, to communicate between the main form and subform. (I would need to trigger the subform's calculation code via code in the main form). Also, there is the problem that I cannot do the calculations for every subform record at once (it's on continuous forms mode).

Btw, I'm assuming that for triggering an action when the user tries to change to the next screen, I would need to remove the standard record navigation buttons and add my own? (The code behind the buttons would trigger the action).

 

[hkaing79]

Have you considered using unbound forms instead?

 

[TheFoxy]

No, and it doesn't seem like a good idea to me. I want the details of the Order items to show in the subform, which would need recordset code otherwise, and it would obviosuly require a whole load of coding to replicate the functions of the form when they are bound.

The only option I can think of atm is similar to the first reply: change the subform to single form, remove the navigation buttons and add a 'Move to next' button that does the calculation and then moves subform to the next record.

This isn't ideal, as I would have wanted the subform records in a list so that they could be added and looked at as a group. :/

 

[willir]

You can run the code on the subform or main form depending on needs.

The way I hanlde this type of thing is through a function...

The following function receives the OrderID and adjusts the inventory...

Function AdjustInventory(lngOrderID As Long) As Boolean

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

If Nz(lngOrderID, 0) Then
    
    Set dbs = CurrentDb()
    
    strSQL = "SELECT stItemID, ItemQty from stOrderDetailTbl WHERE stOrderID = " & lngOrderID
    
    Set rst = dbs.OpenRecordset(strSQL)

    'Turn warning prompt off and then on
    DoCmd.SetWarnings (False)
    
    With rst

        .MoveFirst
        
        Do While Not .EOF
        
            lngItemID = !stItemID
            intQty = !ItemQty
            intQty = -1 * intQty
            intOldQty = DLookup("[ItemOnHand]", "stItemTbl", "[stItemID] = " & lngItemID)
    
            strSQL = "UPDATE stItemTbl SET ItemOnHand = " & (intOldQty + intQty) & " WHERE stItemID = " & lngItemID
            DoCmd.RunSQL strSQL
    
            .MoveNext
    
        Loop
    End With
    
    'Turn warnings back on
    DoCmd.SetWarnings (True)

    rst.Close
    dbs.Close
    AdjustInventory = True

Else

    AdjustInventory = False
    
End If

End Function

By using a function stored in the module, you can test or update from either the form or subform

Richard

 

[hkaing79]

Is it wise to store calculated fields? Instead of subtracting from inventory, why not simply add the total products and the total products sold. The difference is the total products in stock.

 

[willir]

True hkaing, but for inventory, you have to store your on hand, orders, back orders.