help parameters access 2003

[Guest]

I am setting up an access 2003 database at work. I need to set up one of my
querries with 100 parameters
EX: ([product id 1] Or [product id2] Or ..........up to [product id 100]
However with this expression the user must enter 100 values or press enter
the remaining amount of parameters. I understand I can create 10 different
querries each with a differnt amount of parametersin each but I feel this
would be confusing to users. I would like Access to ask "is there any more
product ids to enter" . Can I do this ? If so How

Thank-You
testtech2522

 

[Guest]

May I suggest another approach? They way you are doing it will be difficult,
because every parameter has to be entered. You could use a list box and let
them select the products they want, then use some VBA to write an SQL
statment that would return the values, but that would be some fairly advanced
VBA, so I have an idea. I will lay it out assuming you are in a multiuser
environment.
create a table that has two fields, username and product id
When the user is ready to run this routine:
delete the records in this table where the user id = the current user
make a query based on this table the recordsource for your form or subform,
but don't show the username.
every time the user enters a product id, do a dlookup on what they entered
to ensure it is a valid product. If it is not, prompt them and cancel the
insert. If it is, populate the userid field with their user id.
Have a command button on the form for the user to indicate they are done
entering products.
When they click on this button, then it is time to run your query.
Now, the query you are using now will need some modification. You will have
to add this new table to the query, join it on product id, and select option
1 in the join properties so only those rows that match will be returned. The
one other thing you will need to do is filter the query on the current user
id.

Good Luck.

 

[Larry Daugherty]

Hi,

I read Klatuu's response and agree with his sympathies but not his
conclusion and suggested conclusion. Since this is not yet an
operational database, better to suffer the extra learning and delay to
do it correctly. By correctly, I mean incredibly more easy to USE and
significantly LESS ERROR PRONE. It is much better that your users
complete their tasks by selecting and clicking rather than entering
data.

I'm predisposed to think in terms of a multiselect list box or a
treeview control. The implementation of either of them is a
non-trivial task.

But, like Klatuu, I'm getting the cart before the horse ...

What is the real world problem you are solving? What is the
application you are designing and what are the strategies you plan to
use?

Please answer the above questions first and then describe your current
design.

Give us the schema if you can. Table and field names and types, etc.

HTH
--
-Larry-
--

May I suggest another approach? They way you are doing it will be difficult,
because every parameter has to be entered. You could use a list box and let
them select the products they want, then use some VBA to write an SQL
statement that would return the values, but that would be some fairly advanced
VBA, so I have an idea. I will lay it out assuming you are in a multiuser
environment.
create a table that has two fields, username and product id
When the user is ready to run this routine:
delete the records in this table where the user id = the current user
make a query based on this table the recordsource for your form or subform,
but don't show the username.
every time the user enters a product id, do a dlookup on what they entered
to ensure it is a valid product. If it is not, prompt them and cancel the
insert. If it is, populate the userid field with their user id.
Have a command button on the form for the user to indicate they are done
entering products.
When they click on this button, then it is time to run your query.
Now, the query you are using now will need some modification. You will have
to add this new table to the query, join it on product id, and select option
1 in the join properties so only those rows that match will be returned. The
one other thing you will need to do is filter the query on the current user
id.

Good Luck.

I am setting up an access 2003 database at work. I need to set up one of my
querries with 100 parameters
EX: ([product id 1] Or [product id2] Or ..........up to [product id 100]
However with this expression the user must enter 100 values or press enter
the remaining amount of parameters. I understand I can create 10 different
querries each with a differnt amount of parametersin each but I feel this
would be confusing to users. I would like Access to ask "is there any more
product ids to enter" . Can I do this ? If so How

Thank-You
testtech2522

 

[Guest]

Larry,
I think we agree in concept, but perhaps not technique.
I would personally use a multi select list box, and build Where condition
from it; however, this technique takes some pretty advanced string
manipulation. I have one form that drives a complex report selector that has
six multi select list boxes and I build a where condition based on all of
them.

I don't see why you think it would be more error prone, other than the user
entering incorrect numbers. That can be solved a couple of different ways.
As to the Treeview, I avoid ActiveX controls whenever possible. In my years
of experience, they are more trouble than they are worth.

As to the correct way... This is not a case where you can say one way is
correct and another is not, without a hint of arrogance. There are some
issues regarding database design, etc, where correct is more of an absolute.
I will agree that some techniques are pretty bad. For example, there was a
post the other day where someone was putting multiple addresses into one memo
field and wanted to know how to parse them out. That is incorrect. The
solution I proposed in not incorrect. It is, as I previously stated, not the
best. It is a solution I think is not that complese and could be implemented
with a minimum of coding.

Now, if the OP would like to consider using a multi select list box, I will
be happy to share the code I have that does that.

The reason I proposed the solution I did, is that I am not confident the OP
would be able to code something that complex. (Please, no offense to you,
OP). Although the solution I propose is not that elegant, it is not the
difficult for someone of even modest skill to understand and implement.

Hi,

I read Klatuu's response and agree with his sympathies but not his
conclusion and suggested conclusion. Since this is not yet an
operational database, better to suffer the extra learning and delay to
do it correctly. By correctly, I mean incredibly more easy to USE and
significantly LESS ERROR PRONE. It is much better that your users
complete their tasks by selecting and clicking rather than entering
data.

I'm predisposed to think in terms of a multiselect list box or a
treeview control. The implementation of either of them is a
non-trivial task.

But, like Klatuu, I'm getting the cart before the horse ...

What is the real world problem you are solving? What is the
application you are designing and what are the strategies you plan to
use?

Please answer the above questions first and then describe your current
design.

Give us the schema if you can. Table and field names and types, etc.

HTH
--
-Larry-
--

May I suggest another approach? They way you are doing it will be difficult,
because every parameter has to be entered. You could use a list box and let
them select the products they want, then use some VBA to write an SQL
statement that would return the values, but that would be some fairly advanced
VBA, so I have an idea. I will lay it out assuming you are in a multiuser
environment.
create a table that has two fields, username and product id
When the user is ready to run this routine:
delete the records in this table where the user id = the current user
make a query based on this table the recordsource for your form or subform,
but don't show the username.
every time the user enters a product id, do a dlookup on what they entered
to ensure it is a valid product. If it is not, prompt them and cancel the
insert. If it is, populate the userid field with their user id.
Have a command button on the form for the user to indicate they are done
entering products.
When they click on this button, then it is time to run your query.
Now, the query you are using now will need some modification. You will have
to add this new table to the query, join it on product id, and select option
1 in the join properties so only those rows that match will be returned. The
one other thing you will need to do is filter the query on the current user
id.

Good Luck.

I am setting up an access 2003 database at work. I need to set up one of my
querries with 100 parameters
EX: ([product id 1] Or [product id2] Or ..........up to [product id 100]
However with this expression the user must enter 100 values or press enter
the remaining amount of parameters. I understand I can create 10 different
querries each with a differnt amount of parametersin each but I feel this
would be confusing to users. I would like Access to ask "is there any more
product ids to enter" . Can I do this ? If so How

Thank-You
testtech2522

 

[Tim Ferguson]

I am setting up an access 2003 database at work. I need to set up one
of my querries with 100 parameters
EX: ([product id 1] Or [product id2] Or ..........up to [product id
100]

As indicated down thread, we don't really know what this query is meant to
be solving; but I am guessing that this particular bit would be best
answered with a temporary table. More information?

Tim F

 

[Larry Daugherty]

Klatuu,

I understood you throughout your earlier post and in this last one.
User entering numbers is always more error prone than user selecting
from lists. The disparity is even greater between user entering
numbers versus user selecting words from list. Depending on
application context pointing and clicking is generally quicker than
typing. There are admitted exceptions. WordPerfect users got some
pretty complex strings of hotkeys wired to their backbones. They
whizzed through their work like court reporters. I used to be that
way with TECO, DEC's early and incredibly powerful editor.

Pointing devices and enhanced user interfaces didn't catch on easily
because they were perceived as "just eye candy and toys and of no
business worth". Had their worth never been proven in enhanced
throughput and accuracy as well, we'd still be using simple character
oriented interfaces with hot key command modes and the like.

I know these things and I believe that you do. Along the way we all
make different value judgements. In the context of these newsgroups,
as in my career, I see things that deliver greater value to the end
user as being more correct than those that deliver less. My efforts
are bent toward that end and I encourage the developers who ask for
help and guidance to deliver along those same lines. I don't
encourage mediocrity or settling for less.

I've just spent the last several hours dealing with a very similar
situation. I wrote up a design for part of an application for a
poster in gettingstarted. First I almost passed him by. Then
suggested he take a shallow, mellow path with easy code but ugly user
interface and that he later go back and to it over with a multi-select
list box or Treeview. He posted back for help on the easy code & I
was hooked. I ended up designing a form/subform solution that's not
too elegant but that will sure open his eyes to some possibilities.
If he digs in and does it as I've laid it out for him he will have
solved his problem very well. I was tired then, more tired now.

Believe me, my conclusions about your conclusions were not a personal
attack on you and only apply to this present thread.

HTH
--
-Larry-
--

Larry,
I think we agree in concept, but perhaps not technique.
I would personally use a multi select list box, and build Where condition
from it; however, this technique takes some pretty advanced string
manipulation. I have one form that drives a complex report selector that has
six multi select list boxes and I build a where condition based on all of
them.

I don't see why you think it would be more error prone, other than the user
entering incorrect numbers. That can be solved a couple of different ways.
As to the Treeview, I avoid ActiveX controls whenever possible. In my years
of experience, they are more trouble than they are worth.

As to the correct way... This is not a case where you can say one way is
correct and another is not, without a hint of arrogance. There are some
issues regarding database design, etc, where correct is more of an absolute.
I will agree that some techniques are pretty bad. For example, there was a
post the other day where someone was putting multiple addresses into one memo
field and wanted to know how to parse them out. That is incorrect. The
solution I proposed in not incorrect. It is, as I previously stated, not the
best. It is a solution I think is not that complese and could be implemented
with a minimum of coding.

Now, if the OP would like to consider using a multi select list box, I will
be happy to share the code I have that does that.

The reason I proposed the solution I did, is that I am not confident the OP
would be able to code something that complex. (Please, no offense to you,
OP). Although the solution I propose is not that elegant, it is not the
difficult for someone of even modest skill to understand and implement.

Hi,

I read Klatuu's response and agree with his sympathies but not his
conclusion and suggested conclusion. Since this is not yet an
operational database, better to suffer the extra learning and delay to
do it correctly. By correctly, I mean incredibly more easy to USE and
significantly LESS ERROR PRONE. It is much better that your users
complete their tasks by selecting and clicking rather than entering
data.

I'm predisposed to think in terms of a multiselect list box or a
treeview control. The implementation of either of them is a
non-trivial task.

But, like Klatuu, I'm getting the cart before the horse ...

What is the real world problem you are solving? What is the
application you are designing and what are the strategies you plan to
use?

Please answer the above questions first and then describe your current
design.

Give us the schema if you can. Table and field names and types, etc.

HTH
--
-Larry-
--

May I suggest another approach? They way you are doing it will

be
difficult,

because every parameter has to be entered. You could use a list

box
and let

them select the products they want, then use some VBA to write

an
SQL

statement that would return the values, but that would be some fairly advanced
VBA, so I have an idea. I will lay it out assuming you are in a multiuser
environment.
create a table that has two fields, username and product id
When the user is ready to run this routine:
delete the records in this table where the user id = the current user
make a query based on this table the recordsource for your form

or
subform,

but don't show the username.
every time the user enters a product id, do a dlookup on what

they
entered

to ensure it is a valid product. If it is not, prompt them and cancel the
insert. If it is, populate the userid field with their user id.
Have a command button on the form for the user to indicate they

are
done

entering products.
When they click on this button, then it is time to run your query.
Now, the query you are using now will need some modification.

You
will have

to add this new table to the query, join it on product id, and select option
1 in the join properties so only those rows that match will be returned. The
one other thing you will need to do is filter the query on the current user
id.

Good Luck.

:

I am setting up an access 2003 database at work. I need to set

up
one of my

querries with 100 parameters
EX: ([product id 1] Or [product id2] Or ..........up to

[product
id 100]

However with this expression the user must enter 100 values or press enter
the remaining amount of parameters. I understand I can create

10
different

querries each with a differnt amount of parametersin each but

I
feel this

would be confusing to users. I would like Access to ask "is

there
any more

product ids to enter" . Can I do this ? If so How

Thank-You
testtech2522