Error 9 Subscript Out Of Range (1 Viewer)

[Birdy]

I have converted a 97 database to 2000 and am now getting the error Subscript out of Range. The 97 database worked fine with the calculation but 2000 has a problem. How can I sort it out.

 

[Alexandre]

Any idea where the error happens?

 

[Birdy]

The query will run but will cause the error whilst trying to calculate the problem field. It even comes up when trying to view the results.
The sum I am using is :- RSP_PB01_UnitPrice: CalcUnitPrice([RSP_PB01],[ActualSize],[SectionCode],False,True)

As I mentioned, this works OK but not in the converted database.

 

[Alexandre]

So the error is generated by your CalcUnitPrice function. One possibility is that the type of one of the variables you defined was actually compatible with the values it could take under 97, but not anymore under A2k. Never really heard about such thing pb...
Could we see your code?

 

[The_Doc_Man]

Part of my confusion here is that the thing you mentioned doesn't appear to be subscripted. Therefore, a "Subscript out of range" error doesn't make sense.

But the key is that it is converted from AC97 to AC2K.

This MIGHT BE (not saying IS) a references problem.

In any module (including a NEW one if necessary), from a page for VBA code, follow menu bar path

Tools >> References

You should get a complex dialog box that lists library names and a check box to the left of each one.

See if ANY checked box has the word MISSING (all caps) between the box and the name of the checked item.

If so, you have a references problem. If not, we have to look elsewhere. But the reason I bring this up is that Access is known to do some crazy things with reference problems, including not pointing to the right place and not necessarily making sense with the error message it tries to give you.

 

[RV]

>Access is known to do some crazy things with reference problems, including not pointing to the right place and not necessarily making sense with the error message it tries to give you<

which is considered to be standard functionality in ALL Microsoft products

RV

 

[Birdy]

This is the code it uses.

Public Function CalcUnitPrice(pdblPrice As Double, pstrSize As String, plngSectionCode As Long, pintErrorMessage As Integer, pintEquivalentTo As Integer) As String

Dim dbProm As Database, recSectionException As Recordset
Dim fdbPromOpen As Integer, frecSectionExceptionOpen As Integer
Dim intUnitConversion As Integer
Dim intUnitPosition As Integer
Dim intMultiplierPosition As Integer
Dim intX As Integer
Dim intY As Integer
Dim strQuantity As String
Dim strQuantityPart1 As String
Dim strQuantityPart2 As String
Dim strRestofSize As String
Dim dblQuantity As Double
Dim dblQuantityMultiplier As Double
Dim dblUnitPrice As Double
Dim dblFraction As Double

On Error GoTo CalcUnitPrice_Err
CalcUnitPrice = Space(0)

If pdblPrice = 0 Then
' If pintErrorMessage Then
' CalcUnitPrice = "Price not specified"
' End If
GoTo CalcUnitPrice_Done
End If

If Len(pstrSize) = 0 Then
If pintErrorMessage Then
CalcUnitPrice = "Actual Size not specified"
End If
GoTo CalcUnitPrice_Done
End If

If plngSectionCode = 0 Then
If pintErrorMessage Then
CalcUnitPrice = "Section not specified"
End If
GoTo CalcUnitPrice_Done
End If

'**** find old unit in actual size

intUnitPosition = 0
intUnitConversion = 0

Do Until intUnitConversion > glngUnitConversionCount Or intUnitPosition > 0
intUnitPosition = InStr(1, pstrSize, gstrOldUnit(intUnitConversion), vbTextCompare)
If intUnitPosition = 0 Then
intUnitConversion = intUnitConversion + 1
End If
Loop

If intUnitPosition = 0 Then
If pintErrorMessage Then
CalcUnitPrice = "Invalid Source Unit"
End If
GoTo CalcUnitPrice_Done
End If

'**** check for double weight

strRestofSize = Mid(pstrSize, intUnitPosition + Len(gstrOldUnit(intUnitConversion)))
intX = 0
intY = 0

Do Until intY > glngUnitConversionCount
intX = InStr(1, strRestofSize, gstrOldUnit(intY), vbTextCompare)
If intX > 0 Then
If pintErrorMessage Then
CalcUnitPrice = "Invalid Source Unit"
End If
GoTo CalcUnitPrice_Done
End If
intY = intY + 1
Loop

'**** extract quantity from actual size

strQuantity = Mid(pstrSize, 1, intUnitPosition - 1)

If Len(strQuantity) = 0 Then
If gintQuantityRequired(intUnitConversion) Then
If pintErrorMessage Then
CalcUnitPrice = "Invalid Source Quantity"
End If
GoTo CalcUnitPrice_Done
End If
strQuantity = "1"
End If

intMultiplierPosition = InStr(1, strQuantity, "x", vbTextCompare)

If intMultiplierPosition > 0 Then
strQuantityPart1 = Trim(Mid(strQuantity, 1, intMultiplierPosition - 1))
strQuantityPart2 = Trim(Mid(strQuantity, intMultiplierPosition + 1, Len(strQuantity) - intMultiplierPosition))
Else
strQuantityPart1 = "1"
strQuantityPart2 = Trim(strQuantity)
End If

If Not IsNumeric(strQuantityPart1) Then
If pintErrorMessage Then
CalcUnitPrice = "Invalid Source Quantity"
End If
GoTo CalcUnitPrice_Done
End If

If Not IsNumeric(strQuantityPart2) Then
If pintErrorMessage Then
CalcUnitPrice = "Invalid Source Quantity"
End If
GoTo CalcUnitPrice_Done
End If

dblQuantity = Val(strQuantityPart1) * Val(strQuantityPart2)

If dblQuantity = 0 Then
If pintErrorMessage Then
CalcUnitPrice = "Invalid Source Quantity"
End If
GoTo CalcUnitPrice_Done
End If

dblQuantity = dblQuantity * gdblMultiplier(intUnitConversion)

dblQuantityMultiplier = 1 / dblQuantity

'**** Apply Section Exceptions

dblUnitPrice = pdblPrice * dblQuantityMultiplier

Set dbProm = DBEngine.Workspaces(0).Databases(0)
fdbPromOpen = True
gdblSectionCode = plngSectionCode
gstrUnit = gstrNewUnit(intUnitConversion)
Set recSectionException = dbProm.OpenRecordset("qrytblSectionException", DB_OPEN_DYNASET)
frecSectionExceptionOpen = True
If Not recSectionException.EOF Then
gstrUnit = recSectionException!NewUnitDescription
dblUnitPrice = dblUnitPrice * recSectionException!Multiplier
Else
gstrUnit = "per " & gstrUnit
End If

'**** build unit price string

If dblUnitPrice >= 100 Then
dblFraction = dblUnitPrice - Int(dblUnitPrice)
dblFraction = dblFraction + 0.001
If dblFraction < 0.5 Then
gstrUnit = Format((dblUnitPrice / 100), "£0.00 ") & gstrUnit
Else
gstrUnit = Format((dblUnitPrice / 100) + 0.0005, "£0.00 ") & gstrUnit
End If
Else
If Val(Format(dblUnitPrice, "#")) = Val(Format(dblUnitPrice, "#.#")) Then
If (dblUnitPrice - Int(dblUnitPrice)) < 0.5 Then
gstrUnit = Format(dblUnitPrice, "#") & "p " & gstrUnit
Else
gstrUnit = Format(dblUnitPrice + 0.05, "#") & "p " & gstrUnit
End If
Else
If (dblUnitPrice - (Int(dblUnitPrice * 10) / 10)) < 0.5 Then
gstrUnit = Format(dblUnitPrice, "#.#") & "p " & gstrUnit
Else
gstrUnit = Format(dblUnitPrice + 0.005, "#.#") & "p " & gstrUnit
End If
End If
End If

If pintEquivalentTo Then
gstrUnit = "Equivalent To " & gstrUnit
End If

If pintErrorMessage Then
CalcUnitPrice = Space(0)
Else
CalcUnitPrice = gstrUnit
End If

CalcUnitPrice_Done:
On Error GoTo 0
If frecSectionExceptionOpen Then
recSectionException.Close
End If

If fdbPromOpen Then
dbProm.Close
End If
Exit Function

CalcUnitPrice_Err:
Select Case Err
'Handle specific errors
Case Else: MsgBox "Error " & Err & " " & Error(Err)
End Select
Resume CalcUnitPrice_Done
End Function

It is not just an Access 2000 problem. It has happened on a Test db that we use for enhancements. Don't know how we got round that problem.

 

[Birdy]

All the references are there and it compiles without a problem.

 

[The_Doc_Man]

That code snippet was hard to read, but I at least scanned it a bit. I didn't see an array declaration.

So the other possibility is that something is somehow spelled wrong and is being treated as an external. But it has a parenthetical expression behind it so it looks like an external array.

This is a wild-eyed guess, but....

Go into your Tools>>Options box, select "Module" tab, and verify that you have checked "Require Variable Declaration." It is in the subsection captioned "Coding Options."

If you have not checked it, check it now and recompile. You might get a different error that points to whatever is confusing VBA.

Like I said, a wild-eyed guess, but to quote Judy Tenuta, she of many cable TV Comedy Channel spots, "It could happen!"

 

[Birdy]

I have looked at this and it is currently checked. Like I have mentioned previously, it has happened in the 97 Test DB we had. Don't know how we sorted it though.

 

[The_Doc_Man]

OK, just noticed this. You DON'T have an array declaration but - in your long code sequence you DO have references to variables beginning with 'gstr' that have parentheses behind them.

They are 'gstrOldUnit' and 'gstrNewUnit' and their syntax appears to be that of an array of strings. Assuming some method to this madness, the 'gstr' prefix can only mean "global string" (unless it means God-Awful string, which I tend to doubt.) Now, they COULD be functions, I guess, because the syntax is ambiguous. Without seeing the definitions of the gstrOldUnit and gstrNewUnit objects, I can't tell from here.

So if I were you, I would drop a breakpoint into this code and open the debug window. I would then examine the attributes of the gstrxxxUnit variables.

In fact, I would do this on both the AC97 and AC2K machines to see if a difference has cropped up in the specifications of how these arrays are defined.

And I'll bet your subscript out of range is related to one of these two externally-defined objects.

By the way, if these objects really ARE globally defined, that pretty much sucks as a way to get data into a code module. Because (as you can see here) it becomes ten times harder to debug. You can get all sorts of side-effects, too!

 

[Birdy]

Unfortunately the database was inherited by me when I started. The variables are Globally defined. Can't understand why Access looks at them in different ways. Who's idea was it to go from DAO to ADO and make it complicated for us all.

 

[Rich]

Some idiot called Microsoft