Using DLookup inside a user-defined function which is inside a query

[klar]

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.

 

[Galaxiom]

I have nec=ver seen that message.

However using a DLookup (either inside or outside a function) to process each record in a query can be painfully slow.

You really should try to find another way to perform the query. Can the table you are using the DLookUp on be incorporated directly into the query?

 

[spikepl]

http://office.microsoft.com/en-us/access-help/HV080760122.aspx

At the bottom there is a question:. Did this article help you? Make your opinion known. I did

 

[klar]

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

 

[spikepl]

According to the Microsoft drivel, it's the function itself that is a problem. If you are only viewing data without updates, you could base your form on a temp table and let the query fill that.

Alternatively, drop your function and build the same functionality directly in the query - that should be doable.

 

[Galaxiom]

BTW Concatenation in VBA is nomally done with the ampersand.

Criteria = "ID=" & ID
rather than
Criteria = "ID=" + ID

The addition operation will add numbers if the first section is numeric.

 

[gemma-the-husky]

note that if id is a string (the first parameter in the function) , then your crieria expression will not build correctly

stuff like this can give misleading errors - which may be your problem.

i suspect the function is not quite right.

 

[Galaxiom]

Yes. If ID is a string the criteria would need to be

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

 

[klar]

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

Thank you very much!! That was the solution. The error message from Microsoft led me to a wild goose chase.