Dear Access gurus,

I am not sure if using DLookup inside a user-defined function and then
using that function inside a query is forbidden. When I did that, the
following error is encountered;

"Cannot open a form whose underlying query contains a user-defined
function that attempts to set or get the form's RecordsetClone
property"

I never use RecordsetClone property in my code. Does anyone have any
clues? Thank you.

What's the code of your function? And how are you attempting to use that
function when you get the error?


"Lane" wrote in message
news:a2c0386e-827b-495a-ad5c-07b6d9b29e62 (AT) b20g2000pro (DOT) googlegroups.com...

Dear Access gurus,

I am not sure if using DLookup inside a user-defined function and then
using that function inside a query is forbidden. When I did that, the
following error is encountered;

"Cannot open a form whose underlying query contains a user-defined
function that attempts to set or get the form's RecordsetClone
property"

I never use RecordsetClone property in my code. Does anyone have any
clues? Thank you.

Thanks for the replies. The function which is used inside the query
actually calls another query within. Is this a problem?

The code is below.

Public Function GetRecencyAnl(ID As String, AnnouncementDate As Date)
As Integer
On Error GoTo errorhandler
Dim latest_year As Integer
Dim Criteria As String

Criteria = "ID=" + ID
latest_year = DLookup("Latest_yr", "RecentAnlQry",
Criteria)
GetRecencyAnl = latest_year - Year(AnnouncementDate) + 1

Exit Function
errorhandler:
'MsgBox Err.Source & "-->" & Err.Description, , "Error"
End Function



On Jan 3, 7:47*pm, "Douglas J Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:

On 03/01/2012 23:50:55, Lane wrote:
Assuming ID is a number, try using & instead of +
Criteria = ID = " & ID

Phil

Hello Lane.



You have declared ID as a string value, (. GetRecencyAnl(ID As String, ...), if so, you have to tread it as a string in the query.



Criteria = "ID=" & "'" & ID & "'"



Regards

Jørn

Thank you very much. The problem is solved. The error message from
Microsoft led me to look at the wrong places. Wasted so much time.

On Jan 4, 12:55*pm, "JHB" <jhb... (AT) hotmail (DOT) com> wrote:

JHB wrote:
the expression on the left side of the comparison operator is also a string.
IOW, if the ID field in the table is text, then the value being compared has
to be delimited. If the ID variable (to avoid confusion, it's a good idea to
differentiate your field and variable names) contains a non-numeric value,
the sql will fail if you failed to delimit the value due to a type
conversion failure. But that has nothing to do with why it should be
delimited. It is the field's datatype that is the dermining factor, not the
variable type.

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> skrev i en meddelelse news:je1ob2$dj8$1 (AT) dont-email (DOT) me...
I do agree.



What I meant was that if the ID was a string value, then it should be treated as a string value in the query.



What made me believe that it was a string value was that Lane has declared ID as a string in the procedure call, therefor I wrote "if so", (if it is correct that .., then .).