VBA Across Multiple Records (Access 2010)

[Scorpio11]

Hi

I am very new to VB Coding in access and am testing a simple things.

I have drafted a simple VBA code in a form button that takes the result calculated in a query and pastes the result into a table.

I have attached my test database to illistrate

In my attached database, i have a list of 5 records, i am wanting to push the calculation button have all the records updated with the result that is calculated in the query in 1 go.



As it stands right now i have to do it one at a time per record, which is not preferable when there are potentially hundreds or thousands of lines. I am wanting to do this because i want to reduce my reliance on calculated cells in forms and reports where i have to draft long formulae across 3 separate queries to get the result i am looking for.

Any help would be appreciated.

[Robeen]

You want to update Result3 with the value in Test7 in each row?
I'm not sure I understand why you need to do this because your query 'Tquery1' is already doing the calculation for you and displaying the result in the 'Test7' field. It seems like you're doing the same thing twice - once in the query - and then once in the Calc button . . .

It IS possible to automate the Calc button but perhaps you could explain with a little more detail?

[Scorpio11]

Hi There

Yes that is correct.

I agree with you, in my example, it is not needed to do this as the result is simple.

The reason why i want to do this is that I have queries containing 3 nested formulas and to get to my FINAL result, i need 3 queries to get there. needless to say, it is a little slow when it comes to running reports and such
I want to do this so i can let access take it time every now and then do what it needs to and calculate the result and then dump the result into a table and generate my graphs and forms and reports based on the table information and not the query. The intention is to speed up my database by cutting the constant recalculating time.

[Robeen]

If you look at this thread:
https://www.accessforums.net/showthr...ted-table-rows
you will see a code outline I put in there that allows you to read rows of data from a query and then write some or all of the data from the query into another table.

The code reads the data from the query one row at a time and before it goes to the next row, it writes the data retrieved from the current query row into a different table.

If this is your first time using code, it will take a little time for you to see what is happening, but I think I have put enough explanation in comments so that you can follow the logic.

Let me know if this helps.