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
