Copy values from 2 fields in prior record set into 2 different fields in current record set

[hehrerh]

Attachment 32904

You have 10 calculated fields in table YearSetup, which is the table being updated by your requested code. I couldn't analyze the effect of that on changes made to the table data via the code, but here's what I have...
I eliminated all the table fields in the query that weren't involved in the update logic. It was just confusing extra stuff to wade through that had no reason to be there at all.

One hitch.... is there a way to run all calculations on the to-be-copied record BEFORE copying the 2 fields?

1. run calculations on the current record
2. copy the 2 values
3. move to the next record and paste the 2 values
4. repeat

I'm finding that some calculations are not firing between the copy/paste.

Thank you!

[davegri]

Calculated fields in tables are NOT good practice and should be avoided. You have found one reason why.
You can save yourself a lot of trouble by removing those fields entirely from the table and putting them in a query that does the calculations. Then base any forms, reports, etc on that query.

[hehrerh]

Calculated fields in tables are NOT good practice and should be avoided. You have found one reason why.
You can save yourself a lot of trouble by removing those fields entirely from the table and putting them in a query that does the calculations. Then base any forms, reports, etc on that query.

That's no problem and I will do that, but I believe I will have the same issue.

If the code is then applied to the query, the same calculation order problems would persist.

[davegri]

Use a different query for the code. That would update the table.
Then use the query that does the calculations.
If that still presents a timing problem, then perhaps your design is off.

[hehrerh]

Can you please take a look at what I have? I created a query for the table. I moved all calculations into the query qryYearSetup.

I don't quite understand what you mean by using a 2nd query for the code.. should the query the code is run on be based on the table YearSetup (no calcs), or on the query qryYearSetup (with calcs)?

Attached the database. Excuse the additional messy queries and forms.

[davegri]

Database161-davegri-V01.zip
Had to add sort to [YearSetup Query] so that each next record was in correct order for logic to work.

[hehrerh]

Just wanted to follow up and let you know that this did the trick.

Also - thank you for the tip that calculations should take place within queries. This has helped me better organize all of my work.