Updating a recordeset field using a variable 1

[iainmc]

I am trying to fill a variable in my code with the values held in a recordset field.

I do not want to hard code the field name in (eg: variable_name = rs_name!fld_name).
I want to use my variable value populated through a loop to reference the field name. But i cannot make it recognise the field. It is looking for a field with the name of my variable name not the value.

Any ideas how/if i can get round this.

My code below, thanks all.

Sub Clr_Chr10()

Dim Mydb As Database
Dim rs_Table As Recordset
Dim str_sql As String
Dim t As TableDef
Dim f As Field
Dim ChrPosition As Integer
Dim v_chk_str As String

str_sql = "SELECT * FROM myTable"

Set Mydb = CurrentDb
Set rs_Table = Mydb.OpenRecordset(str_sql)

rs_Table.MoveFirst

Do Until rs_Table.EOF

For Each t In Mydb.TableDefs

If t.Name = "myTable" Then
For Each f In t.Fields

rs_Table.Edit
v_chk_str = rs_Table![f.name] <-----This is where i get an error

ChrPosition = InStr(v_chk_str, Chr(10))

MsgBox (f.Name)

Next f
End If

Next t

Loop

rs_Table.Close

End Sub

 

[PHV]

Have you tried this ?
v_chk_str = rs_Table.Fields(f.name)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[iainmc]

Top man!

Thanks that has worked a treat.

Iain Mc