Easier method to refer to previous record?

Hey Gang. Well, thanks to the Access World Forum's many helpful members, I'm venturing into coding some VBA, rather than using multiple hidden forms, queries, etc. to create convulted code-free solutions. Here's the latest item to be re-examined, but I haven't spotted the VBA solution yet...

Take a simple table that includes just dates and amounts:

TABLE: tblReadings

FIELD: timeReadingDate PRIMARY KEY
TYPE: Date/Time
REQUIRED: Yes
INDEXED: Yes, no dupes

FIELD: longReadingAmount
TYPE: Long Integer
REQUIRED: Yes
INDEXED: No


Now, it seems silly to me to record the difference/volume with every record, since that's a calculated value (today's reading, less yesterday's reading, equals yesterday's volume). However, my current implementation seems inelegant.

First, I have a query that repeats the two original fields, sorting the reading table in ascending order by the dateReadingDate field:

QUERY: qryReadingsByDates

FIELD: timeReadingDate
TABLE: tblReadings
SORT: Ascending

FIELD: longReadingAmount
TABLE: tblReadings
SORT: None


Then I have a second, separate query that takes the first one, repeats the fields again, and adds the longVolume field:

QUERY: qryReadingsWithVolumes

FIELD: timeReadingDate
TABLE: tblReadingsByDates
SORT: None

FIELD: longReadingAmount
TABLE: tblReadingsByDates
SORT: None

FIELD: longVolume:

The longVolume field contains the following expression

longVolume: IIf([timeDate]=DMin("[timeDate]","qryReadingsByDate"),[longAmount],[longAmount]-(DSum("[longAmount]","qryReadingsByDate","[timeDate] =#" & (DMax("[timeDate]","qryReadingsByDate","[timeDate] < #" & [timeDate] & "#")) & "#")))


Then, if I want to to take a further step (say, for example, examining readings and volumes that occur between selected dates), it means adding a THIRD query, making the chain even LONGER...

It occurs to me to wonder if there's not a shorter and faster way to do this in a VBA function. Surely there's something better than slogging through a series of 2 (MINIMUM!) nested queries every time I want to include the volume on a form or report?

All ideas welcome...

--Newbie-Once-Again Mac

FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.

' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************

Function PrevRecVal (F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?

Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"

Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(FieldNameToGet)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

Thank you VERY much for the reply, Rich! Glancing at the code, I'm not sure this is precisely what I need, since it (1) is still necessary to go through a form and (2) depends upon the records being in ascending order. It certainly gives me a jumping-off point, though... I'll let you know what I end up with. Thanks!

Why would you not want daily readings in ascending order? The function can also be used on a report with minor alterations. Where else would you want to use it?

You'd always want your records in ascending order, but you can't count on your user to ENTER them in ascending order

Here's what I came up with:


Public Function longGetVolume(timeTargetDate As Date) As Long
' PURPOSE: Calculate variance in field from previous record.
' ARGUMENT: timeTargetDate = The date for which variance is sought.
' RETURNS: difference in "longAmount" field between record where "timeDate" = timeTargetDate ...
' ... and record where "timeDate" has the highest value not exceeding timeTargetDate
' EXAMPLE: longVolume = longGetVolume([timeDate])

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblReadings ORDER BY timeDate")

rst.findlast "timeDate <= #" & timeTargetDate & "#"
If rst.NoMatch = False Then
longTodayAmount = rst.Fields("longAmount")
rst.MovePrevious
If rst.BOF Then
rst.MoveFirst
longYesterdayAmount = 0
Else
longYesterdayAmount = rst.Fields("longAmount")
End If
longGetVolume = longTodayAmount - longYesterdayAmount
Else
rst.MoveFirst
longGetVolume = rst.Fields("longAmount")
End If

End Function

Using the SQL with "ORDER BY" to create the recordset ensures ascending order by date, allowing the use of MovePrevious. I hope this means I can add a calculated Volume field to any query, no matter what that query does in manipulating the underlying table.

I'd be very grateful for any insights on the code...

Hi, I have been searching for days now for a solution along the lines of the original poster of this topic (hope you don't mind me jumping in here)

I have been trying to use the code listed by rich in a report. I am wanting the previous date from a date field to display (although I will hide it) in an unbound text box on the report so I can compare them to get no. of days between them.

I'm Stuck!!

I've tried every conceivable option, Rich tantilizes with a statement that it is easy to modify for reports. I have changed the Dim rst As DAO.Recordset, ive changed the F as Form to F as Report and am pretty sure the call in the unbound text box on the report is right =PrevRecVal(Report,"MyID", [MyID], [Date])

Am I barking up the wrong tree!!

Any help or comment greatly appreciated

Regards, Rach

Much easier to use the tag property to lever out the previous date
txtChange = 0

If (Me.Section(0).Tag <> "") Then
txtChange = (Me.Section(0).Tag)
End If
Me.Section(0).Tag = Me.DatePaid