Run time error 2001 - you cancelled the previious operation! help!

B

bilbo+

Hello, Ive got this VB for exporting some data to excel, howver this bit

strMgr = DLookup("[GroupName]", "GroupNameTable", _
"[GroupNameID] = " & rstMgr!GroupNameID.Value)

is aparently causing that error, THe field and table names are correct but
it wont proceed any further and comes up with the error - 2001 - you
cancelled the previous operation.

Can anyone help? I've been going over it for hours and I cant get round it!

Thanks,

Will


CODE:

Private Sub Command5_Click()

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 strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
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 and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field
names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT GroupNameID FROM CustomersNEW;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each
ManagerID
' 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, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("[GroupName]", "GroupNameTable", _
"[GroupNameID] = " & rstMgr!GroupNameID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM CustomersNEW WHERE " & _
"GroupNameID = " & rstMgr!GroupNameID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace z:\Desktop\ETC_RoomAllocationTool.xlt with actual path
[transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][,
range]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "Z:\Desktop\" & strMgr & Format(Now(), "ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top