Insert multiple items in one text box into a table one at a time ACCESS 2010

I have a process that works the way I want.  It selects items from a list box and puts them into a text box.  However, as the text box contains more than one data item, how can I then insert them one by one into a table?  that is, the text box would hold data such as: "WEN4687", "TEI18446", "EIC96547".  The text box could hold one to 25 or so items. so is there a way to loop through items in a text box?
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
EDIT: I'm sure others are going to ask why you are taking values from a structured control that accepts MULTIPLE values (a listbox) and then putting those values into something that actually only supports one value (a textbox).   You may be better served using another listbox to move values into.   If you are going to stick with the design using a text box, here's my original comment:

Since a text box is nothing more than a single string, how are you putting these multiple values into the text box and keeping them "separate"?   Whatever you are using to separate the values when you put them INTO the text box will be what you need to use to separate them back out into individual values to loop through them.

I'll take a guess and say you are using some combination of Line Feeds and/or Carriage Returns.  That would put the values on separate lines and make them appear like they are separate values.   In reality, they just appear that way.   The value in the text box is just a string with your delimiter in between each value.  So, you kind of just need to reverse your logic and use that delimiter to SPLIT the string into multiple values.

I believe the SPLIT function will give you what you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Actually, what is going on is the first value of the list box is not seen by the user, only the customer name.  There are three list boxes that the user can select names from as there are different categories.  The various selection eventually will be displayed in one list box that is called Participants, but since it only uses the PersonID, a hidden field as the user only sees the name, I need a way to put the PersonID into the table that the Participants list box is based on so I can then update the table with the user name.  The values are not on different lines and the user will not see them anyway, they are comma separated.  Below is what I have so far as is does loop thorugh the list, my next step is getting the information into the table.  The Split seems to be working so far.

Dim strText As String
Dim i As Integer
Dim intLength As Integer
Dim strID As Variant
Dim strInsert As String

strText = Me.txtCriteria
strID = Split(strText, " ")
For i = 0 To UBound(strID)
 intLength = Len(strID(i)) - 1
 Debug.Print "PersonID: " & Left(strID(i), intLength)  'to see what is going on, will be removed
 strInsert = "INSERT into tblTEMPParticipants(PersonID) Values(" & strID & ")"
Next i
Sandra SmithRetiredAuthor Commented:
Split suggestion worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.