Hi!

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.
I'm very very new to access and VB and any help would be greatly
appreciated!

Below 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

try

'set field to string
narratorStr = rst("Narrator")

hth


"sadiewms" <sadiewms@gmail.com> wrote in message
news:1140551889.741615.291710@z14g2000cwz.googlegr oups.com...[color=blue]
> Hi!
>
> 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.
> I'm very very new to access and VB and any help would be greatly
> appreciated!
>
> Below 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
>[/color]