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.
Code:
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