Argument not optional Error

[BlahBlahBlah]

Huh. I changed f to be a Variant and it worked fine:

Private Function getTableFieldNamesStr(tableName As String) As String
    Dim fieldIdx As Integer
    Dim f As [COLOR=Red][B]Variant[/B][/COLOR]
    Dim fieldNamesStr As String
    Dim fieldNames As Collection
    Set fieldNames = getTableFieldNames(tableName)

    fieldIdx = 1
    For Each f In fieldNames
        fieldNamesStr = fieldNamesStr & f
        ' Don't add comma to last field.
        If fieldIdx < fieldNames.Count Then
            fieldNamesStr = fieldNamesStr & ", "
        End If
        fieldIdx = fieldIdx + 1
    Next
    
    getTableFieldNamesStr = fieldNamesStr
End Function

Can anyone please tell me why this is?

 

[vbaInet]

A Collection, when used in a For Each Loop, needs to be Variant. The same applies when you're performing For Each on an array.

 

[BlahBlahBlah]

A Collection, when used in a For Each Loop, needs to be Variant. The same applies when you're performing For Each on an array.

Er, you mean the enumerator, right? If you do mean the enumerator/control variable/whatever, why does this work, when f is a field object?

Private Function getTableFieldNames(tableName As String) As Collection
    Dim fieldNames As Collection
    Dim rst As Recordset
    Dim f As field
    Dim fieldIdx As Integer
    Dim dbs As DAO.Database
    
    Set dbs = CurrentDb
    Set fieldNames = New Collection
    Set getTableFieldNames = New Collection
    Set rst = dbs.OpenRecordset(tableName)
    fieldIdx = 0
    
    ' Set each field to be added to the rules table.
    For Each f In dbs.TableDefs(tableName).fields
        ' Skip first field (ID).
        If fieldIdx <> 0 Then
            fieldNames.Add ("[" & f.Name & "]")
            ' Add the last created field name to the collection that will be returned.
            getTableFieldNames.Add (fieldNames(fieldIdx))
        End If
        fieldIdx = fieldIdx + 1
    Next
End Function

 

[vbaInet]

I explicitly mentioned Collection (and Dictionary) and Arrays. A Collection is a type of an Array (i.e. an Associative Array) and the items you are returning are not Objects. They are primitive data types. A Variant can take any form.

 

[BlahBlahBlah]

I explicitly mentioned Collection (and Dictionary) and Arrays.

You said:

A Collection [, when used in a For Each Loop,] needs to be Variant.

Which makes no sense to me. I'm trying to understand this better, so I assumed that you meant, in my case, the variable "f" needs to be a variant when "For Each"ing over a collection.

A Collection is a type of an Array (i.e. an Associative Array) and the items you are returning are not Objects. They are primitive data types. A Variant can take any form.

Ok, cheers.

 

[vbaInet]

Ok. Basically, if you're trying to get an item from a collection of some sort (i.e. note I'm not talking about the Collection object, I'm talking about any kind of collection, for e.g. a Forms collection), you need to match it with the type that it's returning. Your Fields collection is an Object so you ensure that the single instance is an Object type.

If your Collection (now this time I'm talking about the Collection object) was returning an Object then yes fld As Object would have worked, but in your case it's returning a String.

A Variant can morph into an Object, a String, a Number, an Array or whatever you want it to be. It's very submissive

 

[BlahBlahBlah]

Ok. Basically, if you're trying to get an item from a collection of some sort (i.e. note I'm not talking about the Collection object, I'm talking about any kind of collection, for e.g. a Forms collection), you need to match it with the type that it's returning. Your Fields collection is an Object so you ensure that the single instance is an Object type.

If your Collection (now this time I'm talking about the Collection object) was returning an Object then yes fld As Object would have worked, but in your case it's returning a String.

A Variant can morph into an Object, a String, a Number, an Array or whatever you want it to be. It's very submissive

Sweet, I think I get that haha.