We help IT Professionals succeed at work.

Beginner Problem: User Defined function works in Immediate Window, but not in Report

cnorrdin
cnorrdin asked
on
Per request:Just copied the entire function TO THE END OF THIS POST.  Adding "Public" did not help, saw no "missing" listed in References. OTHER FUNCTIONS IN THE COPIED MODULE ARE ACTING THE SAME WAY. PERHAPS IT HAS SOMETHING TO DO WITH COPYING MODULE TO DIFFERENT DATABASE.Orig question follows:
Access 2000 function works fine when working from the immediate window, but when I try to use it in a report, I get error msg 2427.
From the immediate window, swtchnm("Nor, SAM") works as designed. when used in a report control's data source, i.e. =swtchnm([FieldName]) I get error msg "2427 You entered an expression that has no value".
The function works fine in reports in the database in which I originally created it. I just copied the module from one db to the one I'm having the problem with. I don't understand this. If I use just [FieldName] as the control's data source the raw value of the field displays. At the error dialog box, Debug points to third line of the SwtchNm function:
Public Function SwtchNm(strtdesc) As String
Dim strgdnm As Variant
strgdnm = InStr(1, strtdesc, ",")
If strgdnm <> 0 Then
SwtchNm = mixed_case((Right(strtdesc, Len(strtdesc) - strgdnm - 1))) & " " & mixed_case(Left(strtdesc, strgdnm - 1))

Else
SwtchNm = mixed_case(strtdesc)
End If
Debug.Print SwtchNm

End Function
Comment
Watch Question

Commented:
Normally, your function would look like this:

Function SwtchNm(strtdesc As String) As String    
...

Note the 'As String' inside the input var ()s.

Author

Commented:
Thanks. I added the "As String", now in report preview the control displays as "Error" but the error msg and dialog box are not displayed.

Author

Commented:
Thanks. I added the "As String", now in report preview the control displays as "Error" but the error msg and dialog box are not displayed.

Commented:
Please post the entire function.
First, I think you have to make your public function:
Public Function SwtchNm(strtdesc) As String
Dim strgdnm As Variant
strgdnm = InStr(1, strtdesc, ",")

If that doesn't work, try checking your references and make sure none of them say missing.
Have you made sure that you have the function mixed_case() and all its dependant subs and functions in your new DB? Maybe making them all public would also help.
As long as you have all the dependant functions and subs there should be no problem with maving a moduele from one place to another.
I'd recomend working thought the code and making sure nothing is missing.

Author

Commented:
Thanks. I believe the logic is right since it works in the immediate window. I was able to meet my immediate need by adding a column which uses the function {i.e. goodname:swtchnm([TDESC])}to the query on which the problem report is based. But why doesn't it work within the report?
Where are you storing this module?  I am thinking it may not be a Public function so the report can't find it.  Just give that a shot first, it will only take a couple of seconds.  It works in the immediate window because you have the function opened already.
Sorry!!  I didn't see your additional comments at the beginning.
If you are running this from the report, make sure you have it in the On_Current Event.
Also, you can not store it in a form and call it (even if it is public)if the form isnt open.  Try putting it in a module if you dont have it there already.
Another thought:
Access will kick out at the first outside call if your references are not set correctly.  What references do you have checked?

Author

Commented:
Thank you PsychoDazey! That was it, there were 2 functions and a sub used by the referenced mixed_case which were private. Making those public allows me to use the swtchnm function in my report. Still don't understand why private classification only prevented use in report and not query. Thank you very, very much!
No problem, glad we could help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.