Split or parse a column of data into rows in Access

[melgra2017]

Hi, I have a file that I've imported into Access 2016 that contains 3 columns. One of the columns contains many values separated by a space. These can be varied in length. Ideally, I would like to create rows for each of these values including the Item Number field so things are unique. The following code I've found on another site but it isn't running and I don't get any errors so I'm not sure what's wrong. Thanks for the help in advance.


Sample
Item Numbers Profile SP_WorkTicket
ABC1235 ABCD17 Issue1 Issue2 Issue3 Issue4






Public Function BreakToWords()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant 'array to hold the split phrase
Dim i As Integer 'counter


Set rsOrig = CurrentDb.OpenRecordset("SELECTs.[Item Numbers], Slips.SP_WorkTicket FROM Slips where [Item Numbers] is not null;")
Set rsNew = CurrentDb.OpenRecordset("ItemNumberWorkTickets")


If rsOrig.RecordCount <> 0 Then


'loop the rsOrig records
rsOrig.MoveFirst
While Not rsOrig.EOF
'split the phrase on a space delimiter
vArr = split(rsOrig("[Item Numbers]"), ",")


'loop the array (words) and add to rsNew
For i = 0 To UBound(vArr)
With rsNew
Debug.Print vArr(i)
.AddNew
.Fields("SP_WorkTicket") = rsOrig("SP_WorkTicket")
.Fields("[ItemNumber]") = Trim(vArr(i))
.Update
End With
Next


rsOrig.MoveNext
Wend


End If


rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing


MsgBox "Complete"




End Function

[OBP]

Without having the database to play with it is difficult to see why it is not working.
You are probably not getting an error message as there is no error trapping in that code.
After this line of code

Dim i As Integer

add

On Error GoTo errorcatch

before this line of code

End Function

add

Exit Function

errorcatch:

MsgBox records & " " & count & " " & count2 & " " & Err.Description & " " & fname



Run your code and see if you get an error message.

[melgra2017]

I actually get the error message now on the word "records" in the errorcatch line I added.

[OBP]

Sorry, that is my fault I copied that from another post on here where I am parsing data.

please use this code instead.

MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

[melgra2017]

Well, that worked but the parsing didn't work. It just added the same field (one I want to split) 3 times and didn't add the Item Number to the rows either. So any suggestions? thanks

[OBP]

So to be clear the code worked and you didn't get any error message?

Is it possible for you to provide an example database in Access 2000-2007 with some dummy data in it?
Or at least provide some dummy data and a description of what it should end up looking like?

[melgra2017]

Attached is sample of both the Original data and my desired result. Hopefully, this makes it clear. Thanks again for the help.

[OBP]

OK, I will take a look and get back to you.
Ideally this parsed data should be in a Sub Table with a key field linking it back to the original Item No. Key ID.

[OBP]

Here is an example database with the code that does what you want.
Open Form1 and click on the command button and the code will run and put the records in the table ItemNumberWorkTickets.