Problem obtaining list of Access fields

[Guest]

Hello,
I am trying to adapt the code found under Access Help and “CreateField
Method Example†to my situation. I have created the table and have added
fields. Now I want to see a list of the fields. I am using the following
code from the example, but have changed some lines to comments. When running
this code I receive an error that says, “Run-time error 13, type mismatchâ€
The error occurs on the "For Each fldLoop In tdfNew.Fields" line. What am I
missing here?
Selections of the code are as follows:

Dim dbsNorthwind As DAO.Database
Dim tdfNew As TableDef
Dim prpLoop As Property
Dim fldLoop As Field
Set dbsNorthwind = CurrentDb
' Enumerate Fields collection to show the properties of
' the new Field objects.

For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name

'For Each prpLoop In fldLoop.Properties
' ' Properties that are invalid in the context of
' ' TableDefs will trigger an error if an attempt
' ' is made to read their values.
' On Error Resume Next
' Debug.Print " " & prpLoop.Name & " - " & _
' IIf(prpLoop = "", "[empty]", prpLoop)
' On Error GoTo 0
'Next prpLoop

Next fldLoop

Thank you,
Keith

 

[Guest]

Hi Keith

The problem is that tdNew has not been set to anything. Add the following
line after setting dbsNorthWind and it works OK.

Set tdfNew = dbsNorthwind.TableDefs("Table1")

 

[Guest]

Hi Nick,
Thanks very much.
Keith

Hi Keith

The problem is that tdNew has not been set to anything. Add the following
line after setting dbsNorthWind and it works OK.

Set tdfNew = dbsNorthwind.TableDefs("Table1")

--
Nick

Hello,
I am trying to adapt the code found under Access Help and “CreateField
Method Example†to my situation. I have created the table and have added
fields. Now I want to see a list of the fields. I am using the following
code from the example, but have changed some lines to comments. When running
this code I receive an error that says, “Run-time error 13, type mismatchâ€
The error occurs on the "For Each fldLoop In tdfNew.Fields" line. What am I
missing here?
Selections of the code are as follows:

Dim dbsNorthwind As DAO.Database
Dim tdfNew As TableDef
Dim prpLoop As Property
Dim fldLoop As Field
Set dbsNorthwind = CurrentDb
' Enumerate Fields collection to show the properties of
' the new Field objects.

For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name

'For Each prpLoop In fldLoop.Properties
' ' Properties that are invalid in the context of
' ' TableDefs will trigger an error if an attempt
' ' is made to read their values.
' On Error Resume Next
' Debug.Print " " & prpLoop.Name & " - " & _
' IIf(prpLoop = "", "[empty]", prpLoop)
' On Error GoTo 0
'Next prpLoop

Next fldLoop

Thank you,
Keith

 

[Douglas J Steele]

In addition to what Nick suggested, if you're using Access 2000 or newer and
you haven't removed the reference to ADO, change

Dim prpLoop As Property
Dim fldLoop As Field

to

Dim prpLoop As DAO.Property
Dim fldLoop As DAO.Field

The ADO model has Property and Field objects in it, and since the reference
to ADO is higher in the list than that to DAO (unless you moved them), the
two lines you have will result in ADO properties, which will lead to the
type mismatch you're getting.