Unmerge data output from Sharepoint to Access (2010) (1 Viewer)

[mdvr613]

Good day all,

Here is some background

We have a Team that enters data through an Infopath form to Sharepoint. The Infopath has 13 LcNum fields with a tracking number acting as a primary field. If the user needs to exceed the 13 LcNum (LcNum1-LcNum13) fields in a repeating table can be used which would allow 14+ fields (in addition to the first 13/LcNum14-LcNum20).

Here is where it gets tricking thanks to MS Sharepoint and Info path.

The table is linked to a Manager Access database. The Manager would review these items and would requiere to assign each LcNum item to a different user.

This is done by porting some of the data over to a second table that currently grabs the LCNum column and create a individual record per LCNum + Tracking number.

The code being used is as follows (and it does work):

Sub Combine()
Dim strDescription As String
Dim strSQL As String, strSQL2 As String, strSQL3 As String
Dim db As Database
Dim rs As Recordset, rs2 As Recordset
Dim I As Integer, RecCount As Integer
strSQL = " SELECT  [New Package].* FROM [New Package];"
strSQL2 = " SELECT [Items to Scan].* FROM [Items to Scan];"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Set rs2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
With rs
    .MoveFirst
    .MoveLast
    RecCount = .RecordCount
End With
RecCount = RecCount * 7
Counter = 0
'If rs.RecordCount > 0 Then rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
I = 9
Do Until I <> Null Or I = 22
    With rs
      TrackingNumber = rs.Fields(8)
      LCNum = rs.Fields(I)
      
    End With
    
    With rs2
        rs2.AddNew
        rs2!TrackingNumber = TrackingNumber
        rs2!LCNumber = LCNum
        rs2!Title = LCNum
    
        '.Update
        Counter = Counter + 1
    End With
    
    I = I + 1
    Loop
    rs.MoveNext
Loop
    
    If RecCount <> Counter Then
        MsgBox "Please ensure all records successfully moved"
    Else
        MsgBox RecCount & " records were added"
    End If
    
    'strSQL3 = "Delete* from tbl_Volume"
    'DoCmd.SetWarnings False
    'DoCmd.RunSQL strSQL3
    'DoCmd.SetWarnings True
    
End Sub

The issue is once the 13 items are exceeded the items would have to be added to that repeating table. The column range is LcNum14-LcNum20, but since this is a repeating table the multiple rows will merge in to one cell (under the respective column) when saved to Sharepoint. When extracted in Access we would have to extract the merged data from this range first so we could run the code provided above. Each LCNum range will always be limited to 8 charaters; on the merged cells it would reflect multiple of 8 (16 charactors represent 2 LcNum etc.)

I hope this makes sense.

All I need to do is extract the merge data within the cell to strings of 8 across LcNum14-LcNum20 if data is avaliable.

Any help or quidance is greatly appreciated.

Thanks!