Special loop with insert query

[julienmy5757]

With your code Seth I have only the last comment in my case

[julienmy5757]

Dim db As DAO.Database
Dim strMain As String
Dim rstMain As DAO.Recordset
Dim strSub As String
Dim rstSub As DAO.Recordset
Dim strComment As String
Dim strInsert As String
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Qry")
Set db = CurrentDb
  
strMain = "SELECT DISTINCT [A], [B], [C], [D] FROM tblR"
Set rstMain = db.OpenRecordset(strMain, dbOpenDynaset)
  
With rstMain
    Do While Not .EOF
        strSub = "SELECT * FROM tblR WHERE " & _
                 "(A = '" & ![A] & "' AND B = '" & ![B] & "'" & _
                 " AND C = " & ![C] & ")" & _
                 "ORDER BY tblR.C, tblR.E ;"
                         
        Set rstSub = db.OpenRecordset(strSub, dbOpenDynaset)
        strComment = ""
  
        Do While Not rstSub.EOF
            strComment = strComment & rstSub!E & ", "
            rstSub.MoveNext
        Loop
  
        strComment = Left(strComment, Len(strComment) - 2)
      
    
      If IsNull(DLookup("[DESC]", "[tblRNEW]", "[A]= '" & ![A] & "' And [B]= '" & ![B] & "' And [R]= '" & ![R] & "' And [E]= '" & strComment & "' And [C]= " & ![C] & "")) = True Then
      
        strInsert = "INSERT INTO tblRNEW ([A], [B], [R], [DESC], [C]) " & _
                    "VALUES ('" & ![A] & "', '" & ![B] & "', '" & ![R] & "', '" & strComment & "', " & ![C] & ")"
  
        db.Execute strInsert, dbFailOnError
        Else
      End If
      
        .MoveNext
    Loop
End With
  
DoCmd.SetWarnings True


Me.Refresh

Corect code but I have en error 3075 , missing operator for the DLOOKUP because I have a ' in my strComment

How can I replace it with a space ?

[Rabbit]

Use the replace function to replace all single quotes in the comments with two single quotes to escape it and preserve the quote.