How do you get the value stored in a field object while processing a table. ( i have the field name) 1

[Pack10]

I am using a field object to get the fieldname in a For Loop processing a large table.
I need to get the value stored in that field.
I can't get the syntax for that.

I thought .Fields(intFldCtr).Name.Value would give me the value stored in that field....but no.


For intFldCtr = 1 To .Fields.Count
strCurrentField = .Fields(intFldCtr).Name
If strCurrentField = strFieldName Then

 

[jrbarnett]

Use

.Fields(intFldCtr).Value

John

 

[Pack10]

I am getting <item not found in this collection> error message.
Is there a special library i need. I have ticked Visual Basic for Applications

 

[kjv1611]

You may have to use a recordset. If you know the ID of the record, and just need the one field, then you could dynamically build a query for it, and set the recordset to that.

something like;

Dim db as dao.database
Dim rs as dao.recordset
Dim strSQL as string

Set db = CurrentDb

For intFldCtr = 1 To .Fields.Count
strCurrentField = .Fields(intFldCtr).Name
If strCurrentField = strFieldName Then

strSQL = "SELECT [" & strFieldName & "] FROM TableName WHERE ID = ..."

Set rs = db.openrecordset(strSQL)

'get the value from rs.fields(0) or rs.fields(1), I forget which comes first.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

Field numbering is zero-based.

Duane
Hook'D on Access
MS Access MVP