Call Function

I've written a VBA function that I then need to call in a form.
Previously to do this I have written the call procedure in the same
module as the function - i.e. Function callfunctionname()
Call functionname
End function

However, although the function itself works, the call procedure
doesn't. I've tested it in another db just to check there wasn't a
problem with the db itself, but it still doesn't work. I've also
checked that my code doesn't have an error, yet if I run it manually
from the module it does what is required. Can anyone give me any tips
as to why the call doesn't work?

Thanks,

Lucy Randles

re: Call Function

Lucy Randles wrote:[color=blue]
> I've written a VBA function that I then need to call in a form.
> Previously to do this I have written the call procedure in the same
> module as the function - i.e. Function callfunctionname()
> Call functionname
> End function
>
> However, although the function itself works, the call procedure
> doesn't. I've tested it in another db just to check there wasn't a
> problem with the db itself, but it still doesn't work. I've also
> checked that my code doesn't have an error, yet if I run it manually
> from the module it does what is required. Can anyone give me any tips
> as to why the call doesn't work?
>
> Thanks,
>
> Lucy Randles[/color]

I'm not sure I understand your problem. Is the procedure located in a
Module or in the code module for the form? If it is in a module, is the
procedure proceeded with the word Private? Ex:
Private Sub ABC()
If so, change it to Public Sub ABC()

re: Call Function

You should change Private Sub Dataextract() to Public Sub Dataextract()

"Lucy Randles" <lucy.randles@synectics-solutions.com> wrote in message
news:1102088958.l7/578jEdK3B8QgteQilng@teranews...[color=blue]
> Maybe if I put some of the code in you would be able to understand?
>
> My module basically does this, the call function being at the bottom...
>
> Option Compare Database
> Option Explicit
>
> Private Sub Dataextract()
> Dim strsql As String
> Dim todaydate As String
> Dim month As String
> Dim monthupper As String
> Dim year As Integer
>
> todaydate = Date
> month = VBA.Format(DateAdd("m", -12, todaydate), "mmm")
> monthupper = StrConv([month], 1)
> year = VBA.Format(DateAdd("m", -12, todaydate), "yyyy")
>
> STRSQL STUFF - DOES COMPILE
>
> CurrentDb.QueryDefs("A02 Insert All Sales").SQL = strsql
>
> Exit Sub
>
>
> End Sub
>
>
> Function callDataextract()
> Call Dataextract
> End Function
>
>
>
> Obviously I've taken out the sql script. Anyway, that runs, and runs
> fine, but the call function doesn't return anything. It obviously does
> 'something' as I don't get an error, but it doesn't run the module, as I
> want it to do.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

re: Call Function

That doesn't make any difference. As I tried to explain, but didn't
very well, the procedure itself works, it's when I try to call it like
so - "Call Dataextract" in a module or form that nothing happens.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Call Function

"Lucy Randles" <lucy.randles@synectics-solutions.com> wrote in message
news:1102090158.M7NxTkhoLBZiWtyUmBVrBw@teranews...[color=blue]
> That doesn't make any difference. As I tried to explain, but didn't
> very well, the procedure itself works, it's when I try to call it like
> so - "Call Dataextract" in a module or form that nothing happens.[/color]

A Private Sub can only be called by code in the same module. If you want
to call it from *other* modules then it needs to be declared as Public Sub.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Call Function

Lucy Randles wrote:
[color=blue]
> Maybe if I put some of the code in you would be able to understand?
>
> My module basically does this, the call function being at the bottom...
>
> Option Compare Database
> Option Explicit
>
> Private Sub Dataextract()
> Dim strsql As String
> Dim todaydate As String
> Dim month As String
> Dim monthupper As String
> Dim year As Integer
>
> todaydate = Date
> month = VBA.Format(DateAdd("m", -12, todaydate), "mmm")
> monthupper = StrConv([month], 1)
> year = VBA.Format(DateAdd("m", -12, todaydate), "yyyy")
>
> STRSQL STUFF - DOES COMPILE
>
> CurrentDb.QueryDefs("A02 Insert All Sales").SQL = strsql
>
> Exit Sub
>
>
> End Sub
>
>
> Function callDataextract()
> Call Dataextract
> End Function
>
>
>
> Obviously I've taken out the sql script. Anyway, that runs, and runs
> fine, but the call function doesn't return anything. It obviously does
> 'something' as I don't get an error, but it doesn't run the module, as I
> want it to do.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

A Function returns something, a sub does not. Let's look at a function
that sums two numbers.

Dim intSum As Integer
intSum = SumIt(1,2)
msgbox intA
it will display 3

Function SumIt(int1, int2) As Integer
SumIt = int1 + int2
End Function

You can see that I declare the type of variable that will be returned.
You also see that SumIt is assigned the value of adding the two
integers. If I were to comment out the line
SumIt = int1 + int2
then the result would be zero.

Now, a subroutine does not return a value.
Dim intSum As Integer
SumIt 1,2

Sub SumIt(int1, int2)
msgbox int1 + int2
End Function

You can see I called SUmIt but nothing is returned to the calling
program. You could test this out in a module if you'd like to see the
difference.