DLookup with mulitple criteria - error message

[toby131]

I get a "Run-time error '2001': You canceled the previous operation" on the
second line of my code. If I remove this line of code, everything works
fine. I'm trying to locally retrieve all the ID numbers where the part
numbers are the same to be displayed in a pop up message in this afterupdate
event. Any ideas why I am getting this error message? Thanks!

Private Sub Part_No_AfterUpdate()

FirstID = DLookup("[ID]", "Part No for ID", "[Part No]= ' " & Me.[Part No] &
" ' ")

SecondID = DLookup("[ID]", "Part No for ID", "[ID]<> FirstID And [Part No] =
' " & Me.[Part No] & " ' ")

 

[toby131]

I get a "Run-time error '2001': You canceled the previous operation" on the

second line of my code. If I remove this line of code, everything works
fine. I'm trying to locally retrieve all the ID numbers where the part
numbers are the same to be displayed in a pop up message in this afterupdate
event. Any ideas why I am getting this error message? Thanks!

Private Sub Part_No_AfterUpdate()

FirstID = DLookup("[ID]", "Part No for ID", "[Part No]= ' " & Me.[Part No] &
" ' ")

SecondID = DLookup("[ID]", "Part No for ID", "[ID]<> FirstID And [Part No] =
' " & Me.[Part No] & " ' ")

Forgot to include:
ID: Number field
Part No for ID: Table Where ID & Part No are stored
Part No: Text field

 

[Daryl S]

Toby131 -

The FirstID needs to be evaulated outside of double-quotes. If FirstID is a
number, then try this:

' " & Me.[Part No] & " ' ")

If FirstID is a string, then try this:

' " & Me.[Part No] & " ' ")

--
Daryl S

I get a "Run-time error '2001': You canceled the previous operation" on the
second line of my code. If I remove this line of code, everything works
fine. I'm trying to locally retrieve all the ID numbers where the part
numbers are the same to be displayed in a pop up message in this afterupdate
event. Any ideas why I am getting this error message? Thanks!

Private Sub Part_No_AfterUpdate()

FirstID = DLookup("[ID]", "Part No for ID", "[Part No]= ' " & Me.[Part No] &
" ' ")

SecondID = DLookup("[ID]", "Part No for ID", "[ID]<> FirstID And [Part No] =
' " & Me.[Part No] & " ' ")

Forgot to include:
ID: Number field
Part No for ID: Table Where ID & Part No are stored
Part No: Text field

 

[toby131]

Thank you, that fixed the problem. Would you be able to explain to me the
use of the quotes and "&"s in this context or point me to a reference so I
can better understand? Thanks again for your help!

Toby131 -

The FirstID needs to be evaulated outside of double-quotes. If FirstID is a
number, then try this:

' " & Me.[Part No] & " ' ")

If FirstID is a string, then try this:

' " & Me.[Part No] & " ' ")

--
Daryl S

I get a "Run-time error '2001': You canceled the previous operation" on the
second line of my code. If I remove this line of code, everything works
fine. I'm trying to locally retrieve all the ID numbers where the part
numbers are the same to be displayed in a pop up message in this afterupdate
event. Any ideas why I am getting this error message? Thanks!

Private Sub Part_No_AfterUpdate()

FirstID = DLookup("[ID]", "Part No for ID", "[Part No]= ' " & Me.[Part No] &
" ' ")

SecondID = DLookup("[ID]", "Part No for ID", "[ID]<> FirstID And [Part No] =
' " & Me.[Part No] & " ' ")

Forgot to include:
ID: Number field
Part No for ID: Table Where ID & Part No are stored
Part No: Text field

 

[Tom Lake]

Thank you, that fixed the problem. Would you be able to explain to me the
use of the quotes and "&"s in this context or point me to a reference so I
can better understand? Thanks again for your help!

That's how BASIC works.
The criteria string has to be a legal BASIC string.
Here's an old-style BASIC program to illustrate:

FirstID$ = "413456"
PartNo$ = "345667"

PRINT "Output:"
PRINT
Criterion1$= """[ID]<>FirstID AND [PartNo] = [PartNo]"""
Criterion2$ = """[ID] <>'" & FirstID$ & "' AND [PartNo]='" & PartNo$ & "'"""
PRINT Criterion1$
PRINT
PRINT Criterion2$
PRINT
PRINT "End Program."
run
Output:

"[ID]<>FirstID AND [PartNo] = [PartNo]"

"[ID]<>'413456' AND [PartNo]='345667'"

End Program.


See the difference? In the first one, FirstID and PartNo are
part of the string constant. In the second one, they're string
variables and the *values* of FirstID$ and PartNo$ are inserted
into the criterion instead.

Tom Lake

 

[Stuart McCall]

Thank you, that fixed the problem. Would you be able to explain to me
the
use of the quotes and "&"s in this context or point me to a reference so
I
can better understand? Thanks again for your help!

That's how BASIC works.
The criteria string has to be a legal BASIC string.
Here's an old-style BASIC program to illustrate:

FirstID$ = "413456"
PartNo$ = "345667"

PRINT "Output:"
PRINT
Criterion1$= """[ID]<>FirstID AND [PartNo] = [PartNo]"""
Criterion2$ = """[ID] <>'" & FirstID$ & "' AND [PartNo]='" & PartNo$ &
"'"""
PRINT Criterion1$
PRINT
PRINT Criterion2$
PRINT
PRINT "End Program."
run
Output:

"[ID]<>FirstID AND [PartNo] = [PartNo]"

"[ID]<>'413456' AND [PartNo]='345667'"

End Program.


See the difference? In the first one, FirstID and PartNo are
part of the string constant. In the second one, they're string
variables and the *values* of FirstID$ and PartNo$ are inserted
into the criterion instead.

Tom Lake

I think that's the best illustration of the concept I've seen. <applause>

The only snag (there's always one is that type-declaration-chars will be
confusing, because most folks on here (minus the MVPs of course) have never
run across them.