If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error using function as controlsource
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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. -- Ken Snell MS ACCESS MVP "Laurel" wrote in message ... 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 |
#4
|
|||
|
|||
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. -- HTH Van T. Dinh MVP (Access) "Laurel" wrote in message ... 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 |
#5
|
|||
|
|||
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 [MVP]" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Customising the Function Wizard | Frank Kabel | Worksheet Functions | 1 | April 20th, 2004 06:46 PM |
Excel Add-Ins Incomplete Gamma Function | Harlan Grove | Worksheet Functions | 9 | April 15th, 2004 09:30 PM |
Need a DAYSINMONTH function to create a sales pace spreadsheet | MOTOGEEK | Worksheet Functions | 4 | April 12th, 2004 05:52 PM |
Function for % gain? | Norman Harker | Worksheet Functions | 4 | April 1st, 2004 11:54 AM |
Returning multiple values from a worksheet function | Steve Lloyd | Worksheet Functions | 4 | January 14th, 2004 03:50 PM |