I'm stumped on this error message. I modified this code from samples provided on another website and had things working fine.
Do not recall making any changes to the code, but now it is not working. No references to Run-time Erro 3012 anywhere.
Private Sub cmdExportToExcel_Click()
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT jobCodes.[Job Code], jobCodes.jobtitle, tblJobFunction.JobFunction AS JobFamilyCode, tblJobFunction.[Long Name] AS JobFamily, tblJobFamily.[Job Family] AS FuncitonCode, tblJobFamily.Descr AS Function, jobCodes.Headcount, jobCodes.Grade, tblProposedGrade.ProposedGrade, tblUsers.Name, tblProposedGrade.EffectiveDate, jobCodes.Status, tblProposedStatus.ProposedStatus, jobCodes.[Comp Plan]"
strSQL = strSQL & " FROM ((((((tblJobFamily RIGHT JOIN jobCodes ON tblJobFamily.[Job Family] = jobCodes.[Job Family]) INNER JOIN qryProposedJobGradeMaxDate ON jobCodes.[Job Code] = qryProposedJobGradeMaxDate.JobCode) INNER JOIN tblProposedGrade ON (qryProposedJobGradeMaxDate.JobCode = tblProposedGrade.JobCode) AND (qryProposedJobGradeMaxDate.MaxOfEffectiveDate = tblProposedGrade.EffectiveDate)) INNER JOIN tblUsers ON tblProposedGrade.UserID = tblUsers.UserID) LEFT JOIN tblJobFunction ON jobCodes.[Job Funct] = tblJobFunction.JobFunction) LEFT JOIN qryProposedStatusMaxDate ON jobCodes.[Job Code] = qryProposedStatusMaxDate.JobCode) LEFT JOIN tblProposedStatus ON (qryProposedStatusMaxDate.JobCode = tblProposedStatus.JobCode) AND (qryProposedStatusMaxDate.MaxOfEffectiveDate = tblProposedStatus.EffectiveDate)"
strSQL = strSQL & " GROUP BY jobCodes.[Job Code], jobCodes.jobtitle, tblJobFunction.JobFunction, tblJobFunction.[Long Name], tblJobFamily.[Job Family], tblJobFamily.Descr, jobCodes.Headcount, jobCodes.Grade, tblProposedGrade.ProposedGrade, tblUsers.Name, tblProposedGrade.EffectiveDate, jobCodes.Status, tblProposedStatus.ProposedStatus, jobCodes.[Comp Plan]"
strSQL = strSQL & " " & Form_frmJobViewSub.formFilter
strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF, "C:\Documents and Settings\ckell52\My Documents\CompAnalysisTool_JobExtract.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
End Sub
The error occurs at this line...
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
Do not recall making any changes to the code, but now it is not working. No references to Run-time Erro 3012 anywhere.
Private Sub cmdExportToExcel_Click()
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb
strSQL = "SELECT jobCodes.[Job Code], jobCodes.jobtitle, tblJobFunction.JobFunction AS JobFamilyCode, tblJobFunction.[Long Name] AS JobFamily, tblJobFamily.[Job Family] AS FuncitonCode, tblJobFamily.Descr AS Function, jobCodes.Headcount, jobCodes.Grade, tblProposedGrade.ProposedGrade, tblUsers.Name, tblProposedGrade.EffectiveDate, jobCodes.Status, tblProposedStatus.ProposedStatus, jobCodes.[Comp Plan]"
strSQL = strSQL & " FROM ((((((tblJobFamily RIGHT JOIN jobCodes ON tblJobFamily.[Job Family] = jobCodes.[Job Family]) INNER JOIN qryProposedJobGradeMaxDate ON jobCodes.[Job Code] = qryProposedJobGradeMaxDate.JobCode) INNER JOIN tblProposedGrade ON (qryProposedJobGradeMaxDate.JobCode = tblProposedGrade.JobCode) AND (qryProposedJobGradeMaxDate.MaxOfEffectiveDate = tblProposedGrade.EffectiveDate)) INNER JOIN tblUsers ON tblProposedGrade.UserID = tblUsers.UserID) LEFT JOIN tblJobFunction ON jobCodes.[Job Funct] = tblJobFunction.JobFunction) LEFT JOIN qryProposedStatusMaxDate ON jobCodes.[Job Code] = qryProposedStatusMaxDate.JobCode) LEFT JOIN tblProposedStatus ON (qryProposedStatusMaxDate.JobCode = tblProposedStatus.JobCode) AND (qryProposedStatusMaxDate.MaxOfEffectiveDate = tblProposedStatus.EffectiveDate)"
strSQL = strSQL & " GROUP BY jobCodes.[Job Code], jobCodes.jobtitle, tblJobFunction.JobFunction, tblJobFunction.[Long Name], tblJobFamily.[Job Family], tblJobFamily.Descr, jobCodes.Headcount, jobCodes.Grade, tblProposedGrade.ProposedGrade, tblUsers.Name, tblProposedGrade.EffectiveDate, jobCodes.Status, tblProposedStatus.ProposedStatus, jobCodes.[Comp Plan]"
strSQL = strSQL & " " & Form_frmJobViewSub.formFilter
strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF, "C:\Documents and Settings\ckell52\My Documents\CompAnalysisTool_JobExtract.xls"
dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
End Sub
The error occurs at this line...
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)