Is there a way to append data that is separated by commas in a text box to a table?

For instance, if I wanted to append the following items: dog, cat, horse, mouse as separate records into a table, would that be possible? I'm thinking of how you can enter multiple tags in a Blogger post and each tag will list that post.

Thanks in advance for any advice.

Hi David,

you can parse the text based on the comma to an array -- then use an Append query



WHERE
controlname_ID is the Name of the control with the primary key (am assuming it is numeric)
controlname_tags is the Name of the control with your list

Thanks for the response. I'll mess around with it and see what I can come up with.

Thanks again.

you're welcome, David (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

If you have trouble with the code, post back with your controlnames (or fieldnames -- but tell us what you are providing) and tell us the data type of each

I too am struggling with trying to parse a comma-delimited string into multiple records. Here's an example of what I'm trying to do: I have one table (tblEmployees) with two fields (EmployeeID,Children). The Employee field contains a unique numeric character set (e.g. "123) while the Children field contains a single comma-delimited string of children names (e.g. "Matthew, Mark, Luke, John). I would like to populate a second table (tblEmployees2) with two fields (EmployeeID,Child) with same EmployeeIDs from the first table, but with a single child's name in the Child field of each record.

Can you help me use the code you provided above to do this? Once I can get my head around this simple example, I think I can extrapolate it to what I REALLY need to do.

I'm using a form to do this, with text box controls "EmployeeID" and "Children" with "Children" containing the comma-delimited string. I'd like to use the on click event of a button on the form to create the arrays in each record. Also, is it possible to display the array "as is" in a control on the single record form?

Thanks!

Edited by: BlueDeviledEgg on Wed Feb 4 14:40:24 EST 2009.

Hi BlueDeviledEgg (what is your name?)

for the records you have already created, you will probably want to loop through the table and parse the Children into arrays, then write those values to another table using an Append Query

put this code into a general (standard) module



WHERE
EmployeeID is the Primary Key in the Employees table and the Foreign Key in the Employee Children table
ChildNameField is the Name of the field with the Child's name
EmpChildren is the Name of the table with Employee Children

as for your question about displaying names in a comma delimited list once you have separated them, look here:

Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm

or here:

code: LoopAndCombine
http://www.utteraccess.com/forums/showflat...;Number=1230988

**********************************************************
*** How to Create a Standard (General) Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc


if you are copying and pasting from the forum, you may find that the lines don't break where they are supposed to -- in that case, copy code from the forum to Word, then copy from Word to a module in Access (make sure Word does not wrap lines -- set the font real small and/or put it in landscape)

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up