"Subscipt out of range" error on array of form control data

[Digital Oatmeal]

I am working in 2007 trying to create a collection for consolidating a lot of the code in my module.

Here a sample of the code from my forms:

Option Compare Database
Private fieldsArray() As String
Private dataArray() As Variant
Private recOrdno As Variant
Private USCollection As New Collection

Private Sub CreateUSDictionary()
CreateFieldsArray
CreateDataArray
For i = 0 To UBound(fieldsArray)
    If IsNull(dataArray(i)) = False Then
        USDictionary.Add fieldsArray(i), dataArray(i)
    End If
Next i
End Sub

Private Sub CreateFieldsArray()
Dim strVar As String
strVar = "Vendname,Description,DroppedTrailer,Location,Carrier,Tankno,StartGauge,StopGauge," _
        & "MicroMotionMeter,MicroMotion,TruckNumber,TrailerNumber,WeightIn1,TimeIn1,WeightOut1," _
        & "TimeOut1,TruckNumber2,WeightIn2,TimeIn2,WeightOut2,TimeOut2,SealNumbers,Comments"
fieldsArray = Split(strVar, ",")
End Sub

Private Sub CreateDataArray()
Dim dataVar As Variant
For i = 0 To UBound(fieldsArray)
    dataVar = Forms![UnscheduledReceipt](fieldsArray(i)).Value
    If IsNull(dataVar) = True Then
        dataVar = "foo"
    Else
        dataArray(i) = dataVar
    End If
Next i
End Sub

Now mind you I am a hack programmer subject to doing totally retarded things so please be nice. I think I can pass this collection as an argument and explode it into all kinds of SQL statements. I just have to change the array of form elements for each form so it would really cut down on the coding. I name all of my controls exactly as their table names so the fieldsArray is multipurpose. I'm basically trying to create key value coding in VBA for each of my forms.

***The code fails at

dataArray(i) = dataVar

on the forth line from the bottom of the code. I get a "Subscript out of range" error but I cannot figure out why. There is data in dataVar by the way.

Thanks ahead of time for any help.

 

[spikepl]

Look up ReDim in the docs

 

[Digital Oatmeal]

Thank you so much. So I added:

ReDim Preserve dataArray(UBound(fieldsArray) - 1)

right before the loop in the CreateDataArray sub and I can get past that.

Now I'm getting "Object Required" at:

USDictionary.Add fieldsArray(i), dataArray(i)

I'll admit that I have never worked with a collection and I honestly don't know how to implement it. Objects in Access confuse me. In an OOP language I'm fine because I know that everyting is ultimately an object, but Access seems inconsistent on which data types are objects.

 

[spikepl]

Access doesn't tolerate much sloppiness, just like any other language. It wants to see an object but doesn't, and says so - check your declarations.

Update: If you intend to create a dictionary, then use the built-in dictionary: http://ezinearticles.com/?The-Dictionary-Object---Please-Consider&id=136536

 

[Digital Oatmeal]

Re: "Subscript out of range" error on array of form control data

Thanks for the update. So I changed my declaration to:

Private USDictionary As Object

and inserted

Set USDictionary = CreateObject("Scripting.Dictionary")

before the loop in the CreateUSDictionay sub

It now loops all the way through the CreateUSDictionary loop but gives me the "Subscript out of range" error after "end if" of the last iteration of the loop. Since I'm initializing the dictionary right before the loop I have no clue as to where I should "ReDim" it.

 

[Digital Oatmeal]

Re: "Subscript out of range" error on array of form control data

Is there a reference library I need to implement to use the dictionary object?

 

[spikepl]

No. There is a concept of "early binding" which requires reference, but gives you Intellisense, and "late binding", where Access consumes whatever obejcts at runtime. Google these two concepts.

Your previous question: The dictionary object is not to be dimmed/redimmed, so the error probably stems from something else, or perhaps you are attempting to access an item by using an index out of range. When asking for help each time provide the specific context and code.