Field Value

[dperry]

I am trying to get the value of fields in a table which I am iterating through.Here is the code I am using
Sub FieldNameAndValue()
Dim dbs As DATABASE, tdf As TableDef
Dim fld As Field
Dim fldname As String, fldvalue As String
Dim FieldCount As Long
Dim i As Integer
' Return Database variable that points to current database.
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!Cust
FieldCount = tdf.Fields.Count
For Each fld In tdf.Fields
fldname = tdf.Fields(i).Name
fldvalue = tdf!fldname.Value
MsgBox "There are " & Str(FieldCount) & " field(s)" & Chr(13) & "The field position is " & Str(i) & Chr(13) _
& "The field name is " & fldname & Chr(13) & "The field value is " & fieldvalue
i = i + 1
Next fld
End Sub
What syntax should I use?

Thanks,

Dennis

 

[RickSpr]

When you use a "For Each" statement, you don't have an iterator variable (i). Instead, the object variable name after For Each gets set to each object in the collection in turn.

The following:

    For i = 0 To tdf.Fields.Count - 1
        fldname = tdf.Fields(i)
    Next i

is equivalent to:

    For Each fld In tdf.Fields
        fldname = fld.Name
    Next fld

With the For statement, you would use the iterator variable to index an item in the Fields collection. With the For Each statement, you don't need to bother--the For Each indexes it for you.

Here's the syntax you want:

    i = 0     ' change to i = 1 to number fields from 1
    For Each fld In tdf.Fields
        fldname = fld.Name
        fldvalue = fld.Value
        MsgBox "There are " & Str(FieldCount) & " field(s)" & Chr(13) _
          & "The field position is " & Str(i) & Chr(13) _
          & "The field name is " & fldname & Chr(13) _
          & "The field value is " & fldvalue
        i = i + 1
    Next fld

 

[dperry]

Thanks RickSpr. Now I understand.

Dennis Perry

 

[dperry]

Ran into another problem when I run the program.


i = 0 ' change to i = 1 to number fields from 1
For Each fld In tdf.Fields
fldname = fld.Name

*Now fldvalue = fld.Value generates a Run-Time err 3219.

MsgBox "There are " & Str(FieldCount) & " field(s)" & Chr(13) _
& "The field position is " & Str(i) & Chr(13) _
& "The field name is " & fldname & Chr(13) _
& "The field value is " & fldvalue
i = i + 1
Next fld

I forgot to mention that I am working in Access 95.

Thanks,

Dennis

 

[richardii]

But when I try to use fldvalue = fld.Value in the way you indicate:

Function e_num()
Dim dbs As Database, tdf As TableDef
Dim fld As Field
Dim fldname As String
Dim fldvalue

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Tbl&quot

For Each fld In tdf.Fields
fldname = fld.Name
fldvalue = fld.Value
MsgBox fld.Name
Next fld

End Function

The line fldvalue = fld.Value causes an error. How can you list the field values in this way (you would have to know the record number to do this. There are multiple values per field.

 

[richardii]

Dennis - didn't see you had the same problem.

 

[richardii]

The following should do it (may need adapting slightly). See "Enumerate recordset" for frustrating mistake!

Function e_num()
Dim dbs As Database, tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim i As Integer
i = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Tbl", dbOpenSnapshot)

With rst
rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF
i = 0
Do While i < rst.Fields.Count
Set fld = rst.Fields(i)
MsgBox fld.Name &amp; &quot; = &quot; &amp; fld.Value
i = i + 1
Loop
rst.MoveNext
Loop
rst.Close
End With
End Function

 

[Dperry15]

Thanks richardii. This worked as I wanted it to.

Regards,

Dennis Perry