split a string by comma and break it up into rows (1 Viewer)

[lala]

i have a string that i need broken up into rows

ID EMAIL
33455 1@yahoo.com,2@yahoo.com,3@yahoo.com,4@yahoo.com,5@yahoo.com,6@yahoo.com,7@yahoo.com,8@yahoo.com

is that possible? i know how to split it and get each value, but don't know how to insert it into a table one at a time

besides inserting each value, i also have to insert the ID, so for each row i have to insert one email address and the id, and repeat it 8 times

number of email addresses is always different

thank you

 

[DCrake]

Are you using the Split() function to copy the items into an array? If so what cod have you got in place now?

 

[lala]

Private Sub SplitTest_Click()

Const TestString As String = "1yahoo.com,2yahoo.com,3yahoo.com,4yahoo.com,5yahoo.com,6yahoo.com,7yahoo.com,8yahoo.com"

Dim strText As String
Dim strParts() As String
Dim intCounter As Integer


strParts = Split(TestString, ",")

For intCounter = LBound(strParts()) To UBound(strParts())
    MsgBox strParts(intCounter)
Next intCounter
End Sub

again, not my code, off the internet and edited

 

[lala]

the code above gives me the email addresses as it loops through the string

how do i take the first emaill address and append it to the table? bu using an append query?
and literally just loop through and do them one by one?

if so, then i know what to do, if there's a better way then i need help.

 

[lala]

i got it!!!!
as long as there's not an easier way of doing it than appending them one by one - i got it to work

 

[DCrake]

Try This

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset(YourTableHere)

For intCounter = LBound(strParts()) To UBound(strParts())
    Rs.AddNew
    Rs(YourEmailfield) =  strParts(intCounter)
    Rs.Update
Next intCounter

Rs.Close
Set Rs = Nothing

 

[lala]

this is much faster, thank you so much!!!!!!!!!

 

[jjjones009]

I have a similar issue as above, but it has caused another issue that I cannot figure out on my own. I have gotten the below code to work (thank you for helping me with that), but I cannot get the selected groupid in the groupid listbox to populate in a second column in my table duplicating for each value of the classtext string. Here is the code and I attached an image to show the current output vs. the desired output.

The classtext string that I am entering is 214, 215
Add the value that I am selecting in my groupid list box is 4

Private Sub addclasses_Click()
Dim Rs As DAO.Recordset
Dim groupid As Integer
Dim strParts() As String
Dim intCounter As Integer
strParts = Split(classtext, ",")
groupid = groupidlist.ItemData(groupid)

Set Rs = CurrentDb.OpenRecordset("LU_REPORT_GROUPING_CLASS")

For intCounter = LBound(strParts()) To UBound(strParts())
    Rs.AddNew
    Rs("CLASS_ID") = strParts(intCounter)
    Rs("REPORT_GROUPING_ID") = groupidlist.ItemData(groupid)
    Rs.Update
Next intCounter

Rs.Close
Set Rs = Nothing
End Sub

Please help me, because I have been :banghead: for too long on this.

Thank You