I think I detect a little conflict between your two answers. If there are a
variable number of elements, how will you determine how many fields there are
in the destination table? That is, if one of them has four elements and one
has twenty-nine, it would seem that you need a table with twenty-nine fields,
and some records will have some fields empty. In that case, would the
four-field record be just in the first four fields, or is there some criteria
for determining which field the data goes into when it has fewer fields than
the maximum number in the batch. Post back whether you want to:
1. Determine the maximum number of fields required before you begin and make
the table beforehand, then run this as an append query, or
2. Use a make-table query and include a process to find the record having
the highest number of members at runtime.
Either can be done.
Third question: will there ever be spaces within the individual members, or
only next to the commas? This is important when coding the Replace. If there
are no spaces in the members, then we can Replace all spaces with commas, as
in the optional line below; if not, it will be more complicated.
In the meantime, here is code that will split a delimited field like this
into separate records (not fields). It is not quite what you want, but will
demonstrate the process. Your answers to the questions above regarding number
of fields will help define getting it to separate them into fields instead of
records.
Private Sub ButtonRun_Click()
'requires tblA & tblB, each with at least one text field named Field1
'tblA (source table) to have Field1 populated with comma-delimited
'tblB
Dim txtStatus As Variant 'used for status bar
Dim varSplit As Variant 'used to split values into array
Dim recCount As Long 'used to count records to show progress on status bar
Dim recCurrent As Long 'used to show current position on status bar
Dim ValInput As String 'used to look up the original value
Dim SplitVal As String 'used for each element of array
Dim strSQL As String 'used to build SQL expression used to append records
Dim rsTest As Recordset
Set rsTest = CurrentDb.OpenRecordset("tblA", dbOpenSnapshot)
rsTest.MoveLast 'force accurate record count
recCount = rsTest.RecordCount 'get record count for status bar meter
rsTest.MoveFirst 'start at the beginning
txtStatus = SysCmd(acSysCmdInitMeter, "Populating tblB...", recCount) 'start
the status bar meter
Do While Not rsTest.EOF
recCurrent = recCurrent + 1
txtStatus = SysCmd(acSysCmdUpdateMeter, recCurrent) 'increment the
status bar meter
ValInput = rsTest.Fields("Field1").Value 'get the field value
' ValInput = Replace(ValInput," ","") 'enable this line to strip out all
spaces
varSplit = Split(ValInput, ",", -1, 0) 'split it into an array
Dim intMembers As Integer 'used to identify how many members are in this
particular record/array
intMembers = UBound(varSplit) 'identify highest array member
Dim intCurrent As Integer 'used to loop through members
'insert each part of the original string as a separate record
For intCurrent = 0 To intMembers
SplitVal = varSplit(intCurrent)
strSQL = "INSERT into tblB (Field1) SELECT '" & SplitVal & "'"
CurrentDb.Execute strSQL, dbFailOnError
Next intCurrent
rsTest.MoveNext 'move to next record
Loop 'go back & process the next record
txtStatus = SysCmd(acSysCmdClearStatus) 'clear the status bar
MsgBox "Done" 'notify user that it is done
End Sub
