CurrentDb.Execute "INSERT INTO tblActiveMaster(UnitCount, Line Number, Question, AFI, PagePara, PubDate, UnitAssigned, CycleNumber, DateAssigned, SuspenceDate, CriticalYN, AssignedBy) Values (UC,'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "','" & pubD & "','" & tempName & "', CycleNum,'" & Dassign & "','" & suspDate & "', Crit,'" & assignby & "')"
CurrentDb.Execute "INSERT INTO tblActiveMaster(UnitCount, Line Number, Question, AFI, PagePara, PubDate, UnitAssigned, CycleNumber, DateAssigned, SuspenceDate, CriticalYN, AssignedBy) Values (" & UC & ",'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "',#" & pubD & "#,'" & tempName & "'," & CycleNum & ",#" & Dassign & "#,#" & suspDate & "#," & Crit & ",'" & assignby & "')"
Debug.print "INSERT INTO tblActiveMaster(UnitCount, Line Number, Question, AFI, PagePara, PubDate, UnitAssigned, CycleNumber, DateAssigned, SuspenceDate, CriticalYN, AssignedBy) Values (" & UC & ",'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "',#" & pubD & "#,'" & tempName & "'," & CycleNum & ",#" & Dassign & "#,#" & suspDate & "#," & Crit & ",'" & assignby & "')"
CurrentDb.Execute "INSERT INTO tblActiveMaster Values (" & UC & ",'" & LineNum & "','" & Qstn & "','" & AF & "','" & PP & "',#" & pubD & "#,'" & tempName & "'," & CycleNum & ",#" & Dassign & "#,#" & suspDate & "#," & Crit & ",'" & assignby & "')"
CurrentDb.Execute "INSERT INTO tblActiveMaster (UnitCount, [Line Number],
Dim i As Integer, iPrevious As Integer, strNames As String, tempName As String, UC As Integer, LineNum As String, Qstn As String, AF As String, PP As String, pubD As String, CycleNum As Integer, Dassign As String, suspDate As String, Crit As Integer, assignby As String, tb as DAO.recordset
set tb = currentdb.openrecordset("tblmasterchecklisttemp")
tb.movefirst
do while tb.eof = false
strNames = tb!UnitAssigned
UC = tb!UnitCount
LineNum = tb![Line Number]
Qstn = tb!Question
AF = tb!AFI
PP = tb!PagePara
pubD = tb!PubDate
CycleNum = tb!CycleNumber
Dassign = tb!DateAssigned
suspDate = tb!SuspenceDate
Crit = tb!CriticalYN
assignby = tb!AssignedBy
'add a trailing semicolon to check end of string later
If Right(strNames, 1) <> ";" Then
strNames = strNames & ";"
End If
i = 1
Do While i < Len(strNames)
iPrevious = i
i = InStr(i, strNames, ";") + 1
tempName = Mid(strNames, iPrevious, i - iPrevious - 1)
CurrentDb.Execute 'Insert your finalized SQL statement here
Loop
tb.movenext
loop
tb.close
End Sub