Accessing fields via variable

Hi

In a table I have fields like field1, field2, field3 and so on. Is there any
way for me to access these fields via variable like;

I =1 to 10
"field"&str(I) = "some value"
next

Thanks

Regards

See:

http://www.mvps.org/access/forms/frm0003.htm



and add a bookmark to:


http://www.mvps.org/access


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"John" wrote:

Hi

In a table I have fields like field1, field2, field3 and so on. Is there any
way for me to access these fields via variable like;

I =1 to 10
"field"&str(I) = "some value"
next

Thanks

Regards

John,

Yes, it would be like this:

For i = 1 to 10
Me("field" & i) = "some value"
Next i

Your need to do so, however, probably indicates that your table design
could be improved. Let us know if you are interested in exploring that
aspect.

--
Steve Schapel, Microsoft Access MVP

John wrote:
Hi

In a table I have fields like field1, field2, field3 and so on. Is there any
way for me to access these fields via variable like;

I =1 to 10
"field"&str(I) = "some value"
next

On Sat, 22 Mar 2008 02:30:17 -0000, "John" wrote:

Hi

In a table I have fields like field1, field2, field3 and so on. Is there any
way for me to access these fields via variable like;

I =1 to 10
"field"&str(I) = "some value"
next

You can do so, using a Recordset in VBA code:

Dim i As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
' You can replace "tablename" by a string containing a valid SQL query
returning records from the table
' use some appropriate code to find the record that you want to edit, e.g.
FindFirst
rs.Edit
For i 1 to 10
rs.Fields("Field" & i) = "some value"
Next i
rs.Update ' commit the changes to disk


The need to do this makes me REALLY REALLY suspicious of your data structure,
however! I suspect that this table could and should be normalized into a
one-to-many relationship between two tables, or more.
--

John W. Vinson [MVP]

Oops, Hit the enter key trying to paste


So your code would look like:


Dim i As Integer

For i = 1 To 10
Me("field" & i) = i*10 ' or some value
Next i



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Steve Sanford" wrote:

See:

http://www.mvps.org/access/forms/frm0003.htm



and add a bookmark to:


http://www.mvps.org/access


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"John" wrote:

Hi

In a table I have fields like field1, field2, field3 and so on. Is there any
way for me to access these fields via variable like;

I =1 to 10
"field"&str(I) = "some value"
next

Thanks

Regards

Hi John,

Yes, you can do that - you didn't mention a form so I will assume you want to work directly with the table data? Insert your table name in the place of the const myTable, your field count in the place of CountOfCol. Note that, as an alternative, fields can be addressed by their ordinal (position) value, this count starts at 0, so the first field/column is 0, the second is 1, etc.

So - instead of using the field1, field2, etc. field labels (often assigned as a default during import when no header row is provided) you might want to use the ordinal positions? You stated that you wish to access the values, it appears in your code that you are attempting to assign 'some value' rather than view the existing contents? I will show you via code examples, a way to read them both ways, using the field name/label & the numeric ordinal index.

To write new data into the field, you would

'go into edit mode
rs.Edit
'set the field = "some value"
rs.Fields("Field" & CStr(x)) = "some value"
'run update
rs.Update

If this was to be a new record...

'go into addnew mode
rs.AddNew
'set the field = "some value"
rs.Fields("Field" & CStr(x)) = "some value"
'run update
rs.Update

NOTE: You were using the Str function. The problem with the Str function is that it left pads your integer with a space. Instead of returning "1" for 1, it returns " 1" and that will mess up your label assignment. I prefer using CStr for this type of conversion - it returns "1" for 1.

NOTE: I use the Nz (Null to Zero) function to condition my list of values because if I hit a Null value, Nz will return an empty string. If I don't do that, the Null will break the CStr function and the code will stop running on that error.

NOTE: If you want to read every row, one at a time, remove the comment marks from the Do, rs.MoveNext, and Loop lines.
In that case, you could address rs.Fields(x) instead of going after the labels (field1, etc) as the fields collection index.

NOTE: I am appending the contents to a string named s, then displaying the results in a msgbox. If you were going to use this information for further processing purposes, you might prefer to assign each value to an Array and then work with that Array. In this situation, you would likely be best served by dimensioning the array type as variant so it can hold whatever data types the fields contain.

Dim myArray(CountOfCol)

This is particularly useful when, for instance, you want to populate unbound fields of a form with the information.

Instead of
s =s & "Field" & CStr(x) & ": " & CStr(Nz(rs.Fields("Field" & CStr(x)).Value)) & vbCrLf

you could:
myArray(x) = "Field" & CStr(x) & ": " & CStr(Nz(rs.Fields("Field" & CStr(x)).Value)) & vbCrLf


Code is below,

Hope this helps,
Gordon
================================================== =====

'### Reading with Labels

Sub TestingLabelRead()
Const myTable As String = "NameOfMyTable"
Const CountOfCol As Integer = 10

Dim x As Integer
Dim s As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(myTable)

'make sure we have some records here
If rs.RecordCount 0 Then
'Do While Not rs.EOF
For x = 1 To CountOfCol
s = s & "Field" & CStr(x) & ": " & CStr(Nz(rs.Fields("Field" & CStr(x)).Value)) & vbCrLf
Next x
MsgBox s, vbInformation, "Here Is Your Row Data"
'rs.MoveNext
'Loop
Else
MsgBox "No Row Data Found", vbCritical, "NO RECORDS"
End If
Set rs = Nothing
End Sub



'### Reading with ordinal indexes

Sub TestingIndexRead()
Const myTable As String = "NameOfMyTable"
Const CountOfCol As Integer = 10

Dim x As Integer
Dim s As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(myTable)

'make sure we have some records here
If rs.RecordCount 0 Then
'Do While Not rs.EOF
For x = 0 To (CountOfCol - 1)
s = s & rs.Fields(x).Name & ": " & CStr(Nz(rs.Fields(x).Value)) & vbCrLf
Next x
MsgBox s, vbInformation, "Here Is Your Row Data"
'rs.MoveNext
'Loop
Else
MsgBox "No Row Data Found", vbCritical, "NO RECORDS"
End If
Set rs = Nothing
End Sub




"John" wrote in message ...
Hi

In a table I have fields like field1, field2, field3 and so on. Is there any
way for me to access these fields via variable like;

I =1 to 10
"field"&str(I) = "some value"
next

Thanks

Regards