Update calculations - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update calculations

Masih Ostad Novin

2 years ago

Hi.
I have a simple continuous form. I have an update btn that runs an update query to update Volume. Nothing fancy. Then I have a button that do the calculations: Conc= Volume/ Factor and Diluent= Volume - Conc. Everything is working good, both the update and calculations. Now I want put calculations on Volume that everytime the volume updates so does the calculations. I tried with on change events on volume field and on after update but didn't work. Any idea?
Thanks

	Masih Ostad Novin OP @Reply 2 years ago

	Sami Shamma @Reply 2 years ago
Please show your code. Did you add "Me.Requery" in the after update code?

	Masih Ostad Novin OP @Reply 2 years ago
Private Sub Volume_AfterUpdate() Conc = Volume / Factor Diluent = Volume - Conc Me.Requery End Sub I added me.requery but doesn't work.

	Kevin Robertson @Reply 2 years ago
Create a Sub for your calculations then make a call to that sub after the Update Query runs.

	Masih Ostad Novin OP @Reply 2 years ago
Do you mean in the update button? This is my code: Private Sub UpdateBtn_Click() 'On Error Resume Next DoCmd.OpenQuery "UpdateBondQ" Calculate End Sub Private Sub Calculate() Conc= Volume/ Factor Diluent= Volume- Conc Me.Requery End Sub It still doesn't work.

	Kevin Robertson @Reply 2 years ago
What are you updating that requires an Update Query? What is the criteria? Can't you just perform all the calculations in Calculated Query Fields?

	Kevin Robertson @Reply 2 years ago
In your Update Button you may have to requery the form before running the Calculate sub.

	Masih Ostad Novin OP @Reply 2 years ago
It is working now but only in the first row of the form.

	Kevin Robertson @Reply 2 years ago
You will need a Recordset loop if you have to perform the calculation on all the records.

	Masih Ostad Novin OP @Reply 2 years ago
Thanks.

Masih Ostad Novin OP @Reply

2 years ago

This is my code for the recordset. It still doesn't work but I at least don't get any error messages. The update query works.

Private Sub UpdateBtn_Click()

    Dim rs As Recordset

    Me.Requery

    DoCmd.OpenQuery "UpdateBondQ"
    Me.Requery

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM AntikroppBondT")
    While Not rs.EOF
        doCalculate
        rs.MoveNext
    Wend
    rs.Close

    Set rs = Nothing

End Sub

	Kevin Robertson @Reply 2 years ago
You are trying to EDIT the recordset. You are missing rs.Edit and rs.Update.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View