#Error using function as controlsource

  • Thread starter Thread starter Laurel
  • Start date Start date
L

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
 
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?
 
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.
 
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.
 
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])





Ken Snell said:
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>


Laurel said:
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
Click to expand...
Click to expand...
 
Back
Top