Solved DAO.field doesn't give the field name

[freuzo]

Hello,

I have this code

'code....
    Dim tdf As DAO.TableDef
    Dim fld
    Set fld = New DAO.field
 
    sql = "select * from t_import_agence"
    Set rst = CurrentDb.OpenRecordset(sql)

    Set tdf = CurrentDb.TableDefs("t_import_agence")

    If Not rst.EOF Then       
        strlines = "UPDATE agence SET "       
        For Each fld In tdf.Fields
            rst.MoveFirst           
            strlines = strlines & fld.name           
            Do While Not rst.EOF               
                strlines = strlines & " = CASE WHEN " & rst!agence_id & " THEN " & Nz(fld.Value, "NULL")
                rst.MoveNext
            Loop           
            strlines = strlines & " END ,"     
        Next fld
    End If
'code...

but I get an error : 3420 Object invalid on the line : For Each fld In tdf.Fields.

I don't understand. Also in DAO.field the 'f' is always in lower case. I think the issue comes from there. I even imported all the projectg to another file but same issue.
What is hapening there ?

 

[MajP]

When working with fields of table defs you need to save a pointer to the current db by creating a variable for the database.

dim db as dao.database
dim tdf as dao.tabledef
dim fld as dao.field
dim rst as dao.recordset

set db = currentDb
set tdf = db.tabledefs("t_import_agence")

 

[Minty]

Firstly I would delete this line

Set fld = New DAO.field

Secondly, it looks like this is creating a SQL String to pass through to a SQL server, when a simple Update query could probably do the same thing.

Take a step back and explain the task at hand.

 

[Gasman]

Why haven't you defined fld as DAO.Field and instead left it as variant?

 

[MajP]

Good article here explaining the need for a persistent datbase variable and why it differs from recordsets.

CurrentDB and the "Object invalid or no longer set" error

Misuse of the CurrentDb function is a common cause of the "Object invalid or no longer set" error. Are you using it safely?

nolongerset.com

 

[freuzo]

Firstly I would delete this line

Set fld = New DAO.field

Secondly, it looks like this is creating a SQL String to pass through to a SQL server, when a simple Update query could probably do the same thing.

Take a step back and explain the task at hand.

I'm trying to build a bulk update sql.
This is just the start of all the string I'll build but before go forward I can't get the field name.

 

[freuzo]

Why haven't you defined fld as DAO.Field and instead left it as variant?

I did it at first, got the error and tried that way. Still the same error

 

[freuzo]

Good article here explaining the need for a persistent datbase variable and why it differs from recordsets.

CurrentDB and the "Object invalid or no longer set" error

Misuse of the CurrentDb function is a common cause of the "Object invalid or no longer set" error. Are you using it safely?

nolongerset.com

Thanks for the link.
Hope it helps me with the issue.

 

[freuzo]

When working with fields of table defs you need to save a pointer to the current db by creating a variable for the database.

dim db as dao.database
dim tdf as dao.tabledef
dim fld as dao.field
dim rst as dao.recordset

set db = currentDb
set tdf = db.tabledefs("t_import_agence")

I did this and It gave me the field's name but faild to give the value. Error 3219 Invalid operation on the line where I want the value (e.g Debug.print fld.Value)

 

[freuzo]

Got the value by changing

For Each fld In tdf.Fields

to

For Each fld In rst.Fields

Thanks @MajP for pointing me in the right direction.