I have a table that holds the field names of a secondary table. I create a recordset to iterate the fieldnames and alter the table to set the field to be Text(40). This synatx works perfect for the 1st iteration, but the Next is hit, and the code continues on as if the table only holds one field.
Why does this syntax not fully iterate all fields contained in the table?
Code:
Public Function AlterFieldType()
Dim fld As DAO.Field
Dim StrSQL As String,secondSQL As String
Set db = CurrentDb()
StrSQL = "select fieldname from needstobetext"
Set rs3 = db.OpenRecordset(StrSQL)
For Each fld In rs3.Fields
secondSQL = "ALTER TABLE prod ALTER COLUMN [" & fld.Value & "] TEXT(40);"
DoCmd.RunSQL secondSQL
Next
Set fld = Nothing
rs3.Close
End Function