How can I use a lookup table for select parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have normalised my database and some of the fields are now linked to lookup
tables via ID numbers. My problem is this .... when I want to run a select
query the select parameter must now be the ID number rather than the data in
the field. This would be very difficult for the user as the lookup table can
have a very high number of entries and to have to remember the ID number is
not viable. Is there anyway that the select parameter can use a drop down box
linked to the lookup table?
 
You have taken your application up a notch by normalizing. Take it up
another notch by not using parameter prompts in queries. Use references to
controls on forms. This provides much greater functionality and a better
user interface.
 
Duane,

How very, very true! Highly recommended for any application unless the
programmer is also the only person who will use it.

Tom Ellison
 
The parameter box in a query cannot use a combo.

Some alternatives:

1. Use text-based keys
=================
For lookup tables such as categories, you do not need to use a hidden
autonumber field. The category name is unique, and can be the primary key of
the category table. The foriegn key in the related table then uses the
category name (text field of the same size), and the problem of
finding-by-number is gone.

This technique is not suitable for large tables such as customers where a
natural key may not be possible and could span many fields.

2. Use a query that includes the lookup table.
==================================
Create a query that uses both the table you need and the lookup table. You
can then include the text field from the lookup table in the query grid, and
so you are now able to filter the query based on that text field.

This technique may not be suitable where you already have several tables in
your query, and you may need to use outer joins if the lookup is not
required.

3. Use a combo on a form
====================
In the Criteria row of your query, you can enter something like:
[Forms].[Form1].[Combo2]
Provided the form is open at the time, and the combo has a value, and its
Bound Column is of the right type, the query will read the value from the
combo on the form instead of popping up the parameter box.

This technique is not suitable if you need to OpenRecordset() or Execute
this query in code, unless you also supply the parameter programmatically.
 
Hi Guys, thanks for your help, I've finally used the following from a
previous post

"Make a new unbound form or use an existing form.
Add a combo box that will show the CodeID field.
Make sure the Combo Box Bound Column is the
same DataType as the CodeID field.
Add a command button.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

Code the Query CodeID field's criteria line
forms!ParamForm!ComboBoxName

Open the form.
Find the CodeID in the combo box.
Click the command button.

The query will display just those records selected.
The Form will close."
I actually use this code to run a report from the query, but now I have
another problem.
When I open the report it immediately prints, is there anyway I can open the
report in "preview mode"?



Allen Browne said:
The parameter box in a query cannot use a combo.

Some alternatives:

1. Use text-based keys
=================
For lookup tables such as categories, you do not need to use a hidden
autonumber field. The category name is unique, and can be the primary key of
the category table. The foriegn key in the related table then uses the
category name (text field of the same size), and the problem of
finding-by-number is gone.

This technique is not suitable for large tables such as customers where a
natural key may not be possible and could span many fields.

2. Use a query that includes the lookup table.
==================================
Create a query that uses both the table you need and the lookup table. You
can then include the text field from the lookup table in the query grid, and
so you are now able to filter the query based on that text field.

This technique may not be suitable where you already have several tables in
your query, and you may need to use outer joins if the lookup is not
required.

3. Use a combo on a form
====================
In the Criteria row of your query, you can enter something like:
[Forms].[Form1].[Combo2]
Provided the form is open at the time, and the combo has a value, and its
Bound Column is of the right type, the query will read the value from the
combo on the form instead of popping up the parameter box.

This technique is not suitable if you need to OpenRecordset() or Execute
this query in code, unless you also supply the parameter programmatically.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bil Neilsen said:
I have normalised my database and some of the fields are now linked to
lookup
tables via ID numbers. My problem is this .... when I want to run a select
query the select parameter must now be the ID number rather than the data
in
the field. This would be very difficult for the user as the lookup table
can
have a very high number of entries and to have to remember the ID number
is
not viable. Is there anyway that the select parameter can use a drop down
box
linked to the lookup table?
Click to expand...
Click to expand...
 
Back
Top