Compile error: Expected End Function

[outdoor]

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?

[Welshgasman]

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?

[CJ_London]

Your insertdata sub is embedded in your mine function - you can’t do that

[davegri]

Try it this way

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

[outdoor]

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

[Welshgasman]

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

[orange]

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

[Welshgasman]

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

[orange]

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.

[Welshgasman]

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.

[moke123]

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 -

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

[Welshgasman]

I thought that the "Run code function name" gave it away as an embedded macro which only runs functions.

Could call it as just -

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.

[moke123]

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.