Export a form's recordset to a table

[Angel69]

Thank you!

That seems to work but not consistently. For instance I tried it and it worked. Tried it again and got error similar to Run-time error '3075' Syntax error (missing operator) in query expression "Rec'd email from Laura A. Siess-Gannon'.

This is the text in memo2 field for one of my records. I then closed out and opened and tried again a few times and it worked. Just now I tried again and got the above mentioned error '3075'. I'm trying to figure out if a certain combination of filters I'm using is triggering the error since I have three fields I'm filtering plus the dates. I did not use the dates filter in all cases.

Thanks!

 

[pr2-eugin]

There is your problem.. You have single quotes somewhere that makes the syntax error.. You can use """ instead of using single quotes I am away from my system.. It should be something like..

Insert into tablename values (""" & stringVariable & """)

Previously you would have had..

Insert into tablename values ('" & stringVariable & "')

 

[Angel69]

Hi pr2-eugin,

You meant to put the """" around the memo fields, correct? I did this and get an error.

Here's my debug.print output:

INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date, Approved_Date, Approved_ADENT, Business_Line, ID, Updated_Date, Created_Name, Created_ADENT, Open_By_AU, Initiated_ADENT, Initiated_Name, Approved_Name, Memo1, Memo2, Memo3) VALUES ('912246','Henn2006A','ACH',#5/31/2012 11:28:17 AM#,#5/29/2013 11:19:02 AM#,'BIRKM01','CMES','151', #5/29/2013 11:19:02 AM#,'GREG WEIS','WEISG01','0117472','WEISG01', 'GREG WEIS','MARK BIRKHOLZ',"" & tmpRS!Memo1 & "","" & tmpRS!Memo2 & "","" & tmpRS!Memo3 & "")

As you can see, all looks good except the memos. I see some of my memos actually have text with quotes around them. I'm wondering if that is throwing it off??

 

[pr2-eugin]

This should work..

Private Sub cmdAddToTable_Click()
    Dim tmpRS As DAO.Recordset, tmpSQL As String
    Set tmpRS = Me.frmPacsVerificationSubForm.Form.RecordsetClone
    
    If tmpRS.RecordCount > 0 Then
        Do While Not tmpRS.EOF
            tmpSQL = "INSERT INTO tblResultsTracking (Template_ID, Template_Name, Payment_Type_Description, Created_Date, Approved_Date, Approved_ADENT, " & _
                     "Business_Line, ID, Updated_Date, Created_Name, Open_By_AU, Initiated_ADENT, Initiated_Name, Approved_Name, Memo1, Memo2, Memo3) VALUES (""" & _
                     tmpRS!Template_ID & """,""" & tmpRS!Template_Name & """,""" & tmpRS!Payment_Type_Description & """,#" & tmpRS!Created_Date & "#,#" & _
                     tmpRS!Approved_Date & "#,""" & tmpRS!Approved_ADENT & """,""" & tmpRS!Business_Line & """,""" & tmpRS!ID & """, #" & tmpRS!Updated_Date & "#,""" & _
                     tmpRS!Created_Name & """, """ & tmpRS!Open_By_AU & """, """ & tmpRS!Initiated_ADENT & """, """ & tmpRS!Initiated_Name & """, """ & tmpRS!Approved_Name & _
                     """, """ & tmpRS!Memo1 & """, """ & tmpRS!Memo2 & """, """ & tmpRS!Memo3 & """)"
            Debug.Print tmpSQL
            CurrentDb.Execute tmpSQL
            tmpRS.MoveNext
        Loop
    End If
    Set tmpRS = Nothing
End Sub

 

[Angel69]

Works great! Thank you so much!!