question

TrinhNghi-2431 avatar image
0 Votes"
TrinhNghi-2431 asked thedbguy commented

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

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

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

thedbguy avatar image
0 Votes"
thedbguy answered thedbguy commented

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?

· 16
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

POID is one field of tblPO. It is same value with POnumber of tbloutput because we use list box from POID to input data. I dont know the DSUM() formular is correct or not. Kindly help to check condition of DSUM in my code. I afraid something wrong.

Just make clear. TblOutput will be key in data accroding to PO Number. and in one TblPO, I would like to update total value according that PO and one condition from Process like "Final_Inspection".

I think about Query and then use Dlookup to find the result thru POID. But in this case I just want to use Code to update.

0 Votes 0 ·

Like I was saying, though, normally, you wouldn't store a calculated value in a table. Also, by using the Load event of the form, you're only updating one record. In any case, try using this code and let us know if the result shown on the screen is correct.

 Private Sub Form_Current()
     MsgBox DSum("OKQty", "tblOutput", "Process='Final_Inspection' AND PONumber=" & Nz(Me.POID,0))
 End Sub
0 Votes 0 ·

84207-image.png




Alert "Data type mismatch in critearia expression"

You are right. On just update only one record. How to update each record with POID accordingly



0 Votes 0 ·
image.png (8.8 KiB)
Show more comments