Type Mismatch when Trying to Fill Array from Field Names

[djblois]

I am trying to query a table for its field names and then put those names in an array to then fill a combobox with. I have gotten the query to work but I keep getting an error when trying to populate the array.



Here is the code I created:

Public Function fnGetColumnNames(table_name As String) As Variant

  Dim rst As New ADODB.Recordset: rst.Open "select * from  " + table_name + " where 1=0", CurrentProject.Connection
  Dim aColumnNames As Variant
  
  Dim i As Integer: For i = 0 To rst.Fields.Count - 1
    aColumnNames(i) = rst.Fields(i).name
  Next i
  fnGetColumnNames = aColumnNames


End Function

I keep getting a 'Type Mismatch' error on this line of code:

aColumnNames(i) = rst.Fields(i).name

It is a variant and it should be accepting string values. What am I doing wrong?

[June7]

Probably need ReDim statement.

Bit why have intermediate step of filling array?

Just build string from the recordset to use as combobox RowSource.

Could use DAO TableDefs instead of recordset https://www.devhut.net/ms-access-vba...69e53a4fd34a7c

[Welshgasman]

Wouldn't that be Dim ColumnNames() AS Variant?

https://docs.microsoft.com/en-us/off...claring-arrays

However I agree, just use the Field List as the source.

[moke123]

why not just set the combobox's row source type to Field List and set the rowsource to the table name.

[djblois]

Is there a way to sort the list then?

[June7]

Arrange fields in table design. Combobox will pull fields in order listed in table design.

[djblois]

That is what I thought; the issue is there are over 200 fields. Thank you though.

[June7]

Sounds like 'spreadsheetitis' - non-normalized structure.

Rearranging could take an hour or so but once and done. Or use VBA provided.

[moke123]

Is there a way to sort the list then?

I believe you can also use a query instead of a table.

[June7]

How would a query provide a list of field names?

[Welshgasman]

Well I was thinking they must be held in a system table somewhere, but could not find them?

[June7]

It they are, and they must be somewhere, would have to be in a deep hidden table that cannot be exposed with the "Show system tables" option. Review https://www.access-programmers.co.uk...20c0c1bfddcd59

[Welshgasman]

I have asked isladogs, as he has extensive knowledge of the deep stuff. After all, the property sheet must get them from somewhere?

[moke123]

How would a query provide a list of field names?

SELECT tblStates.StateID, tblStates.StateLong, tblStates.StateShort
FROM tblStates

heres an example using queries, a table, a procedure and a dictionary.

The dictionary can be sorted Asc or Desc.

[Micron]

Isn't the syntax supposed to be
Dim aColumnNames() As Variant
aColumnNames(0) =
aColumnNames(1) =

or

Dim aColumnNames As Variant
aColumnNames = Array(value1, value2,...)

(or any similar construct using looping if need be).

OP has
Dim aColumnNames As Variant, which isn't an array, it's just a variable of type Variant. So
aColumnNames(i) would not apply?