FUNCTION BEHAVIOUR?

[easten]

I have made a code using function and not sub in the module. the function will be used for every form i made,
the function is used to calculate the average and generate it when the after update event happen.

the code:
Public Function Average()
On Err Goto Err_Handler

Code**************
******************

Exit_Handler:
Exit Function --> repeat the function

Err_Handler:
MsgBox Err.Description
Resume Next

End Function

my problem is i cannot figure it out why the function is always repeating it self after the exit function ? is there any way to escape the function without using exit function?
and also is there any good tip for making good function in module?
and why I cannot use sub in the module?

 

[Remou]

How are you calling this function?

 

[easten]

i'm sorry if i write the code a little bit confusing in my thread.
but i don't write the code because i thought the code will not have any relation with the function behaviour i get.

the code:

Function Average()
On error Goto Err_Handler

Dim divident as Double
Dim total as Double

divident = 0
total = 0

For intI = 0 To UBound (ArraySubject)

if frm.Controls("txt" & intI).Value = 0 Then
divident = divident + 0
Else
divident = divident + 1
End if
total = total + frm.Controls("txt" & intI).Value

Next intI

if divident <> 0 then
frm.controls("txtAverage").Value = total/divident
end if

exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description
Resume Next

End Function

 

[Remou]

You must be calling the function from somewhere, what line calls the function? For example:

a=Average

You have not made the function equal to anything: a function returns an answer.

Where is frm coming from?

Have you tried stepping through the code?

Finally, a search in the Access fora for basAverage should return a useful function by MichaelRed.

 

[easten]

Thnks for the reply
i the problem is because i call the function from the
text box property in the after update property i type
=durchschnittNoten()
that make infinite loop and only call function

so i must write it manually in the vb code

Private sub txt0_AfterUpdate()
call Average

End Sub

 

[Remou]

Ok. Try this:

Function Average(frm As Form)
On error Goto Err_Handler

Dim divident as Double 
Dim total as Double 

divident = 0 
total = 0

For intI = 0 To UBound (ArraySubject)

 if frm.Controls("txt" & intI).Value = 0 Then
    divident = divident + 0 
 Else 
    divident =  divident + 1 
 End if 
 total = total + frm.Controls("txt" & intI).Value 

Next intI

if divident <> 0 then
   Average = total/divident
Else
   Average = 0
end if 

exit_Handler:
 Exit Function

Err_Handler:
 MsgBox Err.Description
 Resume exit_Handler
End Function

Then, in the textbox:
[tt]=Average(Forms![Name of Form Here])[/tt]

 

[missinglinq]

While we're on the subject, something looked odd about the function as orginally posted and I finally figured out what it was! Do most people include error handling and exit handling in functions? I did a search here and looked at over a dozen functions posted as well as search a number of texts and couldn't find a single instance of this being done! And would the error handler of the sub from which the function was called handle an error arising in the function?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

missingling - error handling is very common (and recommended) in production code. We often leave it out in the sample code on this forum since we are concentrating on a specific problem, but it is assumed you will put in the proper error handling in the production code.

The lowest level sub/function that has an error handler will handle the error.

So for example let's say Sub A calls Sub B, and an error occurs in Sub B. If Sub B has an error handler, that's where it's handled. If Sub B has no handler, but Sub A does, than execution in Sub B immediately ends and Sub A handles the error.

If neither sub has an error handler, execution just ends, the user gets an ugly error message, and if they press the Debug button they end up in the VBA screen. Needless to say, that's not a good thing, and is why we use error handlers.

 

[missinglinq]

Thanks, Joe! I understand the concept/importance of error handling and use it in all subs; just wondered why I've never seen it anywhere in functions, here, in texts, or even in functions offered as whole, ready to use by some of the real gurus in the field!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!