Run time error 2001 - you cancelled the operation

  • Thread starter Thread starter bilbo+
  • Start date Start date
B

bilbo+

I have some code which im trying to use to export multiple temporary queries
to multiple files in excel, however I'm having trouble with this section -

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

It keeps coming up with the error - 2001 runtime error - you cancelled the
operation. I've spent hours trying to fix it but to no avail! Any ideas
anyone?

Full 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
 
That very misleading error message often indicates that you've made a
mistake in your DLookup statement: that the table name isn't actually
GroupNameTable, that either GroupName or GroupNameID isn't an actual field
name, that rstMgr!GroupNameID doesn't actually contain a value, or that
GroupNameID is a text field, not a numeric field (in which case you need
quotes around the value: "[GroupNameID] = '" & rstMgr!GroupNameID.Value &
"'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


bilbo+ said:
I have some code which im trying to use to export multiple temporary
queries
to multiple files in excel, however I'm having trouble with this section -

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

It keeps coming up with the error - 2001 runtime error - you cancelled the
operation. I've spent hours trying to fix it but to no avail! Any ideas
anyone?

Full 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
Click to expand...
 
hello, Ive double checked and triple checked and it still doesnt work -
however i did notice that if I click on the zexportquery that it has created
(and not deleted since it didnt ifnish running) there is no sign of
grouptable ID, just a lot of field names that are in fact table names...

If i explain what Ive done a bit more maybe it might help..

I have one table called CustomersNEW which has 8 fields in it and one look
up field, called GroupNameID. This looks up a table called GroupName Table,
which has two field an ID field(autonumber) and a GroupName field.

Thats pretty much all thats relevant I think!

Thanks,

Will

Douglas J. Steele said:
That very misleading error message often indicates that you've made a
mistake in your DLookup statement: that the table name isn't actually
GroupNameTable, that either GroupName or GroupNameID isn't an actual field
name, that rstMgr!GroupNameID doesn't actually contain a value, or that
GroupNameID is a text field, not a numeric field (in which case you need
quotes around the value: "[GroupNameID] = '" & rstMgr!GroupNameID.Value &
"'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


bilbo+ said:
I have some code which im trying to use to export multiple temporary
queries
to multiple files in excel, however I'm having trouble with this section -

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

It keeps coming up with the error - 2001 runtime error - you cancelled the
operation. I've spent hours trying to fix it but to no avail! Any ideas
anyone?

Full 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
Click to expand...
Click to expand...
 
Just to add - when I change it to this

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

(changing the first bit to ID not groupnameID, then it seems to work - it
has made all the files etc. with no error codes - is that correct?




bilbo+ said:
hello, Ive double checked and triple checked and it still doesnt work -
however i did notice that if I click on the zexportquery that it has created
(and not deleted since it didnt ifnish running) there is no sign of
grouptable ID, just a lot of field names that are in fact table names...

If i explain what Ive done a bit more maybe it might help..

I have one table called CustomersNEW which has 8 fields in it and one look
up field, called GroupNameID. This looks up a table called GroupName Table,
which has two field an ID field(autonumber) and a GroupName field.

Thats pretty much all thats relevant I think!

Thanks,

Will

Douglas J. Steele said:
That very misleading error message often indicates that you've made a
mistake in your DLookup statement: that the table name isn't actually
GroupNameTable, that either GroupName or GroupNameID isn't an actual field
name, that rstMgr!GroupNameID doesn't actually contain a value, or that
GroupNameID is a text field, not a numeric field (in which case you need
quotes around the value: "[GroupNameID] = '" & rstMgr!GroupNameID.Value &
"'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


bilbo+ said:
I have some code which im trying to use to export multiple temporary
queries
to multiple files in excel, however I'm having trouble with this section -

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

It keeps coming up with the error - 2001 runtime error - you cancelled the
operation. I've spent hours trying to fix it but to no avail! Any ideas
anyone?

Full 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
Click to expand...
Click to expand...
Click to expand...
 
If it's working, then I'd have to assume it's correct.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


bilbo+ said:
Just to add - when I change it to this

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

(changing the first bit to ID not groupnameID, then it seems to work - it
has made all the files etc. with no error codes - is that correct?




bilbo+ said:
hello, Ive double checked and triple checked and it still doesnt work -
however i did notice that if I click on the zexportquery that it has
created
(and not deleted since it didnt ifnish running) there is no sign of
grouptable ID, just a lot of field names that are in fact table names...

If i explain what Ive done a bit more maybe it might help..

I have one table called CustomersNEW which has 8 fields in it and one
look
up field, called GroupNameID. This looks up a table called GroupName
Table,
which has two field an ID field(autonumber) and a GroupName field.

Thats pretty much all thats relevant I think!

Thanks,

Will

Douglas J. Steele said:
That very misleading error message often indicates that you've made a
mistake in your DLookup statement: that the table name isn't actually
GroupNameTable, that either GroupName or GroupNameID isn't an actual
field
name, that rstMgr!GroupNameID doesn't actually contain a value, or that
GroupNameID is a text field, not a numeric field (in which case you
need
quotes around the value: "[GroupNameID] = '" & rstMgr!GroupNameID.Value
&
"'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have some code which im trying to use to export multiple temporary
queries
to multiple files in excel, however I'm having trouble with this
section -

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

It keeps coming up with the error - 2001 runtime error - you
cancelled the
operation. I've spent hours trying to fix it but to no avail! Any
ideas
anyone?

Full 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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top