Trying to Resolve a Subscript Out of Range: Run-Time Error 9

[abares]

I am working with an "inherited" access database to produce reporting for one of my firm's annual research projects. I have used this database and the particular module represented here without issue for 7 years, but this time it is giving me a Subscript Out of Range: Run-Time Error 9. When I debug, it leads me to the code noted below, with the highlighted text noted below.

Select Case X

Case Is = 1 'case where both NumInJob and BaseAvg were reported
IncCount = IncCount + SortedBaseSet("NumInJob") 'add to incumbent count
BaseIncCount = BaseIncCount + SortedBaseSet("NumInJob") 'add to incumbent count
WtdFactor = WtdFactor + (SortedBaseSet("BaseAvg") * SortedBaseSet("NumInJob"))
AvgIncFactor = AvgIncFactor + 1 'One person added to track simple average
AvgFactor = AvgFactor + SortedBaseSet("BaseAvg")
N = N + 1


DataTank(N) = SortedBaseSet("BaseAvg") ' read into array

Putting my curser over the N following DataTank gives me the number 10001

Earlier in the module, the DataTank is referenced in the following code:

Dim DataTank(1 To 10000) As Double, C As Integer, IncCount As Integer, BaseIncCount As Integer, WtdFactor As Double

This leads me to think there is a duplicate record in the DataTank, but I'm not sure how to find the "DataTank" among the tables and queries that the module draws on. It doesn't seem to be a common term in Access use, so I'm guessing it might just be a term the original programmer liked to use.

I am a non-technical, but have been using Access for a number of years. Can anyone suggest an approach to help me find the data (assume its in a table somewhere - there are many in this database) that is being referenced in this "Subscript Out of Range" error?

Many thanks for your help.

[Welshgasman]

Firstly that is an array?
You have some how exceeded the dimensions of that array, hence the error message.
So you need to find out why the original programmer decided 10000 was enough and now you are up to more than 10000 ?
You could just increase the Dim line to 12000 ?, but that will probably fall over in the future when you exceed 12000?

So you really have to discover why the increase?


Insert

Debug.Print DataTank(N)

before N = N + 1
and then look at the output in the immediate window (Ctrl + G)

If the 'duplicates' are not near each other, it could be tricky to see where the issue lies.

[June7]

DataTank is an array variable. Somewhere code may be opening a recordset object to feed that array. Possibly SortedBaseSet() is a recordset object. Find code that is building SortedBaseSet and will probably find reference to table data is pulled from.

If you want to provide db for analysis, follow instructions at bottom of my post.

[abares]

Welshgasman

Thanks for quick response.
Over the 7 yrs of using this and other databases, I've discovered that the original programmer created and coded in a number of limitations (size, content, etc.) that seemed completely arbitrary, made no sense given the purpose and use of the database. In looking at a couple of other databases created by the same individual, I've noticed that the coding for this module used figures (rather than the 10000) that ranged from 200 (I can't figure out why that one hasn't encountered a similar error) and 100000.

I did insert the debug code you recommended and it gave me the number 46, which doesn't correspond to anything I can think of.

Betting that the 10000 was an arbitrary figure, I tried increasing the Dim line to 12000 and rerunning the module, but I received the same error and this time when I put my cursor over the N following DataTank it gave me the number 12001 (as though it moved the goalpost when I changed the number). I will look at the link you provided for debugging Access (I have looked at a number - but not this one, so appreciate that).

June7
Thanks for your quick response. I did find the code that is building SortedBaseSet and it doesn't reference a table or query in this database. I appreciate your offer to look at the db - because it contains confidential information I am unable to share it, unfortunately.

[June7]

Confidential information is why my guidance includes recommendation to make a copy and remove such data.

[abares]

I appreciate that and your clarification June7. There are many levels and a number of fields across many tables that include confidential data, and I'm not sure how we could remove or replace that information to a level that would not compromise our agreements without substantial work.

[Micron]

I'm not sure how we could remove or replace that information to a level that would not compromise our agreements without substantial work.

Maybe this, or the other approach that is in here somewhere.

https://www.accessforums.net/showthread.php?t=77482

[June7]

How many tables? And how many have confidential info? What do you consider confidential? Addresses, phone, email? In copy of db, copy tables but don't copy data, delete tables with data, rename new tables.

Is database split design?

[Welshgasman]

You only need to upload enough to see the issue. However if N was 46, then it is not the error I am thinking of?
So walk your code and show us the EXACT line on which the error occurs. Could it occur in SortedBaseSet?

Add Debug.Print UBound(datatank) before that line in red and report back what that shows.

[abares]

I've attempted to walk through the code as best I know how, but if I'm looking for an error beyond the line I originally noted in red font, I don't think I have sufficient understanding of Access to find it.

I appreciate the time and attention from everyone here, but thinking now that I don't have the expertise to act appropriately on your suggestions. A business colleague has recommended a consultant with Access programming experience who can join me on a call in a few hours. At this point, I think that's a better use of everyone's time.

I just realized that I've been sent the Cross Posting link twice now. I was clear before starting this thread that cross posting was bad form and I'm not aware that I've posted this thread anywhere else, but if I did unknowingly then I apologize for that.

Thanks again for trying to help me.

[Micron]

I just realized that I've been sent the Cross Posting link twice now

That's just part of a signature. Causes this confusion sometimes so I personally would not use it. To each their own.

[June7]

The cross-posting link is in Welshgasman's signature. It shows for all of his posts. Just as the guidance in my signature shows for all posts.

Not specific to you.

[Welshgasman]

I just realized that I've been sent the Cross Posting link twice now. I was clear before starting this thread that cross posting was bad form and I'm not aware that I've posted this thread anywhere else, but if I did unknowingly then I apologize for that.

So you have been sent Debugging Access link as well twice?
As mentioned, they are links in my signature in an attempt to help newbies.

[abares]

Yes - clicked on the debugging articles right away the first time, and good stuff, thank you. I spent a lot of the weekend reviewing the articles and videos. Especially the Pharos video(s) which I discovered on my own before finding it in your link. Just hadn't opened the other until today.

[Welshgasman]

Do let us know the outcome.