Iterate Through Fields in a recordset

[Guest]

Thanks for taking the time to read my question.

I have a string of data that is comma delimited. I want each value to be
placed into seperate fields.

how can I iterate through the fields of a recordset on the same record?

with rst
.addnew

.??????????? 'what can I use here to select the proper field

.update
end with

I am starting with the first field and just moving across until there are no
more fields.


Thanks for your help.

Brad

 

[Brendan Reynolds]

Public Sub Array2Table()

Dim strData As String
Dim astrData() As String
Dim lngloop As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

strData = "one,two,three"
astrData = Split(strData, ",")
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT TextOne, TextTwo, TextThree FROM
TestTable")
rst.AddNew
For lngloop = LBound(astrData) To UBound(astrData)
rst.Fields(lngloop) = astrData(lngloop)
Next lngloop
rst.Update
rst.Close

End Sub

 

[Douglas J. Steele]

Are you saying that the first element in the comma-delimited list should go
to the first field, the second to the second field and so on?

Assuming your comma-delimited list is in variable strCSVList, try:

Dim intLoop As Integer
Dim varValues As Variant

with rst
.addnew
varValues = Split(strCSVList, ",")
For intLoop = 0 to (rst.Fields.Count - 1)
rst.Fields(intLoop) = varValues(intLoop)
Next intLoop
.update
end with

 

[Guest]

Ok, those are great solutions. The one I had going.... well lets just say it
was getting really really really big and confusing.

Thanks to both of you.

Brad