Results 1 to 13 of 13
  1. #1
    outdoor is offline Novice
    Windows 11 Access 2019
    Join Date
    Dec 2023
    Posts
    8

    Compile error: Expected End Function

    When i try to run a macro from Access : Runcode Function name mine()


    I get the compile error

    Here is my module


    Public Function mine()


    Sub InsertData()
    DoCmd.TransferText transfertype:=acImportDelim, _
    specificationname:="CATS Spec", _
    tablename:="CATS D", _
    filename:="C:\Users\GrahamS4\Desktop\CATS.csv", _
    hasfieldnames:=True
    End Sub

    End Function


    As you can see i have the end function. Why do i get this error?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,772
    You start with a Sub and end with an End Function???

    Which one do you actually want?
    As you are not returning anything I would go with Sub?, both top and bottom.

    In fact looking closer, I can see the Sub is inside the Function????

    Looking even closer, I have no idea what you are trying to do? Why do you have a sub inside a function?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    Your insertdata sub is embedded in your mine function - you canít do that

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,373
    Try it this way

    Code:
    Public Function mine()
    	Call InsertData
    End Function
    
    
    Sub InsertData()
    	DoCmd.TransferText transfertype:=acImportDelim, _
    	specificationname:="CATS Spec", _
    	tablename:="CATS D", _
    	filename:="C:\Users\GrahamS4\Desktop\CATS.csv", _
    	hasfieldnames:=True
    End Sub

  5. #5
    outdoor is offline Novice
    Windows 11 Access 2019
    Join Date
    Dec 2023
    Posts
    8
    Maybe I'm not understanding something. I start with function mine() and end with end function. I'm trying to have a macro trigger the Sub to end sub code

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,772
    Quote Originally Posted by outdoor View Post
    Maybe I'm not understanding something. I start with function mine() and end with end function. I'm trying to have a macro trigger the Sub to end sub code
    Definitely, see post #3
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    This line is confusing: "I'm trying to have a macro trigger the Sub to end sub code".

    What macro??

    CJ has described the issue. Davegri has shown you how to set up the code whereby
    your Function mine() will call and execute your Sub InsertData

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,772
    Quote Originally Posted by orange View Post
    This line is confusing: "I'm trying to have a macro trigger the Sub to end sub code".

    What macro??

    CJ has described the issue. Davegri has shown you how to set up the code whereby
    your Function mine() will call and execute your Sub InsertData
    Jack,
    Those who come from an Excel background call any VBA as macros, as that is what they are called in Excel.
    Nothing at all like Access macros
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    Paul,
    How do/did you know the OP came from excel?
    I've seen posters who call any vba code 'macro', but outdoor is new to the forum.

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,772
    Quote Originally Posted by orange View Post
    Paul,
    How do/did you know the OP came from excel?
    I've seen posters who call any vba code 'macro', but outdoor is new to the forum.
    Assumption TBH, but as it happens a fair bit, that would be my first guess.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,636
    When i try to run a macro from Access : Runcode Function name mine()
    I thought that the "Run code function name" gave it away as an embedded macro which only runs functions.

    Could call it as just -

    Code:
    Public function InsertData()
        DoCmd.TransferText transfertype:=acImportDelim, _
        specificationname:="CATS Spec", _
        tablename:="CATS D", _
        filename:="C:\Users\GrahamS4\Desktop\CATS.csv", _
        hasfieldnames:=True
    End function
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,772
    Quote Originally Posted by moke123 View Post
    I thought that the "Run code function name" gave it away as an embedded macro which only runs functions.

    Could call it as just -

    Code:
    Public function InsertData()
        DoCmd.TransferText transfertype:=acImportDelim, _
        specificationname:="CATS Spec", _
        tablename:="CATS D", _
        filename:="C:\Users\GrahamS4\Desktop\CATS.csv", _
        hasfieldnames:=True
    End function
    Ah, I see. I hardly ever use Access Macros TBH, but I do recall I had to do the same for something I had to back up a database a good few years ago.
    However that only called a single function, though it never returned anything, which in my mind a function should do, but as Access will not run subs that way? .......
    I did not chuck extra subs/functions inside it though.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,636
    Quote Originally Posted by Welshgasman View Post
    Ah, I see. I hardly ever use Access Macros TBH, but I do recall I had to do the same for something I had to back up a database a good few years ago.
    However that only called a single function, though it never returned anything, which in my mind a function should do, but as Access will not run subs that way? .......
    I did not chuck extra subs/functions inside it though.
    Yea, I only use an autoexec macro on startup, nowhere else. Can't execute subs from there however, they have to be functions.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compile Error : Expected End of Statement
    By Shamli in forum Queries
    Replies: 12
    Last Post: 08-20-2018, 12:47 PM
  2. Compile Error: Expected: list separator or )
    By Voodeux2014 in forum Programming
    Replies: 11
    Last Post: 04-21-2015, 10:23 AM
  3. Compile error: Expected end of statement
    By ritati in forum Macros
    Replies: 1
    Last Post: 12-05-2014, 05:33 AM
  4. Replies: 6
    Last Post: 11-24-2011, 08:38 PM
  5. compile error: expected end of statement
    By RedGoneWILD in forum Programming
    Replies: 5
    Last Post: 07-29-2010, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums