How to ensure the open recordset include also any new record entered (1 Viewer)

[nectorch]

I want the open record set below to include the current type in record or new record, but it does nothing , it only include history data or prior record not current record being entered by users.

Is there a way to force the code below to also include the current or new record being being captured?

Here is the VBA

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("QrySmartInvoiceResidualBalancePOS")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set qdf = Nothing
strSQL = "SELECT Nz(Sum(StockBalance),0)As Balance FROM [QrySmartInvoiceResidualBalancePOS] Where [ProductID] =" & Me.ProductID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
DoCmd.Requery
Me.CurrentStock = Nz(rs!Balance, 0)
rs.Close
Set rs = Nothing
Set db = Nothing
Set qdf = Nothing
Set prm = Nothing

 

[moke123]

Commit the record before opening the record set. If in a form you can use Me.dirty = false.

 

[nectorch]

Is there a way to include record set clone so that the above code can include any record in currently the subform, that what I'm looking for.

I have added that code but nothing is working out

Me.dirty = false

 

[Minty]

There is a huge amount of unnecessary code in that routine, that could all be replaced with

Me.CurrentStock = DSum("StockBalance","QrySmartInvoiceResidualBalancePOS","[ProductID] = " & Me.ProductID

 

[nectorch]

Thank you minty both codes above works okay but the problem is that the subform current record showing on the picture is not taken into , I expected the current balance of 96 to be reducing when a user add a new line:

1.Line => 96 - 1 = 95
2.Line = > 95 - 1 = 94
3.Line = > 94 -1 = 93
4.line = > 93 -1 = 92

That is what is required

 

[The_Doc_Man]

If your code muddles with the sub-form's .Recordset, do a sub-form.Requery just after you have updated the recordset. Since a .Requery triggers the form in question to do a .Refresh, you don't even have to tell it to refresh. Or shouldn't, anyway.

me.name-of-subform-control.Form.Requery

Note that you DO NOT refer to the name of the sub-form here... you refer to the name of the control that HOLDS the sub-form. You never actually use the sub-form name in this context. The English of that command is: Look at my current form, find the named sub-control, look at the form active in that subform control, and requery it. Since you had the implied .Form property to point to that sub-form, you didn't need the actual form name.