How to ensure that the current stock control is update when the data appears on the subform in access (1 Viewer)

[nectorch]

When I run the requery below the data appear in all two subforms and allows me to edit and save, see the VBA query below:

Private Sub CbotrIssues_Click()
On Error GoTo Err_Handler
Dim LTAudit As String
LTAudit = Nz(DLookup("IssueStatus", "tblIssueSlip", "SlipID =" & Me.CbotrIssues))
Me.Filter = "SlipID = " & Me!CbotrIssues.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![SfrmWIP Subform].Form.RecordsetClone
    Set Records = Me![sfrmBOMQuantities 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_CbotrIssues_Click:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CbotrIssues_Click
End Sub

Now what I want is when the data appears in the subform attached shown below I want the control called currentstock be updated, I tried to use on dirty nothing is working

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.ProductName
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
Me.ItemID = Me.txtPosition

 

[theDBguy]

It might be easier to see what you mean if you could post a sample db showing the problem. I don't understand your first code why you set a recordset to something and set it again to something else right after you just set it. Also, your second code is using a recordset, but your attachment looks like a query, I think, those are two different things.

 

[nector]

I don't understand your first code why you set a recordset to something and set it again to something else right after you just set it I don't understand your first code why you set a recordset to something and set it again to something else right after you just set it

There two subform which need to be edited and that code works very well no issues at all