User-defined function in query fails when VBA running

Question

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.

Answer 1

I think the #error in this case is normal.

All code is stopped when it hits the Stop in the code.  If I add a Wait to your code so that the query can finish processing before the stop, there is no #error returned.

Private Sub Command0_Click()

    DoCmd.OpenQuery "Query1"

    Wait 1

    Stop

End Sub

Add the function to a module.

Public Function Wait(intPauseSeconds As Integer) As Boolean

    On Error GoTo Err_Proc

    Dim sngStart As Single

    ' intPauseSeconds duration.

    sngStart = Timer   ' Set start time.

    Do While Timer < sngStart + intPauseSeconds

        DoEvents    ' Yield to other processes.

    Loop

    Wait = True

Exit_Proc:

    Exit Function

Err_Proc:

    MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbCritical, "Error"

    Wait = False

    Resume Exit_Proc

End Function

Answer 2

Is the code in a module and is the function public?

Answer 3

Can you post the function?

The function is :

Public Function StoreCount(strref As String, strDelim As String) As IntegerDim intCount As IntegerDim intPos

If strref = "" Or IsNull(strref) Then 'Empty/Null string  StoreCount = 0Else  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 + 1End If

End Function

and the query ref's as below with [Ref 1] being a text field with data like "202/305 BS" or "106 TN" :

StoreCnt: StoreCount([Table1]![Ref 1],'/')

I have even tried a simple test fn as below with same results.

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

Answer 4

Can you post the function?

Answer 5

Is the code in a module and is the function public?

Yes  - the fn is in a public module. If I put it in the a form the query never can see it.