Dropdown Selector Linked to Table Data in Query

[FIECA]

Hi,

I want to create a query that employs a dropdown to allow the user to select
information from a specific field in a specific table. The purpose is to
avoid misspelling of the input data (the value is the name of a customer),
so I want the user to be able to choose from existing data. Can anyone help
me with creating the mechanism to make the query provide a combo box
linkable to a field in a table?

Thanks,

Tom

 

[kingston via AccessMonster.com]

The query field will propagate the table field's properties so make sure the
underlying table field is a lookup field. In other words, the field in the
table must be a lookup so when you include it in a query, the query just
presents that field like a dropdown.

 

[FIECA]

So how do I make the query ask for this field. I.E. I would normally type
[enter data] underneath the field heading in the query. How would I make
the query prompt the user with the dropdown box?

Thanks for such a quick reply.

 

[John Vinson]

Can anyone help
me with creating the mechanism to make the query provide a combo box
linkable to a field in a table?

Create an unbound Form (named frmCrit, let's say) with a Combo Box
(cboPerson). Ideally this should display a human-readable name but its
Bound Column should be a unique PersonID.

In your query use a criterion

=[Forms]![frmCrit]![cboPerson]

It's handy to put a command button on frmCrit to open the Report or
Form based on your query; generally, it's not necessary or beneficial
to actually open the query datasheet itself.

John W. Vinson[MVP]

 

[kingston via AccessMonster.com]

When you construct a query, you add a field from a table. The field from the
table should be defined as a lookup field (during the design of the table).
I'm not sure I understand what you mean by "make the query prompt the user
with the dropdown box."

So how do I make the query ask for this field. I.E. I would normally type
[enter data] underneath the field heading in the query. How would I make
the query prompt the user with the dropdown box?

Thanks for such a quick reply.

The query field will propagate the table field's properties so make sure
the

[quoted text clipped - 17 lines]

 

[FIECA]

The field is already a dropdown in the main table. How can I ensure that
the query provides a dropdown box from which the user will select the data?

i.e. Normally, when getting input from a user, in the query you enter the
code [Enter X] in the criteria field when building the query. This creates
a popup for the user to enter "X" and provide data to the query, as a basis
for the records to be returned by the query.

I want the query to create a popup which provides the user the ability to
select the data from a dropdown, for the query to use as a basis for which
records need to be returned.

In other words, I need the user to be able to select from a list of
customers, and return contract records based on the customer the user
chooses from the dropdown.

I'm sorry I didn't make myself clear. I hope this clarifies what I am
trying to accomplish.

Thanks again for your help.

Tom

When you construct a query, you add a field from a table. The field from
the
table should be defined as a lookup field (during the design of the
table).
I'm not sure I understand what you mean by "make the query prompt the user
with the dropdown box."

So how do I make the query ask for this field. I.E. I would normally type
[enter data] underneath the field heading in the query. How would I make
the query prompt the user with the dropdown box?

Thanks for such a quick reply.

The query field will propagate the table field's properties so make sure
the

[quoted text clipped - 17 lines]

Tom

 

[kingston via AccessMonster.com]

AFAIK, this cannot be done with the parameter in a parameter query. You can
do it by creating a form with a combobox. So, whatever triggers the query
now will open the form instead. Once a selection is made in the form, the
combobox's AfterUpdate event would trigger the query where the parameter is
equal to the selection. I didn't realize that you were referring to the pop
up for a parameter query.

The field is already a dropdown in the main table. How can I ensure that
the query provides a dropdown box from which the user will select the data?

i.e. Normally, when getting input from a user, in the query you enter the
code [Enter X] in the criteria field when building the query. This creates
a popup for the user to enter "X" and provide data to the query, as a basis
for the records to be returned by the query.

I want the query to create a popup which provides the user the ability to
select the data from a dropdown, for the query to use as a basis for which
records need to be returned.

In other words, I need the user to be able to select from a list of
customers, and return contract records based on the customer the user
chooses from the dropdown.

I'm sorry I didn't make myself clear. I hope this clarifies what I am
trying to accomplish.

Thanks again for your help.

Tom

When you construct a query, you add a field from a table. The field from
the

[quoted text clipped - 14 lines]

 

[FIECA]

I am afraid most of that was greek to me. How do I get the form with the
combo box to appear for the user to select the criteria? Afterwards, how do
I run the query with the criteria selected in the combo box. I did create
the box, and I did place the appropriate script event in the field in the
query. However, when I run the query, the combo box doesn't appear, but
another popup appears, prompting the user for input, but not in the form of
a combo box (just a text box). I hate to keep harping on the same question,
but there is a step or something I am missing here.

Thanks for your patience and help.

Tom

AFAIK, this cannot be done with the parameter in a parameter query. You
can
do it by creating a form with a combobox. So, whatever triggers the query
now will open the form instead. Once a selection is made in the form, the
combobox's AfterUpdate event would trigger the query where the parameter
is
equal to the selection. I didn't realize that you were referring to the
pop
up for a parameter query.

The field is already a dropdown in the main table. How can I ensure that
the query provides a dropdown box from which the user will select the
data?

i.e. Normally, when getting input from a user, in the query you enter the
code [Enter X] in the criteria field when building the query. This
creates
a popup for the user to enter "X" and provide data to the query, as a
basis
for the records to be returned by the query.

I want the query to create a popup which provides the user the ability to
select the data from a dropdown, for the query to use as a basis for which
records need to be returned.

In other words, I need the user to be able to select from a list of
customers, and return contract records based on the customer the user
chooses from the dropdown.

I'm sorry I didn't make myself clear. I hope this clarifies what I am
trying to accomplish.

Thanks again for your help.

Tom

When you construct a query, you add a field from a table. The field
from
the

[quoted text clipped - 14 lines]

Tom

 

[John Vinson]

I am afraid most of that was greek to me. How do I get the form with the
combo box to appear for the user to select the criteria? Afterwards, how do
I run the query with the criteria selected in the combo box. I did create
the box, and I did place the appropriate script event in the field in the
query. However, when I run the query, the combo box doesn't appear, but
another popup appears, prompting the user for input, but not in the form of
a combo box (just a text box). I hate to keep harping on the same question,
but there is a step or something I am missing here.

You're doing it backwards.

Don't expect the Query to open the Form. Open the form FIRST, and put
a command button on it to open the Query (or, better, to open a Form
or Report based on the query; query datasheets should generally be
used only for debugging).

If you have a switchboard, just make this form one of the options.

John W. Vinson[MVP]

 

[FIECA]

I am afraid I have to give up at this point. I have done what I believe are
your instructions, but I just can't make it work. I thought it would be
fairly simple to create a form to display data based on criteria chosen by
the user via a dropdown box. However, this seems to be much more complex,
and I'll just have to settle for a simple query and the user will have to
deal with figuring out the exact spelling to bring up the proper results.
Thanks for trying, though. I have just spent too much time on this. If I
had known it would take this much time to figure out, I wouldn't have even
tried it to begin with.