Iterate Through Fields in a recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad - 05 Dec 2005 23:56 GMT

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

Reply to this Message

Brendan Reynolds - 06 Dec 2005 00:13 GMT

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

Signature

Brendan Reynolds

> Thanks for taking the time to read my question.
>
[quoted text clipped - 18 lines]
>
> Brad

Reply to this Message

Douglas J. Steele - 06 Dec 2005 00:15 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for taking the time to read my question.
>
[quoted text clipped - 18 lines]
>
> Brad

Reply to this Message

Brad - 06 Dec 2005 00:25 GMT

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

> Thanks for taking the time to read my question.
>
[quoted text clipped - 17 lines]
>
> Brad

Reply to this Message