Display a prior record value with current record value

[Guest]

Is there any way via programming to grab a value to display from the record
just prior to the current record and display it with the current record.

For example, a table has following four fields with data entered:
TransactionID (PK) DateChange Amount InvID
1 8/1/06 $100 1
2 9/1/06 $200 1
3 10/1/06 $300 1

I would like to display in a report or form run for InvID 1 for the period
of 10/1/06 to 10/20/06 the following:

InvID Date of Change Amount prior to change Current Amount
1 10/1/06 $200 $300

TIA

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

 

[Guest]

Thanks Allen. Can you suggest how to adapt module code to a report?

 

[Allen Browne]

You can use Method 1 - the DLookup() in a report.
Just set the ControlSource property the same way.
Main problem with that is the assumption that the sequence of ID numbers is
contiguous: not a safe assumption.

Alternative approaches (query based):
http://www.mvps.org/access/queries/qry0020.htm
There are some limitations with subqueries in reports. If the report does
any sorting/grouping, you may be told it has a "multi-level group-by error".
A workaround is to use a stacked query (one query on top of another) instead
of a subquery.

There is another approach that uses the event of the report sections. If the
control is in the Detail section, declare a variable in the report's module,
Assign a value in the Print event of the Detail section, and that value to
the unbound control in the Format event of the section. This approach is
flawed though: it doesn't work correctly in a report where you don't print
all pages, because the events may fire for the printed pages also.

 

[Guest]

Yes, I’m finding it a bit challenging to try to do what I want in a reliable
way. Dlookup will not work, as there is no guarantee the autonumbers will be
contiguous.

I got the coding example to work that you referenced in your original post,
but in addition to wanting to try to pull out a previous value (which is
actually a calculated value) for InvID 1 on a report, there will be other
InvID’s that may actually end up be the previous record value (in looking at
my original post, I realize, I didn't explain that piece.

Maybe what I’ll have to resort to is storing the calculated value of the
previous record in a field versus trying to always calculate the previous
value on the fly. I posted a question to this effect under the Database
Design topic area titled Calculate on the Fly vs Update Query on 10/19/06,
which more fully explains the design/objective of my tables/db. I realize
calculating the value is usually a better option, than calculating a value
and storing it statically in a field, but I just don’t know how to accomplish
it. Thanks for taking the time to answer my previous posts. I appreciate it.

 

[Allen Browne]

Fair enough Janna.

Access is not good at this kind of thing. You can solve it fairly easily
with a subquery (along the lines of the Michel Walsh article), but IME,
Access doesn't handle these well in this application. The report often gives
the 'Multi-level group-by' error, and if it doesn't Access will sometimes
churn for ages choosing a really inefficient plan, and if it doesn't it will
not infrequently crash as JET is prone to this kind of error.

Still, I wouldn't store the value. There has to be some way to get the
value, possibly by stacking one query on top of another. You may be able to
run one of Michel Walsh's queries in the lower level query, and get it that
way.

It's worth persisting with, and it is possible to find an efficent solution.
I had one of these just a couple of weeks ago. There were 900k records in
the table, so it really was a matter of persisting until we got around the
JET bugs and got a stable, reliable solution.