query 1 pulls ONLY the recs with commas, then parses each item in list ,then writes the record to the table.
something like:
Code:
Public Sub ParseCommaTbl()
Dim rst
'query1 ONLY pulls the records with commas
'query1 = "select * from table where instr([device model(s)],',')>0")
DoCmd.SetWarnings False
Set rst = CurrentDb.OpenRecordset("select * from query1")
With rst
While Not .EOF
VREF = .Fields("[Reference ID]").Value
vDevice = .Fields("[Device UUID(s)]").Value
vFld = .Fields("[Device Model(s)]").Value
i = InStr(vFld, ",")
While i > 0
vWord = Left(vFld, i - 1)
vFld = Mid(vFld, i + 1)
GoSub PostRec
Wend
'post last item in comma list
vWord = vFld
GoSub PostRec
.MoveNext
Wend
End With
'remove comma recs
' DoCmd.OpenQuery "qdDeleteCommaRecs"
DoCmd.SetWarnings True
Exit Sub
'post single rec to the table
PostRec:
sSql = "Insert into table ([REFERENCE ID],[Device UUID(s)],[Device Model(s)]) VALUES ('" & VREF & "','" & vDevice & "','" & vWord & "')"
DoCmd.RunSQL sSql
Return
End Sub