Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Access VBA 2010: Undefined function

    I have a function
    Code:
    Public Function SplitPart(LSTRSP_EML_EMAIL)
    Dim LSTRSP_EML_EMAIL As String
    
    'Dim LSTRSP_EML_DOMAIN As String
    
    Split([LSTRSP_EML_EMAIL], "@", 2) = [LSTRSP_EML_EMAIL]
    
    
    SplitPart_Exit:
        
        Exit Function
    
    SplitPart_Err:
        MsgBox Error$
        Resume SplitPart_Exit
    End Function
    Which I have called in my query: LSTRSP_EML_DOMAIN: SplitPart().

    I am getting an "undefined function "SplitPart' in Expression. I have verified my references in VBA--see attached file. So far I have only created and worked on this database using Access 2010 and have not attempted to open it in another version. OS Win 7 Professional. Not sure whether the problem is actually with my code or my references.

    Your help, as always, is greatly appreciated.
    Attached Images Attached Images

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Hi,

    Welcome to the Lounge.

    Did you try to compile your function before using it?
    Last edited by ruirib; 2012-05-03 at 12:47.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by djsmith View Post
    I have a function
    Code:
    Public Function SplitPart(LSTRSP_EML_EMAIL)
    Dim LSTRSP_EML_EMAIL As String
    
    'Dim LSTRSP_EML_DOMAIN As String
    
    Split([LSTRSP_EML_EMAIL], "@", 2) = [LSTRSP_EML_EMAIL]
    
    
    SplitPart_Exit:
        
        Exit Function
    
    SplitPart_Err:
        MsgBox Error$
        Resume SplitPart_Exit
    End Function
    Which I have called in my query: LSTRSP_EML_DOMAIN: SplitPart().

    I am getting an "undefined function "SplitPart' in Expression. I have verified my references in VBA--see attached file. So far I have only created and worked on this database using Access 2010 and have not attempted to open it in another version. OS Win 7 Professional. Not sure whether the problem is actually with my code or my references.

    Your help, as always, is greatly appreciated.
    Unless you've missed out or abbreviated a lot of code, I'm not sure how your function is supposed to work!

    You haven't specified a return type for the function; your function definition doesn't include a line that returns a value from the function; you declare the function to take 1 input parameter but if you just call it with SplitPart() then you're not passing in any arguments; you have a local variable declared with exactly the same name as the function parameter; and the line that begins Split(...) = surely won't work because Split() is an inbuilt function that should appear on the right hand side of an assignment.

    Very confused how this function will actually work (if it compiles at all)!

  4. #4
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    No, but that could well be my problem.

    This is a drawn out process that I am trying to simplify with code. An Excel file is created from a report pulled from an online web database. This report gives us all subscriptions even if they have expired or the person no longer has an active membership. The contents of this report are then copied to the clipboard.

    My Function Leg_ENews() cleans out the data in the table, pastes the contents from the clipboard, Runs a couple of queries to clean out inactive records then a delete query is ran to clean out the old data and prepare the table for the new. Without the split function the rest of the code runs flawlessly. I may not be calling the Public Function correctly. Or figuring out how to compile the code as I do not understand a whole lot about programming.

    Code:
    Function Leg_ENews()
      
        
        DoCmd.OpenTable "Leg E-news", acViewNormal, acEdit
        DoCmd.RunCommand (acCmdSelectAllRecords)
        DoCmd.RunCommand (acCmdDeleteRecord)
        DoCmd.SetWarnings (False)
        DoCmd.RunCommand (acCmdSelectAllRecords)
        DoCmd.RunCommand (acCmdPaste)
        DoCmd.Close acTable, "Leg E-news"
        DoCmd.OpenQuery "Leg E-news_Expirey", acViewNormal, acEdit
        DoCmd.Close acQuery, "Leg E-news_Expirey"
        DoCmd.OpenQuery "Leg E-news_Remove_Duplicate_MBRCAT_CODE", acViewNormal, acEdit
        DoCmd.Close acQuery, "Leg E-news_Remove_Duplicate_MBRCAT_CODE"
        DoCmd.OpenQuery "Leg E-news_Delete_Query", acViewNormal, acEdit
        DoCmd.SetWarnings (False)
        DoCmd.OpenQuery "Leg E-news_append", acViewNormal, acEdit    'LSTRSP_EMAIL_DOMAIN calls the Public Function SplitPart
        DoCmd.SetWarnings (False)
        DoCmd.OpenTable "Leg E-news_Final", acViewNormal, acEdit
        
        
        
    Leg_ENews_Exit:
        
        Exit Function
    
    Leg_ENews_Err:
        MsgBox Error$
        Resume Leg_ENews_Exit
    
    End Function
    Thank you very much for your assistance.

  5. #5
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have attempted to address each of the points you made, I just am not sure I am really clear on some of the terms. Below is my updated SplitPart () with comments to see if I am understanding your points.

    Code:
    Public Function SplitPart(LSTRSP_EML_EMAIL As String) ' The email field is a string
    
    Dim LSTRSP_EML_DOMAIN As String ' The domain is a string
    
    LSTRSP_EML_DOMAIN = Split([LSTRSP_EML_EMAIL], "@", 2)   ' I am spliting LSTRSP_EML_EMAIL at the @ sign and passing the email domain to LSTRSP_EML_DOMAIN
    Return  ' I am returning LSTRSP_EML_DOMAIN
    
    ' my query field is : LSTRSP_EML_DOMAIN: Split([LSTRSP_EML_EMAIL],"@",2)
    
    SplitPart_Exit:
        
        Exit Function
    
    SplitPart_Err:
        MsgBox Error$
        Resume SplitPart_Exit
    End Function
    No it still is not working. I just wish I could get a better handle on coding, I would love to understand it. Thank you for your patience.

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    For a function to return a value, you need to assign the value to the name of the function:

    SplitPart =
    LSTRSP_EML_DOMAIN

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by djsmith View Post
    I have attempted to address each of the points you made, I just am not sure I am really clear on some of the terms. Below is my updated SplitPart () with comments to see if I am understanding your points.

    Code:
    Public Function SplitPart(LSTRSP_EML_EMAIL As String) ' The email field is a string
    
    Dim LSTRSP_EML_DOMAIN As String ' The domain is a string
    
    LSTRSP_EML_DOMAIN = Split([LSTRSP_EML_EMAIL], "@", 2)   ' I am spliting LSTRSP_EML_EMAIL at the @ sign and passing the email domain to LSTRSP_EML_DOMAIN
    Return  ' I am returning LSTRSP_EML_DOMAIN
    
    ' my query field is : LSTRSP_EML_DOMAIN: Split([LSTRSP_EML_EMAIL],"@",2)
    
    SplitPart_Exit:
        
        Exit Function
    
    SplitPart_Err:
        MsgBox Error$
        Resume SplitPart_Exit
    End Function
    No it still is not working. I just wish I could get a better handle on coding, I would love to understand it. Thank you for your patience.
    In addition to ruirib's comment, the "As String" in the function declaration needs to come outside the closing parenthesis. Also you seem to be missing an On Error Goto statement to tell your function to go to the error handling label when there is an error.

  8. The Following User Says Thank You to jeremybarker For This Useful Post:

    djsmith (2012-05-10)

  9. #8
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you both! I have removed the 'as string' from the function declaration. and declared it as a variable. Then I added SplitPart = LSTRSP_EML_DOMAIN before the Return.

    I went through my VBA book and figured out the Goto on error, I added this statement 'On Error GoTo SplitPart_Err' above the variable declarations.

    I have tried to debug the code. I think that because I am not calling this from another module the step through (compiler) does seem to be working. There is no table or query for the code to see the email field. Yes, I am rambling, but I am going to try to add code that would cause it to open the query and then run the SplitPart().

  10. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    When you code, there is an immediate window below the code window. You can call the function from there, with a syntax similar to this:

    ? SplitPart("AnExampleValue")

  11. #10
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    The Split function will generate a string array, and in this case with email addresses, the first item will be the user name and the second the "domain" name.

    Looking at the comment next to the docmd.openquery "Leg E-news_append", you only want the "domain".

    If so, Split is not the best function.

    Use
    domain= mid(strEmailAddress, instr(strEmailAddress,"@") +1)

    This will find the position of the @ in the email address, and give only characters after that position.

  12. The Following User Says Thank You to Cronk For This Useful Post:

    djsmith (2012-05-10)

  13. #11
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry, I have been tied up with a few other projects. Just tried the statement suggested by Cronk--Thank you a thousand times, works like a dream. I appreciate everyone's help out here.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •