By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 800 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Can't get function to work in a query

P: 7
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(Form,"ID",[ID],"OdometerReading")
'************************************************* *************
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.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
Sep 15 '06 #1
Share this Question
Share on Google+
2 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

Because the method Recordsetclone concern only the forms...

For queries there is something other:Application. Screen.ActiveDatasheet

:)
Sep 16 '06 #2

P: 7
Hi,

Because the method Recordsetclone concern only the forms...

For queries there is something other:Application. Screen.ActiveDatasheet

:)
Thanks, I'll see if I can work out the code.
Sep 18 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.