for next loop doesn't work for tabledef fields

Try using a recordset, not a tabledef.

Try this instead:

    Dim fldField As Field
    Dim rs As DAO.Recordset
    MsgBox CurrentDb.TableDefs(strSourceTable).Fields(0).Name 'this works
    Set rs = CurrentDb.OpenRecordset(strSourceTable)
    For Each fldField In rs.Fields 'this works!
        MsgBox fldField.Name
        
    Next

Select allOpen in new window

yup yup okie got it! thanks mbizup! = )

but from a learning stand point, how come my code failed for tabledef? the help file clearly said that fields also existed for tabledef

Yup... not sure if that is a bug or by design, but I have seen numerous questions about the same issue without any clear answers as to why tabledefs behave that way.

I think it might have to do with something about the currentdb, reference to it and the dbengine(0)(0) thingy but I'm going to stay very, very, very far away from that haha = )) thanks mbizup!! = ))

his code works for me.  I concur that I think it has to do with the CurrentDb reference.  I've encountered this before.

Public Sub TestFields(SourceTable As String)

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    Set tdf = db.TableDefs(SourceTable)
    For Each fld In tdf.Fields
        Debug.Print fld.Name
    Next
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
End Sub

Select allOpen in new window

--> it has to do with the CurrentDb reference.

Agreed -- but it is specifically with TableDefs, and there are a variety of workarounds, none of which are needed when working with recordsets.

Haha sorry guys, but what does OP stand for?

developingprogrammer:

-->>  what does OP stand for?
"Original Poster"  (that's you :-) )


Dale:

-->> It may also have had to do with the fact that the OP declared fldField...

Explicitly using DAO.Field doesnt make any difference.   It definitely seems to be CurrentDB used directly with a tableDef instead of using a Database Object.

Sorry mbizup, do you think you could give me the same code for using 2 for each field loops to loop through the previousversion recordset and write to the currentversion recordset? I can't really get my head around this. One for each loop yup no problem. But how do I do for 2 nested for each loops? I would think compare the field name. If it's the same then write. But quite inefficient my way. Better ways from you? = )

<<  do you think you could give me the same code for using 2 for each field loops to loop through the previousversion recordset and write to the currentversion recordset?>>

Not sure I follow.  Why do you want to use two recordsets?  Did you try the code I suggested in your previous question, using one recordset based on two tables with identical field names?

ah i see mbizup, ok i just re-read your code and now i understand how to implement it. whao didn't think of it again, i thought i needed 2 for each loops, but the inner join select query solved all the problems. fantastic once again!! = ))

omg mbizup, i just implemented your solution and holy @!#$!@%!@$#@% it is really so darn darn amazing...... omg...

ok sorry, stabilising myself again haha.

but yes! it's really so elegant and such a master stroke!! whao really so so much i have got to learn from you = ) really beautiful code. thanks for always sharing and helping me out mbizup!! (and the rest of the wonderful EE experts as well!! = ))   )

whao Christian!! i've read some really, really, really, really, really long and crazy articles about currentdb vs dbengine BUT you summarised it so succinctly and cogently it's amazing to read!! = ))

phew i thought i'd never understand this currentdb thingy cause it's just so convoluted but now i get a much better understanding of it already = ) i think dbengine is a pointer to the 1st database if we use (0)(0) or something like that and you also need to use refresh and something like that ha, but too much for me to put in my little head now. stay focused and get the important bits done i need to! = ))

fyed, thanks for your great solution code!! = ))

glad to help a developingprogrammer!

Haha thanks fyed! I've improved a lot since all your help on all my previous question, and I'm definitely going to pay it forward! Thanks fyed!! = ))

That is what we do here, learn and pass it on.