Full Version: Why Is Access Saying My Public Function Is An Undefined Expression?
UtterAccess Forums > Microsoft® Access > Access Forms
I'm just starting to learn VBA cod and have really got hung up on something. I wrote a Public Function in the VBA editor called UpdateEmail (The hyperlink data mode stores the address as “*****.com#mail to: *****.com#” and outlook doesn’t recognize the email address like that. So I have a code that will erase everything in the email addresses starting with the “#”). Anyway when I write the name of the public Function in the query it gives me an error saying the Function is an undefined expression. Does anybody have any idea why it's saying this? The Function is saved and stored as a standard module. I’m thinking it might have something to do with my references and the libraries being used but don’t really know how to fix the problem. Would very much appreciate any help.
Thanks! Here's the code:
Public Function UpdateEmail(strInput As String)
'Remove hyperlink from all e-mail addresses
If InStr(strInput, "#") = 0 Then
UpdateEmail = strInput
strInput = Left(strInput, InStr(strInput, "#") - 1)
End If
End Function
Ogot most of this code from somewhere else and this is unrelated but after "Else" is it supposed to say "strInput = " like that or should it say "UpdateEmail = "?
That's the name of the standard module you've saved ? It would need to be called something other than UpdateEmail - eg modEmail. You can't have the name of a module as the same name as any subs/functions it contains.
I'd also include the datatype for the return of the function -
Public Function UpdateEmail(strInput As String) As String
and delete where you have Test:, which would throw a compile error I'd imagine. Does your code compile OK (Debug-->Compile). If it doesn't, you need to fix any errors highlighted by the compiler - and make sure you have Option Explicit at the top of the module right underneath where it says Option Compare Database
Further to Alan's reply,
You should be assiging a value to the Function name (UpdateEmail = ....) before the function terminates.
You guys are great. It worked perfectly. Thank you.
glad we could help
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.