Splitter!

[alexfwalker81]

I'm using the code below in a database which I've inherited. It works beautifully to split long strings on a pipe delimiter and push the split values and the 'Source/Medium' values into a table. However, the origin table has an autonumber in a field called 'ID'. I'd like to pass this to the output table as well, but don't know how to adjust the code.

Public Function Splitter()

10    On Error GoTo PROC_Error
      Dim db As DAO.Database
      Dim rsSource As DAO.Recordset
      Dim rsOut As DAO.Recordset
      Dim SplitToRows() As String
      Dim i As Integer
20    Set db = CurrentDb
30    Set rsSource = db.OpenRecordset("tbl_Import_Source_Medium_TID")
40    Set rsOut = db.OpenRecordset("tbl_Split_Source_Medium_TID")
50    If (Not rsSource.BOF And Not rsSource.EOF) Then
60        rsSource.MoveFirst
70        Do Until rsSource.EOF
80            SplitToRows = Split(rsSource![Transaction ID], "|", -1)
90            For i = LBound(SplitToRows()) To UBound(SplitToRows())
100           rsOut.AddNew
110           rsOut("Source/Medium") = rsSource("Source/Medium")
150           rsOut("Transaction ID") = SplitToRows(i)
160           rsOut.Update
170           Next i
180           rsSource.MoveNext
190       Loop
200   Else
210           MsgBox "No Records in Input"
220   End If
230   rsSource.Close
240   Set rsSource = Nothing
250   rsOut.Close
260   Set rsOut = Nothing
270   Set db = Nothing
PROC_Exit:
280   Exit Function
PROC_Error:
300   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in splittoRows procedure"
310      On Error GoTo 0
 
Resume PROC_Exit:
End Function

My guess is that I'd add a new line like this;

120           rsOut("ID") = rsSource("ID")

Would that work?

 

[theDBguy]

Looks about right. Have you tried it?

 

[arnelgp]

note that "ID" field on rsOut must be Long datatype (not autonumber) to be able to saved.

70        Do Until rsSource.EOF
80            SplitToRows = Split(rsSource![Transaction ID], "|", -1)
90            For i = LBound(SplitToRows()) To UBound(SplitToRows())
100           rsOut.AddNew
              rsOut("theIDFieldName") = rsSource("ID")   
110           rsOut("Source/Medium") = rsSource("Source/Medium")
150           rsOut("Transaction ID") = SplitToRows(i)
160           rsOut.Update
170           Next i
180           rsSource.MoveNext
190       Loop
200   Else
210           MsgBox "No Records in Input"
220   End If

 

[alexfwalker81]

Looks about right. Have you tried it?

Not quite sure why I didn’t just try it before I posted! Yes, just slotted it in and it worked perfectly (once I’d remembered to add ‘ID’ into the relevant query beforehand...)

 

[theDBguy]

Not quite sure why I didn’t just try it before I posted! Yes, just slotted it in and it worked perfectly (once I’d remembered to add ‘ID’ into the relevant query beforehand...)

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.