Converting function for finding previous value

[toast]

Hello,

I currently use the following function in a subform to successfully retrieve the previous record's value (a date):

Function GetPreviousValue(frm As Form, strField As String) As Variant

On Error GoTo Err_GetPrevValue

    Dim rst As Recordset

    Set rst = frm.RecordsetClone
    
    rst.Bookmark = frm.Bookmark
    rst.MoveNext
    GetPreviousValue = rst(strField)

Exit_GetPrevValue:
    Set rst = Nothing
    Exit Function

Err_GetPrevValue:
    If Err.Number <> 3021& Then
        Debug.Print Err.Number, Err.Description
    End If
    GetPreviousValue = Null
    Resume Exit_GetPrevValue

End Function

I am trying to get the same function to work on a report. So I made the following changes:

Function GetPreviousValue([b]rep As Report[/b], strField As String) As Variant

On Error GoTo Err_GetPrevValue

    Dim rst As Recordset

    Set rst = [b]rep[/b].RecordsetClone
    
    rst.Bookmark = [b]rep[/b].Bookmark
    rst.MoveNext
    GetPreviousValue = rst(strField)

Exit_GetPrevValue:
    Set rst = Nothing
    Exit Function

Err_GetPrevValue:
    If Err.Number <> 3021& Then
        Debug.Print Err.Number, Err.Description
    End If
    GetPreviousValue = [b]"apple"[/b] [COLOR="Lime"]'added to be easily recognised[/COLOR]
    Resume Exit_GetPrevValue

End Function

The calculated control on the report is

=GetPreviousValue([Report],"[i]myfieldname[/i]")

And it always returns "apple"

What do I need to change in order to get this working in the report?

 

[vbaInet]

Your code is not returning the previous value, it's returning the Next value.

You can use the DLookup function.

 

[toast]

The report (and subform) have an ORDER BY DESC, so it is returning the previous value but only because of that - I should have said that in the initial post.

My understanding is that using DLookup is not viable when the records do not necessarily follow an incremental increase in the primary key ID... date is the important thing for this report (and subform) and these dates get added out of sequence on occasion. This means that the latest date is not necessarily the latest primary key ID, and the same for the previous date etc.

 

[vbaInet]

How many times is the function being called in your report?

 

[toast]

It's a calculated control on the report detail, so every line

 

[vbaInet]

What's the idea of needing to do this? We might have a better approach.

 

[toast]

It's a report showing the work history for an employee.

It is necessary to show how long the employee has off between each shift.

So on the subform the calculated control draws the previous record's finish time and subtracts that from the current record's start time. I'd like to replicate this on the report.

It isn't really practical to store that data at the table level because the records get edited.

 

[gemma-the-husky]

if you want the previous value, then why bother "reading" the previous value

just save the current value in a variable as you meet each record - then you have it avaiable to compare with the next value.

 

[toast]

That's an idea I've not come across before - the usual pointers are to either use a subquery or move around a recordsetclone.

I understand exactly what you mean, but I have no idea how I would achieve that?!

 

[gemma-the-husky]

just have a variable in the report's module (or a public variable)

then in the report's detail event (or format, or print, whichever) just say something like

'process the record however you want
process me!somecontrol against lastvalue

'save the new value
lastvalue = me!somecontrol

 

[toast]

That's great - thank you very much!
Very straightforward

 

[toast]

Thread resurrection...
This was working, but since coming back several months later it isn't and I don't understand why.

To summarise - on a report, the previous row's finish time is subtracted from the current row's start time.

Dim lastvalue As Date

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.unboundfield = Me.starttime - lastvalue

lastvalue = Me.endtime

End Sub

It just leaves the unbound field blank on every row

 

[vbaInet]

Use this:

Option Compare Database
Option Explicit

Private lastvalue As Date


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If FormatCount = 1 Then
        Me.unboundfield = Me.starttime - lastvalue

        lastvalue = Me.endtime
    End If
End Sub

Are you sure the bound textboxes used in the code are still bound and are actually displaying values?