String Parse - with commas into another table

[jw5107]

I have data structure like so:
IID AllocMstr
2550004146936 SDF,SJU,SLC

I would like for the data to be structured like so:
2550004146936 SDF
2550004146936 SJU
2550004146936 SLC

Below is what I'm working with....
rst1.MoveFirst
Do While Not rst1.EOF
txtField1 = rst1!IID
If Not IsNull(rst1!Allocmstr) Then
txtField2 = Left(rst1!Allocmstr, InStr(rst1!Allocmstr, ",") + 3)
Do Until InStr(txtField2, ",") < 1
rst2.AddNew
rst2!WWRID = rst1!WWRID
rst2!IID = txtField1
rst2!Allocated = Left(txtField2, InStr(txtField2, ",") - 1)
rst2.Update
txtField2 = Mid(txtField2, InStr(txtField2, ",") + 1)
Loop
rst2.AddNew
rst2!WWRID = rst1!WWRID
rst2!IID = txtField1
rst2!Allocated = txtField2
rst2.Update
End If
rst1.MoveNext
Loop

This codes results look like:
2550004146936 SDF
2550004146936 SJU

It only parses the first 2 character values after the comma... I don't know how to fix this...!!
There may be up to 50 commas in a given string/record -- field AllocMstr...
Any suggestions / examples..!!
Thanks,
jw5107

 

[PHV]

You may try something like this:
rst1.MoveFirst
Do While Not rst1.EOF
If Trim(rst1!Allocmstr & "") <> "" Then
txtField1 = rst1!IID
For Each txtField2 In Split(rst1!Allocmstr, ",")
rst2.AddNew
rst2!WWRID = rst1!WWRID
rst2!IID = txtField1
rst2!Allocated = txtField2
rst2.Update
Next
End If
rst1.MoveNext
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886