Solved How to update a currentstock control in the subform after a requery from the parent form

[nector]

I want to be updating a control called currentstock whenever I launch a query below instead of me doing it manually to avoid mistakes. At the moment the update is put on afterevent in the subform and it fires immediately I change the quantinty but does not fire if I requery the data unless I go back and retype the quantity again. This is not good for us to avoid mistakes such typing different a figure instead of the original figure.

This work okay at creation stage

Private Sub Quantity_AfterUpdate()
Me.CurrentStock = Nz(Me.CurrentStock, 0) + Nz(Me.Quantity, 0)
End Sub

The problem comes when you to do an automatic reversal of the same figures it does not fire at all, such that after retrieving the figures by using the query below you have to update the current stock manually

Private Sub CboReverseInvoice_AfterUpdate()
On Error GoTo Err_Handler
Dim LTAudit As String

LTAudit = Nz(DLookup("intrlData", "tblCustomerInvoice", "InvoiceID =" & Me.CboReverseInvoice))
Me.Filter = "InvoiceID = " & Me!CboReverseInvoice.Value & ""
If (LTAudit <> "") Then
    Beep
    MsgBox "This document is already approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    Me.FilterOn = False
Else
    Me.FilterOn = True
End If
Dim Records As DAO.Recordset

    Set Records = Me![sfrmLineDetails Subform].Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
             
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
Exit_CboReverseInvoice_AfterUpdate:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CboReverseInvoice_AfterUpdate

End Sub

I tried to put the code below on dirty again it does nothing

Me.CurrentStock = Nz(Me.CurrentStock, 0) + Nz(Me.Quantity, 0)

 

[Minty]

You should really calculate the current stock from the transactions since the last stock take.
You can store that as a number with the product code (if it helps other queries), but you should run the calculation again to recalculate it, not try and massage it on the form.

If you are using SQL Server it will recalculate very quickly, so you don't need to store it and it will always be correct.

 

[jdraw]

Nector,

See this article by Allen Browne re Inventory.

 

[GPGeorge]

Nector,

See this article by Allen Browne re Inventory.

The Stock Take process and inventory management in Northwind Developer Edition is based largely on Allen Browne's method.

You can download and install it and use it to learn more about that method in context.

NOTE: the template for NW Dev was broken the last time I downloaded and installed it; the custom ribbon was not included. Microsoft is currently working on correcting that, so it may well be fixed by the time you download it. If it's still missing, though, you can import it from the small accdb I attached at the end of this discussion.

 

[Pat Hartman]

The problem comes when you to do an automatic reversal of the same figures it does not fire at all, such that after retrieving the figures by using the query below you have to update the current stock manually

Control level events do not fire when the control is updated via VBA. Just one of the reasons, I only use the Form level Before/After update events unless I have some specific reason to put my code in the control level events.
If you'd like to better understand the Access event model, you might want to watch at least one of the videos and play with the sample database.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[nector]

I think there is a total misunderstanding here. When a user is creating some invoice detail line the current stock balance is automatically calculated the moment the new stock quantity is entered, what fire the calculation is the after event on quantity control. At this stage all is fine, the problem comes up when doing the credit note, the credit note copies the original invoice to reverse with its quantities and then after copying the invoice it creates a new document called credit note with its own unique primary key. This is the document which now require updating the current stock by adding back, but it does not add back the quantities coming in because the quantity after event does not fire unless you re-enter the quantities manually.

The reason why the quantity after event does not fire is because when a query is run it brings the copied invoice to the forms for editing, example checking that the prices and totals are exactly as per original invoice being reversed, leaving no chance for the after-event quantity to fire. That is the reason why I was thinking that may be on dirty event will work, but it does not work either.

Here is the actual query that update the current stock balance but not exactly the same as Allen:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("QrySmartInvoiceResidualBalance")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set qdf = Nothing
strSQL = "SELECT Nz(Sum(StockBalance),0)As Balance FROM [QrySmartInvoiceResidualBalance] Where [ProductID] =" & Me.ProductID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Me.CurrentStock = Nz(rs!Balance, 0)
rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
Set prm = Nothing

Make up of QrySmartInvoiceResidualBalance

SELECT tblCustomerInvoice.ShipDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Opening, 0 AS Productunit, 0 AS Grnunits, tblLineDetails.Quantity, 0 AS Issunits, 0 AS DiffUnits, IIf(([DocCodes]="R"),([Quantity]*1),([Quantity]*-1)) AS StockBalance
FROM tblDocuments INNER JOIN (tblCustomerInvoice INNER JOIN (tblLineDetails INNER JOIN tblProducts ON tblLineDetails.ProductID = tblProducts.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) ON tblDocuments.DocID = tblCustomerInvoice.DOcumentstype
WHERE (((tblCustomerInvoice.intrlData) Is Not Null));

UNION ALL

SELECT tblstockMasterAdj.ocrnDt, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, IIf(([sarTyCd]="06"),[rsdQty],(([rsdQty])*-1)) AS Qty, 0 AS Prod, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, (IIf(([sarTyCd]="06"),[rsdQty],(([rsdQty])*-1))) AS BalancesQty
FROM tblstockMasterAdj INNER JOIN (tblProducts INNER JOIN tblStockAdjustmentDetails ON tblProducts.ProductID = tblStockAdjustmentDetails.SelectProduct) ON tblstockMasterAdj.StockADjID = tblStockAdjustmentDetails.StockADjID
WHERE (((tblstockMasterAdj.Status) Is Not Null));
UNION ALL

SELECT tblpurchases.cfmDt, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS OP, 0 AS Prod, IIf(([pchsSttsCd]="04"),0,(IIf(([pchsSttsCd]<>"06"),(IIf(([pchsSttsCd]<>"05"),([Qty]*1),([Qty]*-1))),([Qty]*-1)))) AS CLsQty, 0 AS Sale, 0 AS Issiung, 0 AS Diff, (IIf(([pchsSttsCd]<>"06"),(IIf(([pchsSttsCd]<>"05"),([Qty]*1),([Qty]*-1))),([Qty]*-1))) AS Stockbalance
FROM tblpurchases INNER JOIN (tblPurchasesDetails INNER JOIN tblProducts ON tblPurchasesDetails.ProductID = tblProducts.ProductID) ON tblpurchases.PurchID = tblPurchasesDetails.PurchID
WHERE (((tblpurchases.status) Is Not Null));
UNION ALL

SELECT tblstocksin.ocrnDt, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, IIf(([sarTyCd]="04"),[Qty],(([Qty])*-1)) AS qtys, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, IIf(([sarTyCd]="04"),[Qty],(([Qty])*-1)) AS StockBalance
FROM (tblstocksin INNER JOIN tblstockinDetails ON tblstocksin.StockInID = tblstockinDetails.StockInID) INNER JOIN tblProducts ON tblstockinDetails.ProductID = tblProducts.ProductID
WHERE (((tblstocksin.status) Is Not Null));
UNION ALL

SELECT tblImports.ImportDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, tblImportDumpdetails.qty, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, ([Qty]) AS StockBalance
FROM tblImports INNER JOIN (tblProducts INNER JOIN tblImportDumpdetails ON tblProducts.ProductID = tblImportDumpdetails.ProductID) ON tblImports.ImportID = tblImportDumpdetails.ImportID
WHERE (((tblImports.Status) Is Not Null));

UNION ALL

SELECT tblIssueSlip.IssueDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, IIf(([tblWIP].[sarTyCd]="05"),([Quanties]),([Quanties]*-1)) AS Quantities, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, IIf(([tblWIP].[sarTyCd]="05"),([Quanties]),([Quanties]*-1)) AS StockBalance
FROM tblIssueSlip INNER JOIN (tblProducts INNER JOIN tblWIP ON tblProducts.ProductID = tblWIP.ProductID) ON tblIssueSlip.SlipID = tblWIP.SlipID
WHERE (((tblIssueSlip.IssueStatus) Is Not Null));

UNION ALL SELECT tblPOSStocksSold.PosDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, 0 AS Prod, 0 AS Grn, tblPosLineDetails.QtySold, 0 AS Iss, 0 AS Diff, IIf(([TransactionType]="R"),[QtySold],([QtySold]*-1)) AS StockBalance
FROM tblPOSStocksSold INNER JOIN (tblProducts INNER JOIN tblPosLineDetails ON tblProducts.ProductID = tblPosLineDetails.ProductID) ON tblPOSStocksSold.ItemSoldID = tblPosLineDetails.ItemSoldID
WHERE (((tblPOSStocksSold.intrlData) Is Not Null));

 

[Pat Hartman]

That is the reason why I was thinking that may be on dirty event will work, but it does not work either.

I told you which event to use -- the FORM's BeforeUpdate event. But as the others have mentioned, you shouldn't be storing the calculated value in any event.

 

[Minty]

When a user is creating some invoice detail line the current stock balance is automatically calculated the moment the new stock quantity is entered,

No, it's not, you are taking the value on the form and storing a local calculated value.

What if someone else added or took stock of the same item at the same time - you now have two conflicting answers.
You need to have a transaction. Stock in or out.

 

[CJ_London]

What if the quantity being credited is different from the quantity invoiced?

What if halfway through the user creating a credit note, they decide to abort?

Why have so many tables to record stock transactions when one is all you need. Appreciate a purchase order is different to a sales invoice or a stock adjustment but easily handled with one or more foreign keys

 

[Pat Hartman]

A credit note is simply another type of transaction that generates a financial event in addition to an inventory event.

 

[Mark_]

@nector,
When doing inventory control you should have a set periodic inventory that says "We have N number as of beginning of period".
You then query all records that move items in to or out of inventory since "beginning of period" to determine how many you have now.
You do not want to try and save the "CurrentStock" as this is a calculated value.

This will avoid a multitude of issues you'll otherwise have with trying to manage a calculated value on a per-transaction basis.

 

[GPGeorge]

@nector,
When doing inventory control you should have a set periodic inventory that says "We have N number as of beginning of period".
You then query all records that move items in to or out of inventory since "beginning of period" to determine how many you have now.
You do not want to try and save the "CurrentStock" as this is a calculated value.

This will avoid a multitude of issues you'll otherwise have with trying to manage a calculated value on a per-transaction basis.

That's the way Allen Browne's method works and the way it was implemented in Northwind Developer.

 

[GPGeorge]

I think there is a total misunderstanding here. When a user is creating some invoice detail line the current stock balance is automatically calculated the moment the new stock quantity is entered, what fire the calculation is the after event on quantity control. At this stage all is fine, the problem comes up when doing the credit note, the credit note copies the original invoice to reverse with its quantities and then after copying the invoice it creates a new document called credit note with its own unique primary key. This is the document which now require updating the current stock by adding back, but it does not add back the quantities coming in because the quantity after event does not fire unless you re-enter the quantities manually.

The reason why the quantity after event does not fire is because when a query is run it brings the copied invoice to the forms for editing, example checking that the prices and totals are exactly as per original invoice being reversed, leaving no chance for the after-event quantity to fire. That is the reason why I was thinking that may be on dirty event will work, but it does not work either.

Spoiler:

Here is the actual query that update the current stock balance but not exactly the same as Allen:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("QrySmartInvoiceResidualBalance")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set qdf = Nothing
strSQL = "SELECT Nz(Sum(StockBalance),0)As Balance FROM [QrySmartInvoiceResidualBalance] Where [ProductID] =" & Me.ProductID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Me.CurrentStock = Nz(rs!Balance, 0)
rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
Set prm = Nothing

Make up of QrySmartInvoiceResidualBalance

SELECT tblCustomerInvoice.ShipDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Opening, 0 AS Productunit, 0 AS Grnunits, tblLineDetails.Quantity, 0 AS Issunits, 0 AS DiffUnits, IIf(([DocCodes]="R"),([Quantity]*1),([Quantity]*-1)) AS StockBalance
FROM tblDocuments INNER JOIN (tblCustomerInvoice INNER JOIN (tblLineDetails INNER JOIN tblProducts ON tblLineDetails.ProductID = tblProducts.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) ON tblDocuments.DocID = tblCustomerInvoice.DOcumentstype
WHERE (((tblCustomerInvoice.intrlData) Is Not Null));

UNION ALL

SELECT tblstockMasterAdj.ocrnDt, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, IIf(([sarTyCd]="06"),[rsdQty],(([rsdQty])*-1)) AS Qty, 0 AS Prod, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, (IIf(([sarTyCd]="06"),[rsdQty],(([rsdQty])*-1))) AS BalancesQty
FROM tblstockMasterAdj INNER JOIN (tblProducts INNER JOIN tblStockAdjustmentDetails ON tblProducts.ProductID = tblStockAdjustmentDetails.SelectProduct) ON tblstockMasterAdj.StockADjID = tblStockAdjustmentDetails.StockADjID
WHERE (((tblstockMasterAdj.Status) Is Not Null));
UNION ALL

SELECT tblpurchases.cfmDt, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS OP, 0 AS Prod, IIf(([pchsSttsCd]="04"),0,(IIf(([pchsSttsCd]<>"06"),(IIf(([pchsSttsCd]<>"05"),([Qty]*1),([Qty]*-1))),([Qty]*-1)))) AS CLsQty, 0 AS Sale, 0 AS Issiung, 0 AS Diff, (IIf(([pchsSttsCd]<>"06"),(IIf(([pchsSttsCd]<>"05"),([Qty]*1),([Qty]*-1))),([Qty]*-1))) AS Stockbalance
FROM tblpurchases INNER JOIN (tblPurchasesDetails INNER JOIN tblProducts ON tblPurchasesDetails.ProductID = tblProducts.ProductID) ON tblpurchases.PurchID = tblPurchasesDetails.PurchID
WHERE (((tblpurchases.status) Is Not Null));
UNION ALL

SELECT tblstocksin.ocrnDt, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, IIf(([sarTyCd]="04"),[Qty],(([Qty])*-1)) AS qtys, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, IIf(([sarTyCd]="04"),[Qty],(([Qty])*-1)) AS StockBalance
FROM (tblstocksin INNER JOIN tblstockinDetails ON tblstocksin.StockInID = tblstockinDetails.StockInID) INNER JOIN tblProducts ON tblstockinDetails.ProductID = tblProducts.ProductID
WHERE (((tblstocksin.status) Is Not Null));
UNION ALL

SELECT tblImports.ImportDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, tblImportDumpdetails.qty, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, ([Qty]) AS StockBalance
FROM tblImports INNER JOIN (tblProducts INNER JOIN tblImportDumpdetails ON tblProducts.ProductID = tblImportDumpdetails.ProductID) ON tblImports.ImportID = tblImportDumpdetails.ImportID
WHERE (((tblImports.Status) Is Not Null));

UNION ALL

SELECT tblIssueSlip.IssueDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, IIf(([tblWIP].[sarTyCd]="05"),([Quanties]),([Quanties]*-1)) AS Quantities, 0 AS Grn, 0 AS Sales, 0 AS Iss, 0 AS Diff, IIf(([tblWIP].[sarTyCd]="05"),([Quanties]),([Quanties]*-1)) AS StockBalance
FROM tblIssueSlip INNER JOIN (tblProducts INNER JOIN tblWIP ON tblProducts.ProductID = tblWIP.ProductID) ON tblIssueSlip.SlipID = tblWIP.SlipID
WHERE (((tblIssueSlip.IssueStatus) Is Not Null));

UNION ALL SELECT tblPOSStocksSold.PosDate, tblProducts.ProductID, tblProducts.Barcode, tblProducts.WHID, tblProducts.ProductName, 0 AS Op, 0 AS Prod, 0 AS Grn, tblPosLineDetails.QtySold, 0 AS Iss, 0 AS Diff, IIf(([TransactionType]="R"),[QtySold],([QtySold]*-1)) AS StockBalance
FROM tblPOSStocksSold INNER JOIN (tblProducts INNER JOIN tblPosLineDetails ON tblProducts.ProductID = tblPosLineDetails.ProductID) ON tblPOSStocksSold.ItemSoldID = tblPosLineDetails.ItemSoldID
WHERE (((tblPOSStocksSold.intrlData) Is Not Null));

You've made this process several times more complicated than it needs to be.

Please take a break from what you are doing here. STUDY the methods others have suggested to you. Learn how the Allen Browne method is designed and learn how we implemented it in Northwind Developer. Much simpler to implement and more reliable.

 

[nector]

That's the way Allen Browne's method works and the way it was implemented in Northwind Developer.

I will do a video on I solved it for other to learn

 

[GPGeorge]

I will do a video on I solved it for other to learn

If you mean that you intend to show how you implemented the Allen Browne method, that would be helpful. Thank you.

 

[jdraw]

If you mean that you intend to show how you implemented the Allen Browne method, that would be helpful. Thank you.

I agree George. I interpreted Nector's comment that a helpful video tutorial will be made. Let's hope to youtube for all to see.

 

[Mark_]

To add "WHY" you do periodic inventory, then add/subtract based on transactions, lets look at the real world.
You check you stock and see you have 100 green widgets. You update your records to show "Got 100 green widgets"
You enter a transaction that sells 10.
While you are entering in your transaction, a coworker is ALSO selling 10 green widgets.

IF you try to catch this in either YOUR transaction or your coworkers, you'll show you still have 90 when in reality you have 80.

Another coworker takes back 5 green widgets on a return.

You go in an count up your green widgets. You find you really do have 80 because the coworker who entered the return entered the wrong kind of widget.

Your data should reflect reality. Not everyone is perfect, so you need a way to catch human errors. Hence a periodic inventory. Normally this record also saves your discrepancies (expected 80, had 85) so you can work on identifying and correcting other issues.