Help: Update Value calculation from one table into one table?

Trinh Nghi 1 Reputation point
2021-04-03T13:57:56.88+00:00

Dear all,

I have 2 tables. One TblOutput have some fields: PONumber, OKQty, Process. And one table TblPO have fields: POID, ActualQty that i take them into one Subform.

When I open the form, The Dsum will calculate from TblOutput and update on tblPO. I write the VBA code as below but I dont see any update. Please help me whether code is correct or not?

Private Sub Form_Load()

On Error GoTo ErrorHandler
Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT * FROM TblPO where lock = No"

Set rs = CurrentDb.OpenRecordset(sql)

With rs

If Not .BOF And Not .EOF Then

    If .Updatable Then


        .Edit          
        ![ActualQty] = DSum("[OKQty]", "TblOutput", "[Process]='Final_Inspection'" And "[PONumber]= Me.POID")
        .Update

    End If
End If

.Close

End With

ExitSub:
Set rs = Nothing

Exit Sub

ErrorHandler:
Resume ExitSub

End Sub

Microsoft 365 and Office | Access | Development
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. DBG 2,381 Reputation points Volunteer Moderator
    2021-04-03T14:50:05.83+00:00

    Are you sure that DSum() is producing a value? What is in Me.POID? Is your form bound to tblPO? Storing calculated values is against normalization rules and not recommended. Why do you want to do it?


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.