#Error using function as controlsource

[Laurel]

I have written a function to format a value in a form's recordsource. But
when I run the form, I get #Error where the return value should show up.
Here are the hypotheses I have tested. The text of the function is at the
bottom of this message.

The control source looks like this =fncCustomPercent([Total])

1 - I'm just mistyping the name of the function.
I cut and pasted =fncCustomPercent from the column's control source
to a text control. Instead of the column name, I put in a value. Thus
=fncCustomPercent(99.9996). The function works fine in the text control -
returns a formatted string.

2 - I don't understand how to write a usable function.
I have another function as a controlsource in this same form. It, too
returns a string. In the debugger it is executed for every row in the
recordset, and works fine. As far as I can see, I reference it in exactly
the same way I reference the new function.

3 - There's something weird about the particular column I'm trying to apply
the function to.
I applied it to other numeric columns in the recordsource, and I have
the same problem.


Any ideas of where else I can look?


The function I'm trying to use:

Public Function fncCustomPercent(av_value As Variant)
Dim ls_value, ls_temp As String
Const IGNORE As Double = 0.000000001

fncCustomPercent = Null
If IsNull(av_value) Then Exit Function
If Not IsNumeric(av_value) Then Exit Function

If Abs(av_value - 1) <= IGNORE Then
'Treat av_value as exactly equal to 1
fncCustomPercent = "100%"
Else
'Format with up to X decimal places
fncCustomPercent = Format(av_value, "000.0%")
End If

Exit_CustomPercent:

Exit Function

Err_CustomPercent:
ls_temp = "fncCustomPercent" & _
vbCrLf & Err.Description
MsgBox ls_temp
fncCustomPercent = "XXX"
Resume Exit_CustomPercent
End Function

 

[Ken Snell [MVP]]

Is the function in a regular module (see Modules in Database window), and is
that module named a different name than the function?

Is the control formatted as something that won't accept a Null value?

 

[Ken Snell [MVP]]

Additional to my other posting, are you wanting to return a string value
from this function? If yes, instead of setting the function to Null at the
beginning of the function, set it to an empty string.

 

[Van T. Dinh]

Assuming that the function is in a Standard Module (not Class Module), my
guess is that there is a problem with the reference [Total].

1. Does the Form have a Control "[Total]" and the Form's RecordSource have
a Field "[Total]" also? If so, try renaming one of them.

2. If "[Total]" is the reference to a Form's Control, try the full
reference Forms!YourForm!Total.

 

[Laurel]

I found the problem. I had been changing the CONTROL attribute of a column
instead of an unbound text field. That is, I had been dragging "score_date"
from the field list and then modifying its control source. I found this by
using the Ucora addin to search my whole application for "=fnc" I saw the
differences that way.

Thanks all for your time.

Ucora Find and Replace Log: Sep 24 2004 03:05:23 pm
Database: C:\LAS\School\DynamicMinds.mdb


FORM: frmStudentDailyScores
CONTROL: Score_Date
PROPERTY: ControlSource = =fncWeekDay([Score_Date])


FORM: frmClassSummary
CONTROL: Text80
PROPERTY: ControlSource = =fncPreviousLevels([Student_ID])

Is the function in a regular module (see Modules in Database window), and is
that module named a different name than the function?

Is the control formatted as something that won't accept a Null value?

--

Ken Snell
<MS ACCESS MVP>

I have written a function to format a value in a form's recordsource. But
when I run the form, I get #Error where the return value should show up.
Here are the hypotheses I have tested. The text of the function is at the
bottom of this message.

The control source looks like this =fncCustomPercent([Total])

1 - I'm just mistyping the name of the function.
I cut and pasted =fncCustomPercent from the column's control source
to a text control. Instead of the column name, I put in a value. Thus
=fncCustomPercent(99.9996). The function works fine in the text control -
returns a formatted string.

2 - I don't understand how to write a usable function.
I have another function as a controlsource in this same form. It, too
returns a string. In the debugger it is executed for every row in the
recordset, and works fine. As far as I can see, I reference it in exactly
the same way I reference the new function.

3 - There's something weird about the particular column I'm trying to apply
the function to.
I applied it to other numeric columns in the recordsource, and I have
the same problem.


Any ideas of where else I can look?


The function I'm trying to use:

Public Function fncCustomPercent(av_value As Variant)
Dim ls_value, ls_temp As String
Const IGNORE As Double = 0.000000001

fncCustomPercent = Null
If IsNull(av_value) Then Exit Function
If Not IsNumeric(av_value) Then Exit Function

If Abs(av_value - 1) <= IGNORE Then
'Treat av_value as exactly equal to 1
fncCustomPercent = "100%"
Else
'Format with up to X decimal places
fncCustomPercent = Format(av_value, "000.0%")
End If

Exit_CustomPercent:

Exit Function

Err_CustomPercent:
ls_temp = "fncCustomPercent" & _
vbCrLf & Err.Description
MsgBox ls_temp
fncCustomPercent = "XXX"
Resume Exit_CustomPercent
End Function