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

 

[Minty]

The first batch of code doesn't appear to do anything that a subform requery would't achieve, if it does indeed achieve anything at all.
Do you understand the logic flow of it? It makes very little sense if you walk through it.

The second code is super convoluted way to join the results of your query to the records in the query/form.
Just use a left join to QrySmartInvoiceResidualBalance from the source of the second screen shot on the ProductID, problem solved...

And why would you call it a ProductID in one place and a ProductName in another that makes no sense at all.
Finally, and it is very picky, but opening an uneditable recordset lookup query as a dynaset makes no sense - it would be quicker to use a DLookup. It indicates you don't really understand the code you are using.

 

[GPGeorge]

Agreed that a small sample accdb would be useful in following the process.

However, I question the appropriateness of maintaining a calculated field in a table for "CurrentStock" at all.

In a properly designed inventory system, CurrentStock, or QuantityonHand, or whatever term you use, can be calculated via queries dynamically for display in forms and reports as needed.
Storing a calculated value risks update anomalies.

For an example of a more appropriate approach to inventory management, you might like to look at the Northwind Developer template in Access.

For reasons, the currently available template from Microsoft is incomplete. However, I have a download of a properly working version available here.

 

[Minty]

@GPGeorge - This sounded familiar when I answered earlier and lo and behold:

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

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

www.access-programmers.co.uk

It's essentially the same question. With similar answers...