Too Few Parameters - Access can't find the name 'Enter book'

  • Thread starter Thread starter Roger Carlson
  • Start date Start date
R

Roger Carlson

All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Access returns the error message:
Microsoft Access can't find the name 'Enter book' you entered in the
expression.

Any idea what is going on here? What am I missing?

BTW, I also tried removing one of the parameters ([Enter book]) and it
crashed on [Enter PubID].
I also tried adding a parameters clause:
PARAMETERS [Enter book] Text ( 255 ), [Enter pubid] Short;
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

Still no luck.
 
Well, the Eval function doesn't have a clue what to do with "[Enter Book]"
(prm.Name) - it can't find any variable or object by that name. Eval won't
prompt the user for the value - you must do that. (Maybe use InputBox?)
Then, set each parameter with the value you receive before attempting to
open a recordset on the query. What is it you expect to happen?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Roger Carlson said:
All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Access returns the error message:
Microsoft Access can't find the name 'Enter book' you entered in the
expression.

Any idea what is going on here? What am I missing?

BTW, I also tried removing one of the parameters ([Enter book]) and it
crashed on [Enter PubID].
I also tried adding a parameters clause:
PARAMETERS [Enter book] Text ( 255 ), [Enter pubid] Short;
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));
Click to expand...
Hi Roger,

Also, I think there may some kind of karma thing
going on here too.... 8-)

My life the last 3 years has been db programming
with book data. Maybe this is for one of your "simple examples"
(which is a great service you provide), but the string for a Title
can be "all over the place." A title from one vendor may use the
starting "The", where another doesn't. Some titles have extra lines
which may be included or not. Some titles get a ("translator") tacked on
because there are 4 different versions of the same title out there,
all by different translators. And so on and so on....

My point is that filtering on a book title (expecting a user
to enter the exact same title string) probably would not have
been the best technique.

In almost all my search routines, I either work with the number
portion (indexed) of the ISBN (which nowadays can mean a book with a
CD or a book w/o a CD...argh), or we search on a key made up
of the first 4 chars of the Author and first 3 chars of the Title.

Maybe this doesn't matter, but had to respond just in case.

Gary Walter
 
Roger Carlson said:
All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Click to expand...
Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
 
Hi Roger,

Neat trick from Paul Fantacci.

You can use Eval (InputBox plus a default value) as
the parameter in your query.


In place of "[Enter Book]" in your query, use

Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")


SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE
(((Books.Title)=Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")))
OR
(((Books.PubID)=Eval("InputBox(""Enter PubID"",""Find Pub"",""SYBEX"")")));

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
'no need for parameter code
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Otherwise, with "[Enter Book]"

For Each prm in qdf.Parameters
prm.Value = InputBox("Value for " & prm.Name)
Next prm

Good luck,

gary









Gary Walter said:
Roger Carlson said:
All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Click to expand...
Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
Click to expand...
 
Thank to both John and Gary.

I knew it would work with a form, but I was SURE it also worked with a plain
parameter query. I'd never actually used it that way, and I can see why.
It IS less useful than using a form for input.

Thanks for straightening me out.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
Roger Carlson said:
All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Access returns the error message:
Microsoft Access can't find the name 'Enter book' you entered in the
expression.

Any idea what is going on here? What am I missing?

BTW, I also tried removing one of the parameters ([Enter book]) and it
crashed on [Enter PubID].
I also tried adding a parameters clause:
PARAMETERS [Enter book] Text ( 255 ), [Enter pubid] Short;
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));
Click to expand...
Hi Roger,

Also, I think there may some kind of karma thing
going on here too.... 8-)

My life the last 3 years has been db programming
with book data. Maybe this is for one of your "simple examples"
(which is a great service you provide), but the string for a Title
can be "all over the place." A title from one vendor may use the
starting "The", where another doesn't. Some titles have extra lines
which may be included or not. Some titles get a ("translator") tacked on
because there are 4 different versions of the same title out there,
all by different translators. And so on and so on....

My point is that filtering on a book title (expecting a user
to enter the exact same title string) probably would not have
been the best technique.

In almost all my search routines, I either work with the number
portion (indexed) of the ISBN (which nowadays can mean a book with a
CD or a book w/o a CD...argh), or we search on a key made up
of the first 4 chars of the Author and first 3 chars of the Title.

Maybe this doesn't matter, but had to respond just in case.

Gary Walter
Click to expand...
 
This is interesting. Thanks.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
Hi Roger,

Neat trick from Paul Fantacci.

You can use Eval (InputBox plus a default value) as
the parameter in your query.


In place of "[Enter Book]" in your query, use

Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")


SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE
(((Books.Title)=Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")))
OR
(((Books.PubID)=Eval("InputBox(""Enter PubID"",""Find Pub"",""SYBEX"")")));

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
'no need for parameter code
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Otherwise, with "[Enter Book]"

For Each prm in qdf.Parameters
prm.Value = InputBox("Value for " & prm.Name)
Next prm

Good luck,

gary









Gary Walter said:
Roger Carlson said:
All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Click to expand...
Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
Click to expand...
Click to expand...
 
Hi Roger,

Actually.. looking back I wish I had
never brought up the "neat trick."

Eval expects an expression that results
in a string or a number. So dates get
complicated. Plus...all the quotes are probably
just setting you up for some user entering something
with a single or double quote that blows it up,
which all could be overcome, but why?

While I too would use a form, if I
*had* to do it this way I would
assign parameter(s) to a variant(s)
with InputBox (where one could
use a default there) at the top of code,
then varify it (them) and possibly massage
the input, then use the var(s) in loop.

varParameter = InputBox(...)

For Each prm in qdf.Parameters
prm.Value = varParameter
Next prm

Oh well,

gary

Roger Carlson said:
This is interesting. Thanks.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
Hi Roger,

Neat trick from Paul Fantacci.

You can use Eval (InputBox plus a default value) as
the parameter in your query.


In place of "[Enter Book]" in your query, use

Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")


SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE
(((Books.Title)=Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")))
OR
(((Books.PubID)=Eval("InputBox(""Enter PubID"",""Find Pub"",""SYBEX"")")));

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
'no need for parameter code
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Otherwise, with "[Enter Book]"

For Each prm in qdf.Parameters
prm.Value = InputBox("Value for " & prm.Name)
Next prm

Good luck,

gary









Gary Walter said:
All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
Click to expand...
Click to expand...
Click to expand...
 
some people verify their input data.....
I varify it. 8-)

Gary Walter said:
Hi Roger,

Actually.. looking back I wish I had
never brought up the "neat trick."

Eval expects an expression that results
in a string or a number. So dates get
complicated. Plus...all the quotes are probably
just setting you up for some user entering something
with a single or double quote that blows it up,
which all could be overcome, but why?

While I too would use a form, if I
*had* to do it this way I would
assign parameter(s) to a variant(s)
with InputBox (where one could
use a default there) at the top of code,
then varify it (them) and possibly massage
the input, then use the var(s) in loop.

varParameter = InputBox(...)

For Each prm in qdf.Parameters
prm.Value = varParameter
Next prm

Oh well,

gary

Roger Carlson said:
This is interesting. Thanks.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
Hi Roger,

Neat trick from Paul Fantacci.

You can use Eval (InputBox plus a default value) as
the parameter in your query.


In place of "[Enter Book]" in your query, use

Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")


SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE
(((Books.Title)=Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")))
OR
(((Books.PubID)=Eval("InputBox(""Enter PubID"",""Find Pub"",""SYBEX"")")));

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
'no need for parameter code
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Otherwise, with "[Enter Book]"

For Each prm in qdf.Parameters
prm.Value = InputBox("Value for " & prm.Name)
Next prm

Good luck,

gary










All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
Click to expand...
Click to expand...
Click to expand...
 
I've got a great sample for "varifying" data called "DataScramble.mdb"
<grin>

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
some people verify their input data.....
I varify it. 8-)

Gary Walter said:
Hi Roger,

Actually.. looking back I wish I had
never brought up the "neat trick."

Eval expects an expression that results
in a string or a number. So dates get
complicated. Plus...all the quotes are probably
just setting you up for some user entering something
with a single or double quote that blows it up,
which all could be overcome, but why?

While I too would use a form, if I
*had* to do it this way I would
assign parameter(s) to a variant(s)
with InputBox (where one could
use a default there) at the top of code,
then varify it (them) and possibly massage
the input, then use the var(s) in loop.

varParameter = InputBox(...)

For Each prm in qdf.Parameters
prm.Value = varParameter
Next prm

Oh well,

gary

Roger Carlson said:
This is interesting. Thanks.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi Roger,

Neat trick from Paul Fantacci.

You can use Eval (InputBox plus a default value) as
the parameter in your query.


In place of "[Enter Book]" in your query, use

Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")


SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE
(((Books.Title)=Eval("InputBox(""Enter Title"",""Find This
Title"",""VBA"")")))
OR
(((Books.PubID)=Eval("InputBox(""Enter PubID"",""Find
Pub"",""SYBEX"")")));

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
'no need for parameter code
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Otherwise, with "[Enter Book]"

For Each prm in qdf.Parameters
prm.Value = InputBox("Value for " & prm.Name)
Next prm

Good luck,

gary










All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter
PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
Click to expand...
Click to expand...
Click to expand...
 
I agree, it's not all that useful, but it IS an interesting curiosity.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
Hi Roger,

Actually.. looking back I wish I had
never brought up the "neat trick."

Eval expects an expression that results
in a string or a number. So dates get
complicated. Plus...all the quotes are probably
just setting you up for some user entering something
with a single or double quote that blows it up,
which all could be overcome, but why?

While I too would use a form, if I
*had* to do it this way I would
assign parameter(s) to a variant(s)
with InputBox (where one could
use a default there) at the top of code,
then varify it (them) and possibly massage
the input, then use the var(s) in loop.

varParameter = InputBox(...)

For Each prm in qdf.Parameters
prm.Value = varParameter
Next prm

Oh well,

gary

Roger Carlson said:
This is interesting. Thanks.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Gary Walter said:
Hi Roger,

Neat trick from Paul Fantacci.

You can use Eval (InputBox plus a default value) as
the parameter in your query.


In place of "[Enter Book]" in your query, use

Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")


SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE
(((Books.Title)=Eval("InputBox(""Enter Title"",""Find This Title"",""VBA"")")))
OR
(((Books.PubID)=Eval("InputBox(""Enter PubID"",""Find Pub"",""SYBEX"")")));

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
'no need for parameter code
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Otherwise, with "[Enter Book]"

For Each prm in qdf.Parameters
prm.Value = InputBox("Value for " & prm.Name)
Next prm

Good luck,

gary










All right! This one is driving me nuts!

I have this query (Query2):
SELECT Books.ISBN, Books.Title, Books.PubID, Books.Price
FROM Books
WHERE (((Books.Title)=[Enter book])) OR (((Books.PubID)=[Enter PubID]));

I want to open it in code and because it has parameters, I use the
following:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("Query2")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)

Hi Roger,

This code *will work* if parameter is
a control on an open form.....
maybe that's why you thought it should work?

gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top