Reply to thread

Message: [QUOTE="Dale, post: 2131735"] Hello, If Ken Snell [MVP] is watching....using your code in Access 2007 I am rec'ving this error, "compile error: constant expression required" with QName being highlighted. I for the life of can't figure out why! I have all the references set. If I replace QName with "somethingelse", the error goes away (actually then I error out on the dlookup but thats a different problem)? Thanks for your help. Dim qdf As DAO.QueryDef Dim dbs As DAO.Database Dim rstMgr As DAO.Recordset Dim strSQL As String, strTemp As String, strMgr As String Const strFileName As String = "empaccess" Const strQName As String = "zExportQuery" Set dbs = CurrentDb() ' Create temporary query that will be used for exporting data; ' give it a dummy SQL statement initially strTemp = dbs.TableDefs(0).Name strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;" Set qdf = dbs.CreateQueryDef(strQName, strSQL) qdf.Close strTemp = strQName ' *** code to set strSQL needs to be changed to conform to your ' *** database design -- ManagerID, EmployeesTable need to ' *** be changed to your table and field names ' Get list of manager IDs -- note: replace my generic table and field 'names ' with the real names of the employees table and the manager ID field strSQL = "SELECT DISTINCT manager FROM categories;" Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) ' Now loop through list of manager IDs and create a query for each ID ' so that the data can be exported -- the code assumes that the actual 'names ' of the managers are in a lookup table -- again, replace generic names 'with ' real names of tables and fields If rstMgr.EOF = False And rstMgr.BOF = False Then rstMgr.MoveFirst Do While rstMgr.EOF = False ' *** code to set strMgr needs to be changed to conform to your ' *** database design -- ManagerNameField, ManagersTable, ' *** ManagerID need to be changed to your table and field names ' *** be changed to your table and field names strMgr = DLookup("manager", "categories", "manager = '" & rstMgr!Manager.Value & "'") ' *** code to set strSQL needs to be changed to conform to your ' *** database design -- ManagerID, EmployeesTable need to ' *** be changed to your table and field names strSQL = "SELECT * FROM qrymanager WHERE " & _ "manager = " & rstMgr!Manager.Value & ";" Set qdf = dbs.QueryDefs(strTemp) qdf.Name = "q_" & strMgr strTemp = qdf.Name qdf.SQL = strSQL qdf.Close Set qdf = Nothing ' Replace C:\FolderName\ with actual path DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ strTemp, "G:\" & "empaccess" & ".xls" rstMgr.MoveNext Loop End If rstMgr.Close Set rstMgr = Nothing dbs.QueryDefs.Delete strTemp dbs.Close Set dbs = Nothing 'End of code End Sub [/QUOTE]

Verification