Subscript not in Range MS Access

1
votes

I'm needing to get fields names from a query and put them into a dynamic array. I believe I have found the proper code this, however I get a "Subscript not in Range" error.

Code so far:

Dim qdf As QueryDef
Dim fld As Field
Dim o As Integer
Dim fieldCount as Integer
fieldCount = CurrentDb.QueryDefs("qryctAverage").Fields.Count

Set qdf = db.QueryDefs("qryctAverage")
Dim n As Integer
n = fieldCount
ReDim colHeaders(0 To n - 1)

For o = 0 To n - 1
    colHeader(o) = qdf.Fields(o).Name
Next o

Line of error: colHeader(o) = qdf.Fields(o).Name

I'm fairly new to VBA so I appreciate the patience and time put in to helping out! Thanks in advance

Note: All answer I've found have applied to non-dynamic arrays.

EDIT:

I now get "Sub or Function not defined" after removing Dim colHeader() as String

Error Line: colHeader(o) = qdf.Fields(o).Name

2
vbams-access
Why are you getting qdf off db, but fieldCount off CurrentDb? Are they the same? I would be expecting to see fieldCount = qdf.Fields.Count under the Set qdf assignment. Also n is redundant, loop would be clearer with For o = 0 To fieldCount - 1 - Mathieu Guindon
@MathieuGuindon db is defined previously in my code. This is just a snippet with fieldCount moved in so there wouldn't be confusion as to where I got fieldCount from - Jaskier
You're using colHeader within the loop, but colHeaders elsewhere to cause that specific error..... but definitely take into account the other comments. No.... you define colHeader() but then ReDim colHeaders.... - Darren Bartrup-Cook
Exactly what is the VBE highlighting when you get that "sub or function not defined" error? Make sure you've also replaced colHeader with colHeaders everywhere else in that procedure.... - Mathieu Guindon
You seem to have left out the most important part of my answer... change it to colHeaders(o) - Mathieu Guindon

2 Answers

3
votes
Dim colHeader() As String
ReDim colHeaders(0 To n - 1)

You've declared colHeader, but resized colHeaders. Option Explicit can't pick that up, because the ReDim statement is perfectly valid as a declaration, too.

But then, colHeader isn't dimensioned, so index o is necessarily out of bounds:

colHeader(o) = qdf.Fields(o).Name

Change it to 

colHeaders(o) = qfd.Fields(o).Name

I'd remove the Dim colHeader() As String declaration, and add Option Explicit at the top of the module if it's missing.

2
votes

Another way would be to use a For Each block.
This code builds a single string of field names separated by an |.
Split is then used to turn the field string into an array of field names which is passed back to the calling procedure.

Option Explicit
Sub Test()

    Dim colHeaders As Variant
    colHeaders = FieldNames("qryctAverage")

    Debug.Assert False 'Pause code so you can look at colHeaders.

End Sub

Public Function FieldNames(QueryName As String) As Variant

    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field
    Dim fldNames As String

    Set qdf = CurrentDb.QueryDefs(QueryName)

    For Each fld In qdf.Fields
        fldNames = fldNames & fld.Name & "|"
    Next fld
    fldNames = Left(fldNames, Len(fldNames) - 1)

    FieldNames = Split(fldNames, "|")

End Function