VBA Record Manipulation, can you use a string to access a field inside a recordset

[bell]

Hi all,



I would like some help with this problem.

I have a lot of fields in a table that have the same beginning text name followed by a number, for example some fields may be "Field1" "Field2" ... "Field25" ... etc.

I am pulling in a lot of data from another database, so to make life easier I wanted to use a Do Loop Until so that I wont need to repeat the same coding steps for each field number, "Field1" "Field2" ... "Field25" ... etc.

Here's something similar to what I tried

Dim count As Number
Dim i As Number

Dim str As String
Dim countStr As String

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("Record Set Table", dbOpenDynaset)

i = 0
count = 1

Do

countStr = count
str = "Field" + countStr

If IsNull(rs![str]) Then
'Dont copy the text to the new database
'End the Do Loop Until
i = 1
Else
'Copy the text to the new database
'[Code for copying]
'Increment count to get the next field
count = count + 1
End If

Loop Until i = 1

My problem is rs![str] doesn't work. Is there another way where I can use strings or something similar to access the fields in the tables?

[ranman256]

if you are doing "Field1" "Field2" ... "Field25" , then your db is set up wrong.
however, to access a field as a variable

dim sFld as string
dim i as integer
for i = 1 to 25
  sFld = "Field" & i
  msgbox rst.fields(sFld).value
next i

[June7]

Why VBA looping - why not import wizard or TransferDatabase method? If you have a link set to the other database table, why not an UPDATE and/or INSERT query object?

What other fields are in table? Do you need to copy those as well? Are the field names the same in both tables?

[bell]

I have to redesign an old database at my job which is setup with fields like Seq1 Seq2 etc. I know that's an awful way to design a database, so I designed a new one much differently. Its was a little hard to just relate both databases together so I thought writing my own code would be easier.

That being said, ranman256 solution worked for me. Thanks

[June7]

So this is a one-time event? There are non-VBA methods using queries to get the data normalized. A UNION query could rearrange the Seq_ fields into normalized structure and that query could be the source for new table.

However, glad you got a solution.