set recordset field contents to string

[sadiewms]

I'm trying to loop through records in a table, split the contents and
insert each subsequent string in the array into a new table. I need to
determine how to cast the current record in the record set as a string.


Here is what I have so far (see question marks in code...):

Public Sub splitinsert()

'Declare strings
Dim narratorStr As String
Dim anlcid As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Narrator from Denaina")
'Declare Array
Dim Arr()

'Loop through records in Denaina table until end of file
With rst
.MoveFirst
While Not .EOF

'set field to string
narratorStr = ?????

'if the string has semicolon, then split
If InStr(narratorStr, ";") Then

'Set Array to Split field
Arr() = Split(narratorStr, ";")

For Each I In Arr
Arr(I) = I
DoCmd.RunSQL "INSERT INTO person_test (person_test)
values(I)"
Next I
Else
Dim SQL As String
SQL = "INSERT INTO person_test (person_test) values('" &
narratorStr & "')"
DoCmd.RunSQL SQL
'End semicolon if
End If

.MoveNext
Wend
End With
End Sub

 

[Jonathan]

Hi Sadiewms,

I think that you are wanting to loop through the recordset fields
collection...

dim fld as dao.field

for each fld in rst.fields
'use chr(34) to insert quotes for text strings
narratorStr =chr(34) & fld.value & chr(34)

'your code here
....
doevents
next fld

Hope this helps

Luck
Jonathan