Rich - A Question If You Don't Mind?

[Bluezman]

May I contact you about a question I have regarding the PrevRecVal function?

You may contact me via email or instant message.

Thanks!

Bluez

 

[Mile-O]

 ' 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(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 = CurrentDb().OpenRecordset("tblName", dbOpenDynaset)

            ' 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) + 1

Bye_PrevRecVal:
            Exit Function
Err_PrevRecVal:
            Resume Bye_PrevRecVal
         End Function

 

[Bluezman]

No, it's a function found on the MS Knowledge Base that is used to get a value from a previous record on a form for use on the current record.

In my case, I'm using it to calculate the differences in days between events. While in the details section of the subform, this calculation works great. It's only when I'm trying to use a SUM() on the subform footer that I get an #error.

My question to Rich (who seems quite fluent in PrevRecVal's use) is whether or not it can be referenced in an aggregate calculation such as a SUM().

If you'd like to take a look at my problem getting this to work, go to the FORMS area and look for a post with attached mdb from me.

Thanks!

Bluez

 

[ColinEssex]

is Mile-O now the new Rich?

 

[Mile-O]

I just think these things are better worked out on the forum so when others come to search for things they are easily found rather than tucked away in people's Private Messages and emails.

 

[Rich]

I haven't had the pm option enabled for a long time Mile