Problem using DLookup

[Rhughes53]

I am having trouble getting Dlookup to run. I have used it in the past without any problems but in this database I keep getting a runtime error 2001 You Canceled the Previous Operation. The code I'm using is

ME![ECN] = DLookup("[ECNNBR]", "tbl MFDI", "[Part Number] = Forms![ABM KB SETUP SHEET]![PART #]")

I am trying to use this Event Procedure at the Lost Focus on [ABM KB SETUP SHEET]![PART #] and [ECNNBR] is a field in the table tbl MFDI. Anybody got any idea's what is wrong. I do not have any missing references and the references I have selected are Microsoft DAO 3.6 Object Library, Microsoft ActiveX Data Object 2.5 Library, OLE Automation, Visual Basic for Application and Microsoft Access 11.0 Object Library. Should I have anything else selected?

 

[SOS]

You just have something in the wrong place:

ME![ECN] = DLookup("[ECNNBR]", "tbl MFDI", "[Part Number] = " & Forms![ABM KB SETUP SHEET]![PART #])

 

[SOS]

And I would highly suggest not using spaces or special characters in your object and field names. Things like # have special meaning within Access and can trip you up as well if you use them.

 

[Rhughes53]

Thanks, and I know about the spaces and special characters, but this database is one I took over from someone else, there has been so much delevelopment on it I was afriad I'd miss something if I started over and renamed things, but maybe I should anyway. Thanks for the reminder. I will try your suggestion on the Dlookup, but you know I've never had to use the *" & * in the criteria before (course I haven't used Dlookup for a very long time) has something changed?

 

[Rhughes53]

I tried using the " & Forms![ABM KB SETUP SHEET]![Part #]) and it still said "You canceled the previous operation" Run Time Error 2001".

So I thought maybe it's the special character So I changed Form Name to Part and the code to read

Dim strECN As String, strItemid As String
strItemid = Forms![ABM KB SETUP SHEET]![PART]
strECN = DLookup("[ECNNBR]", "tbl MFDI", "[Part Number] = " & strItemid)
Me![ECN] = ECN
I also tried strECN = DLookup("[ECNNBR]", "tbl MFDI", "[Part Number] = " & "strItemid")

And I still get the same error. Any Idea's what I can do?

 

[gemma-the-husky]

is the part number a NUMBER, or a STRING? - it makes a difference

try this

strECN = DLookup("[ECNNBR]", "[tbl MFDI]", "[Part Number] = " & chr(34) & strItemid & chr(34))

and double check that your table is tbl MFDI with a space
and the field name is part number with a space


indeed try

on error goto fail
strECN = DLookup("[ECNNBR]", "[tbl MFDI]", "[Part Number] = " & chr(34) & strItemid & chr(34))
exit sub

fail:
call msgbox("Error: " & err & "  Desc: " & err.description)

this might give you more information about why the code is failing