469,929 Members | 1,379 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,929 developers. It's quick & easy.

Can't get function to work in a query

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
2 3122
PEB
1,418 Expert 1GB
Hi,

Because the method Recordsetclone concern only the forms...

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

:)
Sep 16 '06 #2
Wade
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.

Similar topics

4 posts views Thread by Bruce W...1 | last post: by
3 posts views Thread by Stefan Goerres | last post: by
2 posts views Thread by Stephen Bowyer | last post: by
16 posts views Thread by bobueland | last post: by
6 posts views Thread by Icare-Infographie | last post: by
12 posts views Thread by Balaji.V | last post: by
4 posts views Thread by Franky | last post: by
8 posts views Thread by Sullivan WxPyQtKinter | last post: by
1 post views Thread by Joe Strout | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.