Insert multiple values from unbound Text Box into table

[Bcanfield83]

Hi All,

I'm trying to figure out how to take a list of values in an unbound Text Box (on a form), and insert those values into a specified table upon clicking a button.

1) User pastes a list of names into the Text Box (txtNames) on the form. Each subsequent name is on a new line, e.g.:
User1
User2
User3
User4
etc.
2) Clicks Button
3) Insert those names into tblUser_Listing; this table contains only a single text field called "Username"

The current solution that I have is inserting the entire list of names into a single record in that table. E.g.: if I paste a list of 10 names into the Text Box and then click the button, it's inserting all 10 names into a single record in the table rather than 10 separate records.

CurrentDb.Execute "Insert Into tblUser_Listing (Username) " & _
                  "Values ('" & Me.txtNames & "')"

Any help would be appreciated. Thanks!

[rpeare]

The only way you can do this reliably is if the formatting of your input is identical each and every time.

For instance if you have

'joe blow patty t smith'

and you were to separate names by a space this would look like 2.5 names rather than 2 names, one with a middle initial.

If you reliably have a first and last name and *only* a first and last name you can load the string into an array then cycle through the array appending one record at a time into your target table.

[orange]

Depending on your circumstances, you could paste the data into a text file, then import the text file using a "format validation" routine to ensure names are of consistent format (as rpeare stated) and insert same into your table. Records where the format is different than expected, thn you could reject that record forr further analysis/activity.
I would advise you to use 2 fields FirstName and LastName and not a single field.