Field Declaration > Type Mismatch 1

[jon51]

I found I had to use Tool/Refs to add DAO 3.6 but for some reason all the field def statements I try end in a runtime error 13 - type mismatch. This includeds the For Each FLD line below and the alternate Set FLD at the bottom

Option Explicit
Sub TEST()
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Set db = OpenDatabase("C:\Library\myTest.mdb&quot
Debug.Print db.Name
For Each tbl In db.TableDefs ' this works
Next
Set tbl = db.TableDefs!TEST 'ok
'fields is its own collection
Debug.Print db!TEST.Fields.Count ' ok, there are three fields
' ref page 248 should work -
For Each fld In db.TableDefs!TEST.Fields ' runn err
Debug.Print fld.Name
Next
'Alternate test
' test below this line - runtime mismatch type error
Set fld = db!TEST.CreateField("NewField1", dbInteger)
db!CATALOG.Fields.Append fld
End Sub

 

[kathryn]

Have you tried

for each fld in tbl.fields

instead of

For Each fld In db.TableDefs!TEST.Fields

You have set a reference to the individual tabledef in the line "Set tbl = db.TableDefs!TEST", so it SHOULD work. Kathryn

 

[jon51]

I found the missing link in another Tips, and its supported by the code writer automation. DAO requires a declaration prefix, else the type mismatch

Dim tbl as DAO.TableDef
Dim fld as DAO.Field
Dim rs as DAO.Recordset