Loop through 2 arrays simultaneously and add the records to a new table
Posted on 2011-03-16
This is an addendum to my previous question which was answered. The code below works when I split a concatenated string from a field in Table A and append the records to a Field in Table B. Now I have added a second concatenated field from Table A, that I need to append concurrently into a different field from the same record of Table B.
Field 1 = 10, 20, 30, 40
Field 2 = 50, 60, 70, 80
becomes . . .
Record 1: Field 1 = 10 Field 2 = 50
Record 2: Field 1 = 20 Field 2 = 60
Record 3: Field 1 = 30 Field 2 = 70
Record 4: Field 1 = 40 Field 2 = 80
The code below achieves the original desired result in appending Field 1 from Table A into separate records in Table B. I have tried different coding combinations to include the second split but nothing has worked. Thanks for your help again.
Public Sub SplitSetReps()
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim intX As Integer
Set rsIn = CurrentDb.OpenRecordset("SELECT * Table A WHERE ID = " & Forms!frmMainDetail!StrID, dbOpenDynaset)
Set rsOut = CurrentDb.OpenRecordset("Table B", dbOpenDynaset)
Do While Not rsIn.EOF
If Not IsNull(rsIn!Reps) Then
astrNames = Split(rsIn![Reps], ",")
For i = 0 To UBound(astrNames)
rsOut![SetsRep] = astrNames(i)
rsOut![SetsRepID] = rsIn!StrID