Calling a function from a control

[Michael]

Hi,

I have a form with a text box, and I've set the control source to the
result of a function I have written. One of the parameters of the
function is a variant which should contain an array, to get this
variant I call another function i.e. the control source of the text
box is in the following format:

=MyFunction(Param1, Param2, FunctionToGetParam3Variant)

The problem is when previewing the form, the control just displays
#Error. I'm sure its not a problem with either function - they both
have all the necessary error handling and when I call them from other
places within the script they work fine. Also when I replace the
function call for the variant parameter (FunctionToGetParam3Variant)
with a test value i.e. NULL, the main function (MyFunction) gets
called fine. As it is at the moment when I put break points in the
code in the main function, it doesn't get called at all. It looks like
the problem lies with returning a variant to the form and then using
that to pass to MyFunction. Does anyone have any futhers ideas why
this error is occuring or anyone know how I can find out where it is
occuring?

I hope my description makes sense!

Thanks

Michael

 

[Marshall Barton]

I have a form with a text box, and I've set the control source to the
result of a function I have written. One of the parameters of the
function is a variant which should contain an array, to get this
variant I call another function i.e. the control source of the text
box is in the following format:

=MyFunction(Param1, Param2, FunctionToGetParam3Variant)

The problem is when previewing the form, the control just displays
#Error. I'm sure its not a problem with either function - they both
have all the necessary error handling and when I call them from other
places within the script they work fine. Also when I replace the
function call for the variant parameter (FunctionToGetParam3Variant)
with a test value i.e. NULL, the main function (MyFunction) gets
called fine. As it is at the moment when I put break points in the
code in the main function, it doesn't get called at all. It looks like
the problem lies with returning a variant to the form and then using
that to pass to MyFunction. Does anyone have any futhers ideas why
this error is occuring or anyone know how I can find out where it is
occuring?

WHen using a function without an argument anyplace except
VBA, you must include the parenthesis:

=MyFunction(Param1, Param2, FunctionToGetParam3Variant())

If that's not the issue, then look at the function
FunctionToGetParam3Variant to see if it's generating the
error.

 

[Michael]

WHen using a function without an argument anyplace except
VBA, you must include the parenthesis:

=MyFunction(Param1, Param2, FunctionToGetParam3Variant())

If that's not the issue, then look at the function
FunctionToGetParam3Variant to see if it's generating the
error.

Sorry that whas a typing mistake, in the code I have included the
paranthesis. The FunctionToGetParam3Variant doesn't generate any
errors, when I step through it, it works perfectly and exits ok,
returning a variant containing an array of dates. However MyFunction
never gets called and the text box controls just displays #Error. It's
really annoying that I can't find out why its displaying error. Is
there no way of finding where this error has been generated?

 

[Marshall Barton]

Sorry that whas a typing mistake, in the code I have included the
paranthesis. The FunctionToGetParam3Variant doesn't generate any
errors, when I step through it, it works perfectly and exits ok,
returning a variant containing an array of dates. However MyFunction
never gets called and the text box controls just displays #Error. It's
really annoying that I can't find out why its displaying error. Is
there no way of finding where this error has been generated?

I am unaware of any reason why that shouldn't work. Are you
sure that MyFunction expects a variant array? I guess I'll
have to see both function declarations and their pertinate
lines of code.

 

[Michael]

Thanks for the reply. I'll post the actual code i've written, just the
relevant bits.
The actual control source of the text box is


=DaysOverdue([U##WONO],[PRIOCODE],GetOverdueDate(),[SYSDATE_],GetArea(),GetHolidays())



The GetHolidays() function is what courses the problem, without this
parameter everything works fine. The code for this function is as
follows


Public Function GetHolidays() As Variant
Dim CurrDB As Database
Dim rs As Recordset
Dim iCount As Integer
Dim adHolidays() As Date

iCount = -1
Set CurrDB = CurrentDb
Set rs = CurrentDb.OpenRecordset("Holidays")
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
iCount = iCount + 1
rs.MoveNext
Loop
ReDim adHolidays(iCount)
rs.MoveFirst
iCount = 0
Do While Not rs.EOF
adHolidays(iCount) = rs("Holiday")
iCount = iCount + 1
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set CurrDB = Nothing
GetHolidays = adHolidays
End Function


Finally the definatition for DaysOverdue is as follows


Public Function DaysOverdue(strWorkOrder As String, strPriority As
String, dReportDate As Date, dSystemDate As Date, strArea As String,
Holidays As Variant) As String


'Code


End Function


I haven't bothered posting all the the cost for this, because this
function never even gets called so that can't be the problem. The
GetHolidays function definately gets called, I've stepped though it
and the array gets populated correctly, but DaysOverdue is never
called! I really can't understand it! Any suggestions would be greatly
appreciated!

Thanks,

Michael

 

[Marshall Barton]

I ran some example code that used your logic to fill in a
array, pass it back to the calling function in its argument,
and then call the other function - no problems. At this
point I just don't see where the trouble is, maybe someone
else can jump in with something more insightful.

To try to add some minor value to this thread, I can suggest
a more efficient way to code the GetHolidays function.
Here's the code I used to test your logic:

DaysOverdue(GetHolidays())

Public Function GetHolidays() As Variant
Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT Holiday FROM Holidays"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then
GetHolidays = rs.GetRows(100000)
Else
GetHolidays = Null
End If
rs.Close: Set rs = Nothing
End Function

Public Function DaysOverdue(Holidays As Variant) As String
Dim k As Long
For k = 0 To UBound(Holidays, 2)
Debug.Print Holidays(0, k)
Next k
DaysOverdue = UBound(Holidays, 2) + 1
End Function