I am pretty new to Access, especially writing code, but I found code on the web to do just what I want and it is posted below. It returns a field value from a previous record. I found the code at: http://support.microsoft.com/?kbid=210504; many of you have probably seen this code. I have an Access form and a query, both based on exactly the same table, a simple two field table with an ID field and a field of consecutive numbers in consecutive records. The function works when I call it within the form but not when I call it within the query. I have Access 2003. I'm developing an application for a client and am learning Access as I go along. My inexperience is quite apparent here and I need some help. Can someone tell me why the function will work in a form but not a query?
'************** *************** *************** *************** **
' 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(For m,"ID",[ID],"OdometerReadi ng")
'************** *************** *************** *************** ***
Function PrevRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As DAO.Recordset
On Error GoTo Err_PrevRecVal
' The default value is zero.
PrevRecVal = 0
' Get the form recordset.
Set RS = F.RecordsetClon e
' Find the current record.
Select Case RS.Fields(KeyNa me).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(FieldNameToG et)
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function
'************** *************** *************** *************** **
' 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(For m,"ID",[ID],"OdometerReadi ng")
'************** *************** *************** *************** ***
Function PrevRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As DAO.Recordset
On Error GoTo Err_PrevRecVal
' The default value is zero.
PrevRecVal = 0
' Get the form recordset.
Set RS = F.RecordsetClon e
' Find the current record.
Select Case RS.Fields(KeyNa me).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(FieldNameToG et)
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function
Comment