469,266 Members | 1,938 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

Run-time Error 2001 you canceled the previous operation

I have been trying to figure this out for a couple weeks and after reading every post in the forum about the subject I have not found anything that works.
I have two tables;
1) has company numbers/cost centers, hierarchys, markets, and regions
2) the main table to which the information goes to.

I know it has something to do with the cost centers beacuse when I debug, everything else works.

My code is as follows;
Expand|Select|Wrap|Line Numbers
  1.  Private Sub Co_Cost_Center_AfterUpdate()
  2.       Me.Hierarchy = DLookup("Hierarchy", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'")
  3.       Me.Market = DLookup("Market", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'")
  4.       Me.Region = DLookup("Region", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'")
  5. End Sub 
When I go to tab out of the Co/cost ctr field it gives me the runtime error 2001 you canceled the previous operation.

Any ideas?
Apr 10 '07 #1
20 19725
pks00
280 Expert 100+
I take it the co_cost_ctr is alphanumeric and not just numeric

Another thing, DLOOKUP returns null if no records found. What are the types for the 3 fields you are setting? U could wrap DLOOKUP with NZ and supply a default value
eg

Me.Region = NZ(DLookup("Region", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'"),"")


One more thing to try, your DB is not corrupt right. Ive heard before a corrupt db causing that error. Rare but has happened, so u could try a compact/repair or a decompile (always backup db first, stick with original if it makes no difference)
Apr 10 '07 #2
I take it the co_cost_ctr is alphanumeric and not just numeric

Another thing, DLOOKUP returns null if no records found. What are the types for the 3 fields you are setting? U could wrap DLOOKUP with NZ and supply a default value
eg

Me.Region = NZ(DLookup("Region", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'"),"")


One more thing to try, your DB is not corrupt right. Ive heard before a corrupt db causing that error. Rare but has happened, so u could try a compact/repair or a decompile (always backup db first, stick with original if it makes no difference)
The co/cost center is alphanumeric. Im not sure I understand what you ean by the types for the three fields I am setting. They are all text fields if that is what you mean.

I tried compacting/repairing the database and it did nothing.

I will try the NZ thing but Im not sure what that will do.

I know it has something to do with the Co/cost ctr because that is where it gets stuck. Im fairly new with Access so I appologize for not understanding some of the things you are saying.
Apr 10 '07 #3
pks00
280 Expert 100+
No problems

Now when u perform a DLOOKUP, it is one fetch, if you are doing multiple DLOOKUPS using the same criteria, its slow (though u may not notice) so its better to use a recordset
eg

Expand|Select|Wrap|Line Numbers
  1. Private Sub Co_Cost_Center_AfterUpdate()
  2.     Dim rs as DAO.Recordset
  3.  
  4.     'Check if cost center specified
  5.     if IsNull(Me.Co_Cost_Center) = True then
  6.          msgbox "No Cost Center Found"
  7.     else
  8.         set rs=currentdb.openrecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'"
  9.  
  10.         'Check if cost center exists in db
  11.         if rs.eof = true then
  12.             msgbox "No records found for cost center " & Me.Co_Cost_Center
  13.         else
  14.             Me.Hierarchy = rs!Hierarchy
  15.             Me.Market = rs!Market
  16.             Me.Region = rs!Region
  17.         end if
  18.  
  19.         rs.close
  20.         set rs=nothing
  21. End Sub
  22.  

Does the above code make any sense to you?
Apr 10 '07 #4
pks00
280 Expert 100+
If it complains about

DAO.Recordset

ensure u have added Microsoft DAO Object Library v3.6
u do this by going into Tools/References (from vba window)
Apr 10 '07 #5
If it complains about

DAO.Recordset

ensure u have added Microsoft DAO Object Library v3.6
u do this by going into Tools/References (from vba window)
I put that code in but it gave me a syntax error on this line
Expand|Select|Wrap|Line Numbers
  1.         set rs=currentdb.openrecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'"
Am I supposed to change something?
Apr 10 '07 #6
pks00
280 Expert 100+
Sorry, forgot closing round bracket
air code typing


Set rs = CurrentDb.OpenRecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'")
Apr 10 '07 #7
Sorry, forgot closing round bracket
air code typing


Set rs = CurrentDb.OpenRecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'")
Ok so I fixed that and now it gives me run-time error 3061 too few parameters. Expected 1.

What does that mean?
Apr 10 '07 #8
pks00
280 Expert 100+
Ok, here is updated code, can u run it and check the immediate window
Ive added a debug.print, check the SQL that comes up, does it look valid?
also do a debug/compile as well




Private Sub Co_Cost_Center_AfterUpdate()

Dim rs As DAO.Recordset
Dim sSql As String


'Check if cost center specified
If IsNull(Me.co_cost_center) = True Then
MsgBox "No Cost Center Found"
Else
Debug.Print sSql
Set rs = CurrentDb.OpenRecordset(sSql)

'Check if cost center exists in db
If rs.EOF = True Then
MsgBox "No records found for cost center " & Me.co_cost_center
Else
Me.Hierarchy = rs!Hierarchy
Me.Market = rs!Market
Me.Region = rs!Region
End If

rs.Close
Set rs = Nothing
End If
End Sub
Apr 10 '07 #9
Ok, here is updated code, can u run it and check the immediate window
Ive added a debug.print, check the SQL that comes up, does it look valid?
also do a debug/compile as well




Private Sub Co_Cost_Center_AfterUpdate()

Dim rs As DAO.Recordset
Dim sSql As String


'Check if cost center specified
If IsNull(Me.co_cost_center) = True Then
MsgBox "No Cost Center Found"
Else
Debug.Print sSql
Set rs = CurrentDb.OpenRecordset(sSql)

'Check if cost center exists in db
If rs.EOF = True Then
MsgBox "No records found for cost center " & Me.co_cost_center
Else
Me.Hierarchy = rs!Hierarchy
Me.Market = rs!Market
Me.Region = rs!Region
End If

rs.Close
Set rs = Nothing
End If
End Sub

Now it says
Run-time error 3078
The Microsoft Jet database engine cannot find the input table or query". Make sure it exists and that its name is spelled correctly.

It points to this line
Set rs = CurrentDb.OpenRecordset(sSql)
Apr 10 '07 #10
pks00
280 Expert 100+
oh bugger, my bad. I forgot to set sSql

before debug.print sSql, add this line

sSql = "SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.co_cost_center & "'"
Apr 10 '07 #11
oh bugger, my bad. I forgot to set sSql

before debug.print sSql, add this line

sSql = "SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.co_cost_center & "'"
The too few paramaters error comes up again.
In the code it highlights
Set rs = CurrentDb.OpenRecordset(sSql)
and in the immediate window it shows
SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '172/0009668'
Apr 10 '07 #12
pks00
280 Expert 100+
when u run this sql in the sql window, does it work?

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '172/0009668'
Apr 10 '07 #13
when u run this sql in the sql window, does it work?

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '172/0009668'
No it says compile error expected:case
Apr 10 '07 #14
pks00
280 Expert 100+
lets get the sql working first

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '172/0009668'

is this the table u are reading CostCtr/Hierarchy
does it have a field called Co_Cost_Center

the error is probably due to the sql not working
Apr 10 '07 #15
lets get the sql working first

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '172/0009668'

is this the table u are reading CostCtr/Hierarchy
does it have a field called Co_Cost_Center

the error is probably due to the sql not working
In that table it is actually Co_Cost_Ctr
Apr 10 '07 #16
pks00
280 Expert 100+
So the query should be?

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Ctr = '172/0009668'

if the above sql works, then change the code as well and try it
Apr 10 '07 #17
So the query should be?

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Ctr = '172/0009668'

if the above sql works, then change the code as well and try it
I already tried that and it gives me the same error.
Apr 10 '07 #18
pks00
280 Expert 100+
Did the SQL work?

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Ctr = '172/0009668'
Apr 10 '07 #19
Did the SQL work?

SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Ctr = '172/0009668'
No it gave me the same error as before.
Apr 10 '07 #20
pks00
280 Expert 100+
can u confirm the name of the table/query is correct?

why not create a new query using the query wizard
Apr 10 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by leroybt.rm | last post: by
13 posts views Thread by Bob Day | last post: by
19 posts views Thread by Bryan | last post: by
9 posts views Thread by Brett Wesoloski | last post: by
8 posts views Thread by David Thielen | last post: by
3 posts views Thread by traceable1 | last post: by
7 posts views Thread by mxdevit | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.