How to ensure the open recordset include also any new record entered

[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.

 

[nector]

I had to reconstruct the table from scratch and the query including the forms its now working as required, my last question is since I'm using the open record set instead of the Dsum can this change the speed over time for example after having like 2000000 lines per product line. Kindly advise which one is going handle the data faster than the other.

 

[Minty]

The current stock won't be correct for each line if you requery the data, as it's based on the entire record set.
You would have to store it at the time of the record being entered, which is pointless, as presumably it's only accurate at that point in time.

What is the actual purpose of this - to check if it's available when ordered?
If so just check the stock during the entry and pop up a warning if it's not available.

 

[The_Doc_Man]

since I'm using the open record set instead of the Dsum can this change the speed over time for example after having like 2000000 lines per product line.

In the code shown in your first post of this thread, you are opening a recordset that contains an SQL Aggregate query, specifically SUM(...). If you instead used a Domain Aggregate function such as DSum(...), all that does is it builds an SQL aggregate query for you behind the scenes, then opens that temporary recordset and extracts a single value from it, then it closes the recordset. Which is very similar to what you were doing anyway. The delay involved in having DSum build and then execute the SQL for you is minuscule. I doubt you would see the timing difference for a single query.

However, as we say in the USA sometimes, there is still an "elephant in the room." You have a lot of products to consider if you are talking about having 2 million records (as noted in your post #7). Getting an individual sum focused on a single product ID will be relatively fast. However, will there ever be a time when you want to do this for every different product ID in a bulk operation, like perhaps generating a report? The only reason I ask is that there is a potential slowdown ahead, waiting to trip you. But since I don't know your intentions, I would rather not get involved in discussing something you might not want to do anyway.

 

[nector]

Many thanks The Doc Man, you have cleared my worry am not thinking of summing several products I'm only interested with one product and one record at a time.