Help with export filtered records to a table from subform (1 Viewer)

[Angel69]

Hi,

I have a form with a subform. In the form header I have combo boxes for the user to filter data. I want to add a button that they would click to export the filtered records to a table. I have the following code and get the message that records were exported successfully but when I open the table they are not there. I don't get any errors when I compile the code but I also don't get any results in the immediate window either. Any ideas? TIA

Private Sub cmdAddToTable_Click()
    'Dim tmpRS As DAO.Recordset
    'Dim tmpSQL As String
 
    Dim strWhere As String
    Dim strSQL As String
    Dim tmpRS As DAO.Recordset
    Dim lngRecord_Count As Long
 
    Set tmpRS = Me!frmPacsVerificationSubForm.Form.RecordsetClone
 
    'We do the following to get an accurate count of records.
    With tmpRS
        .MoveLast
        .MoveFirst
    End With
 
    lngRecord_Count = tmpRS.RecordCount
    If lngRecord_Count = 0 Then
        MsgBox "No records to export."
        GoTo Exit_Now
    End If
    tmpRS.Close
    Set tmpRS = Nothing
 
    If Me!frmPacsVerificationSubForm.Form.FilterOn = True Then 'filter had been applied.
        strWhere = Me!frmPacsVerificationSubForm.Form.Filter
        If strWhere <> "" Then
            strWhere = "WHERE " & strWhere
        End If
    End If
          strSQL = "INSERT INTO tblResultsTrackingFreeForm (" & _
                            "[ID], " & _
                            "[Request ID], " & _
                            "[Product Rating], " & _
                            "[Pay Type], " & _
                            "[Amount], " & _
                            "[Create Date], " & _
                            "[Approved Date], " & _
                            "[Approver ADENT], " & _
                            "[Business_Line], " & _
                            "[QAComments1], " & _
                            "[QAComments2], " & _
                            "[QAComments3], " & _
                            "[SPA]) "
    strSQL = strSQL & "SELECT tblPacsfreeform.ID, " & _
                                         "tblPacsfreeform.[Request ID], " & _
                                         "tblPacsfreeform.RISK_WEIGHT_RATING_DESC, " & _
                                         "tblPacsfreeform.[Payment Type Description], " & _
                                         "tblPacsfreeform.[Transaction Amount], " & _
                                         "tblPacsfreeform.[Created Date], " & _
                                         "tblPacsfreeform.[Approved Date], " & _
                                         "tblPacsfreeform.[Approved By Full Name], " & _
                                         "tblPacsfreeform.LOB, " & _
                                         "tblPacsfreeform.[Memo(Most Recent)], " & _
                                         "tblPacsfreeform.[Memo(2nd Most Recent)], " & _
                                         "tblPacsfreeform.[Memo(3rd Most Recent)], " & _
                                         "tblPacsfreeform.[AddToSPA] " & _
            "FROM tblPACSTemplate " & _
            strWhere & ";"
   With DoCmd
        .SetWarnings False
        .RunSQL strSQL
        .SetWarnings True
   End With
    MsgBox lngRecord_Count & " records were added to results tracking table", vbOKOnly, "Records added successfully"
Exit_Now:
Exit Sub
End Sub

 

[Estuardo]

G'd evening,
Do you get any rows by running your SELECT query from the query editor with the same parameters you have in your form?

SELECT tblPacsfreeform.ID,   
                                         tblPacsfreeform.[Request ID],   
                                         tblPacsfreeform.RISKWEIGHTRATINGDESC,   
                                         tblPacsfreeform.[Payment Type Description],   
                                         tblPacsfreeform.[Transaction Amount],   
                                         tblPacsfreeform.[Created Date],   
                                         tblPacsfreeform.[Approved Date],   
                                         tblPacsfreeform.[Approved By Full Name],   
                                         tblPacsfreeform.LOB,   
                                         tblPacsfreeform.[Memo(Most Recent)],   
                                         tblPacsfreeform.[Memo(2nd Most Recent)],   
                                         tblPacsfreeform.[Memo(3rd Most Recent)],   
                                         tblPacsfreeform.[AddToSPA]   
            FROM tblPACSTemplate   
            WHERE yourfilter=criteria;

Don't get me wrong, but please change your column names following any naming convension like this one, or if you want to get in depth this is a good article

 

[Angel69]

Estuardo, I know we are getting off topic but I appreciate you bringing the naming convention issue to my attention. I am importing data that the user gets from another application so I have no control how the column headings come into the dbase. I have to keep them the same since we append the new file each week the column headings need to match. Do you have a suggestion around this? BTW, my sql works fine with the naming convention as I just tested it in the query editor. Thanks.

 

[MarlaC]

Angel, you don't have to maintain identical field names if you build and save an import specification (use the import wizard for your file, then before clicking Finish on the final page, click Advanced). This will also allow you to set field types and/or skip unneeded fields, and that way you can put to use a naming convention that will simplify ongoing development for you and do away with the spaces.