User defined function fails only when VBA running

[DonU]

I have a user defined function defined in a module in an Access 2010 DB that used to work fine when referenced to create a calculated column in a query. Now it has an odd behaviour! When I run the query manually, it works as expected. However, when I run it with a VBA program, it now fails.

After testing, I found that this happens consistently, even if I create a new DB (or if I go back to 2007 and do it there), recreate the function (or ANY function), create a query that will use it, and then create a simple form with a single button that only has the STOP command in the VBA code. If I run the query manually it's fine, but if I leave it open (or rerun it after) and load the form and press the button, the calculated column changes from the real values to #ERROR. Then if I let the button code continue, or stop the code, the values instantly reappear. When the STOP is encountered the function can be successfully called in the Immediate window.

I even tried it with a simple function that returns the length of the string passed in (1 line) and ran it both ways with the same results, so there is nothing wrong with the original function.

Is there something that is causing the function to be inaccessible when code is running? This seems to have been a recent development and I'm starting to wonder if MS put something in in one of the Windows/Office updates.

Any help greatly appreciated.

 

[Andrzejek]

Could you show here a simple example of your function, where do you place it in code (form? Module?) and where and how do you call it in code?

Have fun.

---- Andy

 

[DonU]

Here is the test db I created. If you click the button on the form it runs the query with #ERROR in the calculated field while the STOP command is in effect. As soon as you continue (and the code completes) the values fill in

 

[DonU]

Well that didn't work!

Here is the function code from the test.

'*********************************************************************
' Purpose: Get a count of stores from the given field w/ delimter
' Inputs: Ref. to string
' Delimiter to split by
' Returns: Number of store in given string
'*****************************************************
Public Function StoreCount(strref As String, strDelim As String) As Integer
Dim intCount As Integer
Dim intPos

If strref = "" Or IsNull(strref) Then 'Empty/Null string
StoreCount = 0
Else
intPos = 1
intPos = InStr(intPos, strref, strDelim) 'Find first delimiter

Do While intPos <> 0 'Continue until last found
intCount = intCount + 1
intPos = intPos + 1
intPos = InStr(intPos, strref, strDelim)
Loop

StoreCount = intCount + 1 'Store count = #/delim's + 1
End If

End Function


Public Function Test(strref As String) As Integer
Test = Len(strref)
End Function


The code is called from a form button with
Private Sub Command0_Click()
DoCmd.OpenQuery "Query1"
Stop

End Sub

And the query calc'd field is as follows
Expr1: StoreCount([Table1]![Ref 1],'/')

Field [Ref 1] is a text field with data like the following:
604/601/802 MS
234 SS
436 BC

 

[DonU]

Ooops - The function code is in a module. I tried it in the form but the function was unrecognized by the query when it was placed there.