Field names in an array

[Seamus59]

I have field names in an array fname

I have the following code in a subroutine

[Ranking].Value = assessment() ' where '[Ranking]' is a field in the curent recordset

.....my function assessment attempts to read the value from the field whose name is an element in the array
Function assessment()
For I = 0 To UBound(fname) - 1
assessment = assessment + Me.Recordset(fname(I))
Next
End Function

Me.Recordset(fname(I)) keeps returning null

any ideas or pointers to where this may have already been answered?

[Dal Jeanis]

Not sure what you're trying to achieve.

Is that function intended to be recursive? If so, you need to have a default value returned (for instance 0) after the loop.

If not, then when it exits the loop and reaches the end of the function, it will return Null.

Then, the recursive call will set the value of the prior call to (assessment + Me.Recordset(fname(I))) which is (Null + Me.Recordset(fname(I)) which is Null.

[Seamus59]

I know it's a little confusing, but it's not recursive... I'll give you a little more info.
I am simply trying to retrieve the value in the current record of the field described by fname(i)

fname(0)="DGA", so to return the value of the field DGA in the current record which I manipulate then store in a field called Ranking. Just picking up fom one field and storing into another, but the name of the first field is in an array.

If this helps, I am trying to use this in a form, when someone changes one field, it changes another as well.

Thanks

[June7]

This code is behind a form? The fields are included in the form's RecordSource?

Try:

assessment = assessment + Me!(fname(I))

Why do you need to save calculated value? And this looks like an aggregate calc which is usually a particularly bad idea to save.

[Seamus59]

Der, sorry, I'm not too bright...looking at the wrong end...of course it was returning a null, because that's what was in the field! I didn't check that all had been initialized.

Thanks again!

[Dal Jeanis]

It still won't work because of the uncontrolled recursion. Try something more like this (I assumed Currency type for the function.) -

Function assessment() As Currency
   Dim I As Integer
   Dim tempAssess As Currency

   tempAssess = 0
   For I = 0 To UBound(fname) - 1
      tempAssess = tempAssess + Me!(fname(I))
   Next I 

   assessment = TempAssess

End Function

[June7]

Dal, I tested function like OP's original. It works. Don't see need for additional variable.

However, might want to handle the null because math with null returns null.

assessment = assessment + Nz(Me!(fname(I)),0)

[Seamus59]

That makes sense, thanks, I'll try that and let you know.

[Dal Jeanis]

If it works, then I've been suffering a delusion about the way function calls work. I had understood that assigning a value to the name of the function immediately returns that value to the calling line, ending any processing in the function itself.

What, precisely, does this line do when I = 0?

assessment = assessment + Me.Recordset(fname(I))

Does it ever get to I = 1?

[June7]

I think function result is not passed back to calling source until End Function.

Array element index starts with 0 by default. So fname(0) refers to the first element of the array and grabs its value.

The For Next increments the I variable. The upper limit is established by the UBound(fname) - 1 calc. UBound returns the number of elements and must subtract 1 because element index begins with 0 so 4 elements would index 0 through 3.

However, Seamus, I just noticed that your function does not declare or populate an array. Where does that occur?

[Dal Jeanis]

Wow. If that's true, then I've written a lot of functions over the years that mostly worked by serendipity.

I didn't know you needed an "Exit Function" immediately afterwards to avoid continuing logic- I assumed that was inherent in assigning the return value to the function name. ("Return this", to me, meant "return this NOW".)

I'm sure that most of my code worked fine anyway, but it's disconcerting to think how many chances I had to code a grave error. Lucky I never worked in aerospace.

[June7]

I didn't mention Exit Function, I said End Function. However, Exit Function can be used to force exiting a loop if some condition is met