Loop Thru Dao Recordset To Join My Table,

[niloufar]

I have VBA in access to loop thru a recordset and join each row with my main table and create new worksheet.



I get run-time error 3296 JOIN expression not supported

can you experts look thru my code and help me?

Thank you.

Sub ExportReport()

    Dim dbsReport As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rstSKSF As DAO.Recordset
    Dim strSQL As String
     Dim xlsxPath As String

    On Error GoTo ErrorHandler

       Set dbsReport = CurrentDb
       xlsxPath = "I:\Proj\Tr_Rep " & Format(Now(), "mm-dd-yyyy hhmmss AMPM") & ".xlsx"


       'Open a recordset on all records from the SkillSoft Request table that have
   'a Null value in the ReportsTo field.
   strSQL = "SELECT * FROM SKSF_Req WHERE Flag IS NULL"
   Set rstSKSF = dbsReport.OpenRecordset(strSQL, dbOpenDynaset)

   'If the recordset is empty, exit.
   If rstSKSF.EOF Then Exit Sub

   With rstSKSF
      Do Until .EOF

      'join report table with SKSF_request table's Rows
      'Create newworksheet for each report joint with SKSF rows
            Set qdf = dbsReport.CreateQueryDef("Training_Reportsheet", _
            Set qdf = dbsReport.CreateQueryDef("Training_Reportsheet", _
        "SELECT Report.Name, Report.[Employee Role], Report.[Employee Location]," & _
        " Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], " & _
        " Report.[Completion Stat] " & _
        "FROM Report LEFT JOIN SKSF_Req ON Report.[Asset Title] = '" & rstSKSF![Course Name] & "'" & _
        " WHERE (Report.[Asset Title]) = '" & rstSKSF![Course Name] & "'" & _
        " And '" & rstSKSF!Role & "' Like ' * ' & [Report].[Employee Role] & ' * ' " & _
        " GROUP BY Report.Name, Report.[Employee Role], Report.[Employee Location], " & _
        " Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], " & _
        " Report.[Completion Stat], Report.[EMP ID]")

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Training_Reportsheet", xlsxPath, True
            DoCmd.DeleteObject acQuery, "Training_Reportsheet"


         .Edit
          rstSKSF![Flag] = "Y" 'Set Flag
         .Update
         .MoveNext

      Loop
   End With

   rstSKSF.Close
   dbsReport.Close

   Set rstSKSF = Nothing
   Set dbsReport = Nothing

   Exit Sub
'ErrorHandler:
  ' MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

   End Sub

[ranman256]

'quicken this code up by:
build a form
put the codes in a combo box from SKSF_Req.[Course Name]
build a query:"Training_Reportsheet" to use the combo field as criteria.
when you click the Go button
it will scan all values in the combo box, and export the query.

for i = 0 to cboCode.listcount - 1
   cboCode = cboCode.itemdata(i)
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Training_Reportsheet", xlsxPath, True
next
docmd.openquery "quMarkFieldsY"

'no need for DAO since access is already connected.
'THIS way you can run the query and see if it works or if there's an error.
'dont delete the query every time you run it, leave it alone.
'dont update the [Flag] field every time..just do it once at the end of the loop. Build a markup query: Set rstSKSF![Flag] = "Y"

[niloufar]

Hi I attached a sample db, if anybody has time to help me . thank you!Training Report - Copy (2).zip

[niloufar]

I figured it out.
DAO doesn't take `"' Like ' * ' & [Report].[Employee Role] & ' * '` and I was reading from query and had to change it to real table in s`trSQL = "SELECT * FROM SKSF_Req WHERE Flag IS NULL"` Statement.