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

Trinh Nghi 1

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

1 answer

DBG 2,301 Reputation points

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?
Please sign in to rate this answer.
Trinh Nghi 1 Reputation point

2021-04-03T15:39:03.09+00:00

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.

DBG 2,301 Reputation points

2021-04-03T16:09:40.867+00:00

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

Trinh Nghi 1 Reputation point

2021-04-03T18:32:14.667+00:00

Alert "Data type mismatch in critearia expression"

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

DBG 2,301 Reputation points

2021-04-03T18:35:54.993+00:00

Please, listen to what I am trying to say. You don't want/shouldn't update the table with calculated values. Once we figure out the correct DSum() expression, I can show you how to display it without updating the table.

So, if you're getting a type mismatch error, I need to know the data types of the following fields:

Process

PONumber

Trinh Nghi 1 Reputation point

2021-04-03T19:05:58.637+00:00

Sorry Bro.

Your code is 4687. It is correct with first POID. And when I click on each record. The Msg displays correct result accordingly POID. Now how can i update all correct results in TblPO thru TblOutput

I use this code. But only first record is update correctly. Remainings are the same first one.

Private Sub Form_Load()

Dim rs As Recordset
Dim CauSQL As String
Sql = "SELECT * FROM tblPO WHERE Lock=false"
Set rs = CurrentDb.OpenRecordset(sql)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
rs.Edit
rs!ActualQty = DSum("OKQty", "tblOutput", "Process='Final_Inspection' AND PONumber=" & Nz(Me.POID,0))

rs.Update rs.MoveNext Loop

End If
rs.Close

End Sub

DBG 2,301 Reputation points

2021-04-03T19:16:11.053+00:00

Okay, assuming you're saying there was no error with the DSum() expression, in the control where you want to see it on the form, replace the Control Source with the DSum() expression. For example, it might say something like:

=DSum("OKQty", "tblOutput", "Process='Final_Inspection' AND PONumber=" & Nz(Me.POID,0))

Trinh Nghi 1 Reputation point

2021-04-03T19:27:54.727+00:00

But the in Control Source I take it from TblPO. Now on my form it equals ActulaQty. I only create one textbox and add above Dsum in control source. I just want the result update to ActulaQty field.

My form is datasheet.

DBG 2,301 Reputation points

2021-04-03T20:01:35.507+00:00

Hi. You're still not hearing me. Storing calculated value is not recommended. But if you insist, we can still do that. However, I am still trying to ascertain from you if the DSum() expression is working or not. So, if you use the DSum() as the Control Source of a new unbound Textbox, does it show you the correct value (the one that you want to store in your table)?

Trinh Nghi 1 Reputation point

2021-04-03T20:10:14.91+00:00

Thank you so much. When i create one textbox. The results are correct all. The point i concern how to update directly to ActualQty field of tblpo

DBG 2,301 Reputation points

2021-04-03T20:23:47.377+00:00

Okay, thanks for letting us know the DSum() calculation is correct. Now, before I give you the answer to your question, I will try one last time to convince you not to do it.

Can you please tell us why you would want to store the result of the DSum() into the table? I mean, if you take out the field from your table, then you can't store that value, right? However, you must need that value for something. If so, can you please tell us where it is that you need that value. What are you going to use it for?

The reason why we don't recommend you store a calculated value is because it adds a risk to your data integrity. Once a source data for the calculation is changed/updated, your calculated result becomes outdated. Which means, you could be using invalid data in your database (for whatever purpose you were using them).

Instead, the best practice approach is to calculate the value each and every time you need to use it.

Trinh Nghi 1 Reputation point

2021-04-03T20:47:06.243+00:00

Thank you so much. The purpose of this calculation we compare the Plan and actual ouput. On Tblpo we issue plan quantity for each PO. The actual ouput of each PO will take from tblouput. The comparision will let me know the PO is complete or not. Then will lock PO. I will try to find other way to get this update.

DBG 2,301 Reputation points

2021-04-03T20:53:16.673+00:00

Hi. You just lost me. If you don't store the result of the DSum(), there's nothing to compare. So, you may be doing extra work right now, because you created a problem when none existed. If you store the result of the DSum() to compare it, then you're basically comparing it to itself, because the result of the DSum() should be correct anyway. So, again, you may be doing extra work for doing this.

However, if you still insist on storing the value, just assign the result of the DSum() to the field where you want to store it. In other words, you could try something like:

Me.NameOfField = DSum(...all that jazz...)

Just please keep in mind, you are risking data integrity when you do that. You could be comparing invalid values down the line.

Good luck with your project.

Trinh Nghi 1 Reputation point

2021-04-04T01:24:07.94+00:00

Thank bro. I will find out other solution. You are right. Should have simple way to do it. Like export to excel for calculation

DBG 2,301 Reputation points

2021-04-04T02:00:09.35+00:00

Simple is DSum(), which already gives you the correct answer. Besides, I gave you the solution to your original question, that being:

Me.FieldName=DSum()

Not sure what else you have to figure out. Did you try what I said earlier? Just replace the MsgBox line with the new one I just gave you.

Trinh Nghi 1 Reputation point

2021-04-04T05:29:12.14+00:00

Yes now i did as your instruction. The result is correct. Just click one by one and the result will update accodingly. Thank you so much. :) Access is beautifull when have someone like you.

DBG 2,301 Reputation points

2021-04-04T06:15:15.473+00:00

Glad to hear you got it sorted out. Good luck with your project.
Sign in to comment