Calculated query based on Next Record ??? (1 Viewer)

[dudezzz]

I need to develop a calculated query based on values from next record in a single table. After searching the forum, I was able to find some posts that were similar to what I want but I am unable to understand / draw a parallel with my situation.

Please look at the attachment to see what I want to do. Thank you for your time.

 

[RichO]

There seem to be a number of questions on this forum about looking at the previous or next record in a query and usually they go unanswered.

Are you wanting to display the results in a form or report? All I can suggest is to work with a recordset in VBA. That allows you to move around the records using MoveNext and MovePrevious.

 

[dudezzz]

You are right; some of the questions that I saw were not answered fully.

I would like to display the result in a Report. I would grateful if you could walk me through the procedure that you have in mind for this scenario that I have explained....Many Thanks!

 

[RichO]

As I always enjoy a bit of a challenge, I toyed around with this for a bit and here's what came up with:

I set up the table with a field "Y_Calc" to store the result after it is calculated. Normally storing calculated values is frowned upon but this is a different situation and it will always be recalculated when the report is opened. I then designed a report based on this table.

In the report's On Open event I wrote a piece of code to open the table as a recordset and then loop through it doing the calculation of "Y", and updating the table with the correct value. After the code is run the report opens showing the records and the calculated "Y" value.

I've attached the sample DB so you can look it over and see how I did this.

 

[dudezzz]

Brilliant!

Thank you Richo!

This is a Brilliant solution. I briefly checked the report and the table you sent. I am going to use your code in my database and see if I can adapt your code to other situations like this.

If I get into any problems I will update this thread again.

Thanks for helping me out here.

 

[dudezzz]

The report's Onopen event opens the table as a Recordset in your example.

Since I am collecting the information from various tables, I replaced your table with a query.

I ran the Report's Onopen event in step mode and found out that the Total # of recordset showing up as just 1 instead of 3 (even though the query shows 3 records when I ran it separately)

Would someone be able to help me out with this problem please?

 

[RichO]

Right before the line rs.MoveFirst insert a line rs.MoveLast.

I forgot to put that in. MoveLast forces the entire recordset to load and then MoveFirst jumps to the first record.

That should fix the problem.

 

[dudezzz]

Thank you. That worked like a charm!