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.
Full Version: Form and table update question
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
you can parse the text based on the comma to an array -- then use an Append query
CODE
Dim arrVariable() As String _
, i As Integer _
, strSQL as string
arrVariable = Split(me.controlname_tags, ",")
For i = LBound(arrVariable) To UBound(arrVariable)
strSQL = "INSERT INTO Tablename " _
& " FIELDS (KeyField, OtherField) " _
& " SELECT " & me.controlname_ID _
& "'" & arrVariable(i) & "';"
currentdb.execute strSQL
Next i
MsgBox "Done"
, i As Integer _
, strSQL as string
arrVariable = Split(me.controlname_tags, ",")
For i = LBound(arrVariable) To UBound(arrVariable)
strSQL = "INSERT INTO Tablename " _
& " FIELDS (KeyField, OtherField) " _
& " SELECT " & me.controlname_ID _
& "'" & arrVariable(i) & "';"
currentdb.execute strSQL
Next i
MsgBox "Done"
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.
Thanks again.
you're welcome, David 
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
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.
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
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
CODE
Sub ParseChildren()
' CLICK HERE
' press F5 to Run
'NEEDS REFERENCE to Microsoft DAO Library
' Crystal (strive4peace)
'set up Error Handler
On Error GoTo Proc_Err
dim db as dao.database _
, rs as dao.recordset
Dim arrVariable() As String _
, i As Integer _
, strSQL as string
set db = currentdb
set rs = db.OpenRecordset("tblEmployees", dbOpenSnapshot)
do while not rs.eof
if len(trim(nz(rs!Children,""))) > 0 then
arrVariable = Split(rs!Children, ",")
For i = LBound(arrVariable) To UBound(arrVariable)
strSQL = "INSERT INTO [EmpChildren] " _
& " FIELDS (EmployeeID, [ChildNameField]) " _
& " SELECT " & rs!EmployeeID _
& "'" & arrVariable(i) & "';"
currentdb.execute strSQL
Next i
end if
rs.MoveNext
loop
MsgBox "Done"
Proc_Exit:
On Error Resume Next
'close and release object variables if applicable
rs.close
set rs = nothing
set db = nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ParseChildren"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
' CLICK HERE
' press F5 to Run
'NEEDS REFERENCE to Microsoft DAO Library
' Crystal (strive4peace)
'set up Error Handler
On Error GoTo Proc_Err
dim db as dao.database _
, rs as dao.recordset
Dim arrVariable() As String _
, i As Integer _
, strSQL as string
set db = currentdb
set rs = db.OpenRecordset("tblEmployees", dbOpenSnapshot)
do while not rs.eof
if len(trim(nz(rs!Children,""))) > 0 then
arrVariable = Split(rs!Children, ",")
For i = LBound(arrVariable) To UBound(arrVariable)
strSQL = "INSERT INTO [EmpChildren] " _
& " FIELDS (EmployeeID, [ChildNameField]) " _
& " SELECT " & rs!EmployeeID _
& "'" & arrVariable(i) & "';"
currentdb.execute strSQL
Next i
end if
rs.MoveNext
loop
MsgBox "Done"
Proc_Exit:
On Error Resume Next
'close and release object variables if applicable
rs.close
set rs = nothing
set db = nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ParseChildren"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.