Run-time Error 2001 you canceled the previous operation

[Crayola465]

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;

 Private Sub Co_Cost_Center_AfterUpdate()
      Me.Hierarchy = DLookup("Hierarchy", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'")
      Me.Market = DLookup("Market", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'")
      Me.Region = DLookup("Region", "[CostCtr/Hierarchy]", "[Co_Cost_Ctr] = '" & Me.[Co_Cost_Center] & "'")
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?

[pks00]

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)

[Crayola465]

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.

[pks00]

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

Private Sub Co_Cost_Center_AfterUpdate()
    Dim rs as DAO.Recordset
 
    'Check if cost center specified
    if IsNull(Me.Co_Cost_Center) = True then
         msgbox "No Cost Center Found"
    else
        set rs=currentdb.openrecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'"
 
        '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 Sub
 

Does the above code make any sense to you?

[pks00]

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)

[Crayola465]

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

        set rs=currentdb.openrecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'"

Am I supposed to change something?

[pks00]

Sorry, forgot closing round bracket
air code typing


Set rs = CurrentDb.OpenRecordset("SELECT * FROM [CostCtr/Hierarchy] WHERE Co_Cost_Center = '" & Me.Co_Cost_Center & "'")

[Crayola465]

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?

[pks00]

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

[Crayola465]

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)

[pks00]

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 & "'"

[Crayola465]

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'

[pks00]

when u run this sql in the sql window, does it work?

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

[Crayola465]

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

[pks00]

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

[Crayola465]

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

[pks00]

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

[Crayola465]

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.

[pks00]

Did the SQL work?

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

[Crayola465]

Did the SQL work?

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

No it gave me the same error as before.

[pks00]

can u confirm the name of the table/query is correct?

why not create a new query using the query wizard