Argument not optional Error

[BlahBlahBlah]

Hi.

I'm getting "Argument not optional" on the highlighted line:

Private Function getTableFieldNames(tableName As String) As Collection
    Dim fieldNames As Collection
    Dim Rst As Recordset
    Dim f As field
    Dim fieldIdx As Integer
    
    Set fieldNames = New Collection
    Set Rst = CurrentDb.OpenRecordset(tableName)

    fieldIdx = 0
    For Each f In Rst.Fields
        ' Skip first field (ID).
        If fieldIdx <> 0 Then
            fieldNames.Add ("[" & f.Name & "]")
        End If
        fieldIdx = fieldIdx + 1
    Next
    Rst.Close
    
    [COLOR=Red][B]getTableFieldNames = fieldNames[/B][/COLOR]
End Function

I have no other functions named "getTableFieldNames". If I change that line to:

[COLOR=Red][B]Set getTableFieldNames = fieldNames[/B][/COLOR]

the error goes away (is using Set here is correct?) but further along in the execution of the code, anything that uses the return value of that function has an error:

Run-time error '450':

Wrong number of arguments or invalid property assignment

Not really sure what to do...

Thanks.

 

[Galaxiom]

The tablename is an argument of the function and must be included in the call.

Set is used for objects so is required because the return of the function is a Collection. The fieldnames must be addressed as items in that collection.

BTW The function is rather clumsy because opening a recordset is unnecessary. The fields collection can be read directly from the tabledef.

 

[BlahBlahBlah]

The tablename is an argument of the function and must be included in the call.

Set is used for objects so is required because the return of the function is a Collection.

Ok, so if I use Set, how do I stop the error I mentioned from happening?

The fieldnames must be addressed as items in that collection.

I'm not sure what you mean by this.

BTW The function is rather clumsy because opening a recordset is unnecessary. The fields collection can be read directly from the tabledef.

Thanks, I'll use a tabledef instead. Could I do something similiar to this (forums.devx.com/showthread.php?t=77974) except return a collection instead of having a "by reference" input parameter?

 

[BlahBlahBlah]

Even if I pass a collection in instead of having it returned, I get the same error (Wrong number of arguments etc.):

' Taken from: forums.devx.com/showthread.php?t=77974
Public Function getTableFields(strTableName As String, retFields As Collection)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.field
    Dim fields As Collection
    On Error GoTo Proc_Exit
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs(strTableName)

    For Each fld In tdf.fields
        fields(fld.OrdinalPosition - 1) = fld.Name
    Next fld
    Set retFields = fields
    
Proc_Exit:
    On Error Resume Next
    Set fld = Nothing
    Set tdf = Nothing
    dbs.Close
    Set dbs = Nothing
    Set fields = Nothing
    Exit Function
    
Proc_Err:
    MsgBox "Error occurred in GetFields"
    Resume Proc_Exit
End Function

    Dim fieldNames As Collection
    Set fieldNames = New Collection
    Call getTableFields("rules", fieldNames)
    
    MsgBox fieldNames.Count
    MsgBox fieldNames ' Run-time error '450'

 

[stopher]

The error is because you are trying to treat a collection as a string. You are asking MsgBox to display the collection but Msgbox can only display one string at a time whereas as collection is many strings.

So you'd have to loop through the collection and concatenate them into one string then use msgbox.

But what are you really trying to do? What's the bigger picture here?

Chris

 

[gemma-the-husky]

instead of trying to pass the collection, just make the collection a public variable.

I never use collections so I am not sure whether you can actually return a colection from a function.

 

[vbaInet]

If you want to take a copy of the collection you would have to loop through the fieldnames collection and assign it to the function's collection (i.e. using For Each). The fieldnames collection goes out of scope after End Function so the reference becomes broken.

 

[Galaxiom]

The fieldnames collection goes out of scope after End Function so the reference becomes broken.

The fieldNames Collection inside the function goes out of scope when the function ends. However the function argument (ByRef by default) passed as the Collection retFields, is Set to it in the function so its items persist in that collection.

 

[vbaInet]

I keep forgetting, yes you're right it does persist because it still maintains a reference to the object. I now remember using this same strategy when writing a sort function for the Dictionary object.

 

[stopher]

Why bother going to the trouble? tdf.fields is a perfectly good collection. Why bother creating a custom to replicate a collection that is already publicly available?

I can see that the OP doesn't want the first item. But maybe they just need a function to return the fields as a string? Hence my question.

Chris

 

[stopher]

I never use collections so I am not sure whether you can actually return a colection from a function.

Yes you can and I'd favour this method. I don't see the point of passing an empty collection as an argument.

Chris

 

[ChrisO]

I think stopher makes a valid point but if it is required for some reason:-

Sub TestIt()
    Dim colFieldNames As New Collection
    Dim lngKey        As Long
    
    GetTableFields "rules", colFieldNames
    
    For lngKey = 1 To colFieldNames.Count
        MsgBox lngKey - 1 & "  " & colFieldNames.Item(lngKey)
    Next lngKey
    
End Sub



Public Sub GetTableFields(ByVal strTableName As String, _
                          ByRef colFields As Collection)
                          
    Dim dbs As Object
    Dim fld As Object
    
    Set dbs = CurrentDb()

    For Each fld In dbs.TableDefs(strTableName).fields
        colFields.Add Item:=fld.Name
    Next fld
    
End Sub

Chris.

 

[gemma-the-husky]

Yes you can and I'd favour this method. I don't see the point of passing an empty collection as an argument.

Chris

I didn't mean pass an empty collection as an argument. I meant just declare it so the whole module had visiblity, and not pass it at all.

I take your point though, that populating another collection is just repeating a data structure that is already available.

 

[Galaxiom]

I use collections for objects but would just use an array for a list of field names.

 

[BlahBlahBlah]

All I want is to not have to hard-code the field names - I only wanted to return a collection because I wasn't aware of the TableDefs approach. However, this code doesn't work:

Dim rulesRst As Recordset
Dim prvwRulesRst As Recordset
Dim f As Object
Dim isFirst As Boolean
isFirst = True
    
Set rulesRst = CurrentDb.OpenRecordset("rules")
Set prvwRulesRst = CurrentDb.OpenRecordset("tempPreviewRules")

' Set each field to be added to the rules table.
[COLOR=Red][B]For Each f In CurrentDb.TableDefs("rules").fields[/B][/COLOR]
    If isFirst = True Then
        isFirst = False
    Else
        [COLOR=Black]rulesRst(f.Name) = prvwRulesRst(f.Name)[/COLOR]
        MsgBox ("Setting" & f.Name & " to " & prvwRulesRst(f.Name))
    End If
Next

Object invalid or no longer set

 

[stopher]

Ok, so now you want to set the fields names of one table to the same field names of another table (expect the first field). Is that right?

So here's some code to do that:

Dim dbs As Database
Dim tdfRules As TableDef
Dim tdfTempPreviewRules As TableDef

Set dbs = CurrentDb
Set tdfRules = dbs.TableDefs("rules")
Set tdfTempPreviewRules = dbs.TableDefs("tempPreviewRules")

For i = 1 To tdfRules.fields.Count - 1
    tdfRules.fields(i).Name = tdfTempPreviewRules.fields(i).Name
Next i

Set tdfRules = Nothing
Set tdfTempPreviewRules = Nothing
Set dbs = Nothing

But why do you want to do this? It seems an odd thing to do. What's your overall aim here?

Chris

 

[BlahBlahBlah]

Huh? Nope. This is what I'm trying to do:

Private Sub importRulesFn()
    Dim rulesRst As Recordset
    Dim prvwRulesRst As Recordset
    Dim relationshipRst As Recordset
    Dim f As Object
    Dim isFirst As Boolean
    isFirst = True
    
    Set rulesRst = CurrentDb.OpenRecordset("rules")
    Set prvwRulesRst = CurrentDb.OpenRecordset("tempPreviewRules")
    Set relationshipRst = CurrentDb.OpenRecordset("relationship")
    
    ' Want to get the last (AutoNumber) ID after inserting each new record.
    rulesRst.MoveLast
    
    If prvwRulesRst.RecordCount > 0 Then
        prvwRulesRst.MoveFirst
        Do While Not prvwRulesRst.EOF And Not prvwRulesRst.BOF
            rulesRst.AddNew
            ' Set each field to be added to the rules table.
            For Each f In CurrentDb.TableDefs("rules").fields
                If isFirst = True Then
                    isFirst = False
                Else
                    rulesRst(f.Name) = prvwRulesRst(f.Name)
                    MsgBox ("Setting" & f.Name & " to " & prvwRulesRst(f.Name))
                End If
            Next
            
            ' Insert one record into the rules table.
            rulesRst.Update
            ' Move to the next record in the preview table.
            prvwRulesRst.MoveNext
            
            ' Add an overview/rules ID pair to the relationship table.
            relationshipRst.AddNew
            relationshipRst("ID") = importRulesOverviewCombo
            rulesRst.Move 0, rulesRst.LastModified
            relationshipRst("rulesID") = CLng(rulesRst!ID)
            relationshipRst.Update
        Loop
    End If
    
    rulesRst.Close
    prvwRulesRst.Close
    relationshipRst.Close
End Sub

I have a temporary table that lets the user edit data that they've imported from a spreadsheet before they append it to the rules table. I want to set the value for each field in the new rules record that's about to be added, without having to specify each field name explicitly.

 

[gemma-the-husky]

i would define f as field, rather than object


dim f as field

and instantiate currentdb in a slightly different way


dim dbs as dao.database
set db = currentdb

replace currentdb with dbs, and you will find yuor code doesn't get the "out of scope" error

 

[Galaxiom]

replace currentdb with dbs, and you will find yuor code doesn't get the "out of scope" error

This is certainly the problem. It confuses many new developers but there is a sensible reason.

CurrentDb.TableDefs("somename") doesn't mean anything because CurrentDb is not an Object but a Method of the Application Object.

Application.CurrentDb returns the object defined by:

DbEngine.Workspaces(0).Databases(0)

So firstly Set an object variable to Application.CurrentDb as Dave and Chris have shown. Then you can address the Collections (such as Fields), Properties and Methods of that object.

(Application is the default object so can be omitted.)

 

[BlahBlahBlah]

Thanks guys, works perfectly:

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 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

I thought I'd make a similar function that returned the list of fields as a comma-separated string, for functions where a Recordset doesn't need to be opened and Execute can be used instead:

Private Function getTableFieldNamesStr(tableName As String) As String
    Dim fieldIdx As Integer
    Dim f As Object
    Dim fieldNamesStr As String
    Dim fieldNames As Collection
    Set fieldNames = getTableFieldNames(tableName)

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

But I get this error on the bolded line:

424 Object required