Prev: how to refer to all rows in a list box
Next: Changing total on one row in Continous Form
From: toby131 on 7 Apr 2010 15:10
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] & " ' ")
From: toby131 on 7 Apr 2010 15:15
> 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
From: Daryl S on 8 Apr 2010 11:14
Toby131 -

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

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

If FirstID is a string, then try this:

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


--
Daryl S


"toby131" wrote:

> > 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
From: toby131 on 8 Apr 2010 12:36
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!

"Daryl S" wrote:

> Toby131 -
>
> The FirstID needs to be evaulated outside of double-quotes. If FirstID is a
> number, then try this:
>
> SecondID = DLookup("[ID]", "Part No for ID", "[ID]<> " & FirstID & " And
> [Part No] =
> > ' " & Me.[Part No] & " ' ")
>
> If FirstID is a string, then try this:
>
> SecondID = DLookup("[ID]", "Part No for ID", "[ID]<> '" & FirstID & "' And
> [Part No] =
> > ' " & Me.[Part No] & " ' ")
>
>
> --
> Daryl S
>
>
> "toby131" wrote:
>
> > > 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
From: Tom Lake on 8 Apr 2010 14:16

"toby131" <toby131(a)discussions.microsoft.com> wrote in message
news:9623E508-81C3-476A-88A3-CFCF9CB31C21(a)microsoft.com...
> 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

 |  Next  |  Last
Pages: 1 2
Prev: how to refer to all rows in a list box
Next: Changing total on one row in Continous Form