I have two forms, one the parent form also called Invoice header, and the sub form referred as detail Ines. Now when users are entering data in the sub form the form calculate also the remaining balance of the stock being sold, so in short, the running balances per each product latest balance is automatically enter in the field called currentstock.
Now I want to make a report which will show the latest balance per each product shown also on the invoice detail table line report so that the salespeople are able to see the current stock position by just looking at the invoice.
Challenges
Sometimes the salespeople will capture the same product may be four times and each line will show the current stock balance, but on the report, I Just want the last lines per each product to show on the report so that it's easier to read and understand, Example if Mango Juice has ten lines the report should not show all the ten line with balances but only the last line captured with the latest stock balance.
Below is a code I use to help capture the balances:
The code above is faster in terms of capturing data. Any idea about how to go about that? All the two tables have indexes
Now I want to make a report which will show the latest balance per each product shown also on the invoice detail table line report so that the salespeople are able to see the current stock position by just looking at the invoice.
Challenges
Sometimes the salespeople will capture the same product may be four times and each line will show the current stock balance, but on the report, I Just want the last lines per each product to show on the report so that it's easier to read and understand, Example if Mango Juice has ten lines the report should not show all the ten line with balances but only the last line captured with the latest stock balance.
Below is a code I use to help capture the balances:
Code:
Private Sub ProductID_AfterUpdate()
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("QryinvoiceResidualBalance")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set qdf = Nothing
strSQL = "SELECT Nz(Sum(StockBalance),0)As Balance FROM [QryinvoiceResidualBalance] Where [ProductID] =" & Me.ProductID & "AND [WHID] =" & Me.Parent!WareHouse
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
End Sub
Private Sub Quantity_AfterUpdate()
Me.CurrentStock = Nz(Me.CurrentStock, 0) - Nz(Me.Quantity, 0)
Me.Requery
DoCmd.GoToRecord , , acNewRec
End SubThe code above is faster in terms of capturing data. Any idea about how to go about that? All the two tables have indexes
