Expression too complex.... VERY WEIRD (1 Viewer)

[doulostheou]

I am trying to use a few global arrays so that I do not duplicate a lot of work (if values have already been looked up, it just pulls it from the array instead of recalculating the values).

Everything works except for a very odd error the second time it passes through the array. The line that is breaking though makes no sense and the error stating the expression is too complex makes less sense. To really confuse me, if I step through the code after debug, everything works just fine. No more errors.

Here is the code with the line breaking commented:

Public Function compAssistant(lngCourse As Long, lngComp As Long, intStudents As Integer, curTotal As Currency) As Currency
    Dim i As Integer
    Dim blnFound As Boolean
    
    If sysArrayInitialized(compArray) Then
        For i = 0 To UBound(compArray, 2)
            If compArray(0, i) = lngCourse Then
                blnFound = True
                Exit For
            End If
        Next i
    End If
    
    If Not blnFound Then
        Call determinePay(lngCourse, lngComp, intStudents, curTotal)
        For i = 0 To UBound(compArray, 2)
            If compArray(0, i) = lngCourse Then
                Exit For
            End If
        Next i
    End If
    
    compAssistant = compArray(3, i)
End Function

Public Function determinePay(lngCourse As Long, lngComp As Long, intStudents As Integer, curTotal As Currency) As Boolean
    Dim i As Integer, j As Integer
    Dim curInst As Currency, curAssist As Currency, curSchool As Currency, curTempTotal As Currency

    If sysArrayInitialized(compArray) Then
        i = UBound(compArray, 2) + 1
    End If

    Select Case lngComp
        Case 1
            If Not blnInitialized Then Call popPayArray
            ReDim Preserve compArray(3, i)
            
            For j = 0 To UBound(payArray, 2)
                If payArray(0, j) <= intStudents Then
                    compArray(0, i) = lngCourse
                    curInst = payArray(1, j)
                    curAssist = payArray(2, j)
                    curSchool = payArray(3, j)
                    curTempTotal = curInst + curAssist + curSchool
'Code breaks on next line: Error 16.  Expression too complex
                    curInst = CCur(CDbl(curInst) / CDbl(curTempTotal))
                    curAssist = CDbl(curAssist) / CDbl(curTempTotal)
                    curSchool = CDbl(curSchool) / CDbl(curTempTotal)
                    curInst = curInst * curTotal
                    curAssist = curAssist * curTotal
                    curSchool = curSchool * curTotal
                    compArray(1, i) = curInst
                    compArray(2, i) = curAssist
                    compArray(3, i) = curSchool
                    Exit For
                End If
            Next j
        Case 2
            ReDim Preserve compArray(3, i)
            curInst = intStudents * 15
            curTempTotal = curTotal - curInst
            curAssist = 0.7 * curTempTotal
            curSchool = 0.3 * curTempTotal
            compArray(1, i) = curInst
            compArray(2, i) = curAssist
            compArray(3, i) = curSchool
        Case 3
            ReDim Preserve compArray(3, i)
            curInst = curTotal
            curAssist = 0
            curSchool = 0
            compArray(1, i) = curInst
            compArray(2, i) = curAssist
            compArray(3, i) = curSchool
    End Select
End Function

The first function calls the second. It only breaks in case 1. Ignore some of the craziness I have there doing my math functions. It was just to try to see if I could workaround the problem (all to no avail).

I am at a loss. In one forum it said that I may need to decompile the database. But I could not get the instructions they gave to decompile to work. I'm running Access 2003 on Vista. Any suggestions would be greatly appreciated!

 

[georgedwilkinson]

You certainly have us at a disadvantage in that you can inspect the values and we cannot. You can set a break on the line before and mouse-over the variables to see what their values are just before it breaks. This should tell you a lot.

 

[doulostheou]

I have simplified the line of code that fails since my other attempts were failing anyway. So now the line of code is simply:

curInst = curInst / curTempTotal

Here are values on first run (no error):
curInst=35
curTempTotal=75

Here are values on second run (when error occurs):
curInst=35
curTempTotal=75

Again, once it throws the error, simply hitting F5 causes everything to work without an error. So the line of code actually works correctly.

I did figure out how to decompile, but the problem still persists. So the problem must not be with some deleted code hanging out in my database and causing weird errors. For anyone interested, type the following from the command prompt and then open your database:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile

It doesn't say it did anything, but apparently the first database you open will be decompiled. I also read that this method is unsupported and undocumented and there is a possibility that it could corrupt your database. So I guess use with caution.

 

[georgedwilkinson]

With what little attention span I have today, I'm guessing you are using a global array. Is that correct?

Did you know that sometimes VBA doesn't necessarily do things in the order you think it should do them in. Especially when the code you are running creates Windows events.

I'm thinking you should simplify this quite a bit. Perhaps use a local array and do the work in a single procedure, at least until you have it working.

I hardly ever use arrays in Access because tables are so much handier and not as subject to multi-threading/locking issues.

 

[doulostheou]

That's exactly what I'm doing (using global arrays). I am doing this as these functions are to be used by a query, meaning the functions will be ran many times. I thought it would be better to handle with global arrays and then my main function can simply see if the calculation already exists for the desired course. If it does, it will simply retrieve the value. If not, it will calculate and store.

I'm not tied to the global arrays, but I'm not sure what a good alternative is as I really don't like the idea of recalculating everything so many times.

I experimented more with the cases out of curiosity. Case 3 works without any problems. Case 2 errors at curAssist = 0.7 * curTempTotal. Again, this is only an issue on the 2nd run and simply hitting F5 allows everything to run with no errors.

 

[doulostheou]

Okay. I got this working, but the fix is so idiotic that I would still appreciate any additional insight into what is going on. Maybe it is an error with the way access handles global arrays, but any documentation shedding light on that issue would be welcome.

Here was the fix. At the beginning of the function:

On Error GoTo ErrHandle

At the end of the function:

ErrHandle:
    If Err.Number = 16 Then Resume