Linked Drop Down boxes

[FreoDockers]

Hi,

I'm a very new user to ms Access and have been trying to fix an old data
base. I'm getting stuck on a linked drop dwon box. What I'm trying to do is
use the selection from the first drop down box determine the contents of the
second one.

For example if someone selects Ford in the first drop down box only ford
model vehicles will be shown in the second drop down box.

It seems the link was made in the past using two tables with a common
secondary key. So, for example, an F would be entered after all of the fords
in the master vehicle database. The same key was then entered after Ford in
the vehicle selection database.

I'm using this for pipe size and material selection so it's a little
different but the princplile is the same.

Pipe material type is selected in the first column from a drop down list (is
stainless, carbon steel, HDPE etc). The second column is then a drop down
list of the available pipe sizes made from that material. In the second drop
down box the common key bewteen lists is joined by dragging the tag across.
Only the pipe size is ticked to display in the drop down list.

My problem is the entire pipe size list is displayed (not the filtered one
for that material type). I've tried all sorts of things to fix this and have
been unable to do so.

Any assistance with this problem would be appreciated.

Cheers

Matt

The link was made by showing both tables and linking the common secondary
key (by dragging the vehicle tag across from the many table to the few
table). The

 

[Al Campagna]

Matt,
I responded to your email to my web site with some preliminary suggestions. You said
you had tried "Access help"... I did not realize that you meant the Access newsgroups.
I think this post is a bit more confusing than the email you sent me (Autos vs Pipes
with no sample data)
Review my suggestions, and post back here if you feel you have the problem solved... or
not.
I would add your post to me to this thread so that other folks might want to wade in...
The table structure and samples are critical, as well as the "drill down" sequence you
want to employ.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[FreoDockers]

Hi Al,

Thanks for both of your replies. I agree my posting on this site isn't too
clear but I'll try and fix that up now.

I wish to make a form which is used to define pipe lines in piping and
instrumentation diagrams. The reduce the time taken to fill out each line and
significantly reduce the chance of errors occuring I've tried to impliment a
filtered list.

The form would work something like this:
- in the first column the user selects the specification code for the
material they whish to use. The are about 20-30 choices of spec code from a
drop down list. Of the choices available they can be grouped in 4 standard
sizes.
- The user then choses from a drop down list if they wish to see the
preferred or non preferred sizes.
- This two lots of information is then used to filter the pipe size drop
down list (which contains about 150 entries) to only show the pipe sizes for
the preferred or non preferred pipe size of the (four) standard pipe sizes.

I'll show the tables below to try and clarify this. The first table is the
size table which contains about 150 entries. The first column is the primary
key, the second is the pipe size which there can be one to four of the same
number, the third is standard material size spec (four types) each MATL has a
standard pipe size list. The final column is a selection to show preferred or
non preferred pipe size.

The second table contains the pipe specification. The user will select from
Spec Code column to chose the material type. This choice will then define the
standard material size to use (PVC, S, PE or ABS). The user then selects if
they wich to see preferred or non preferred pipe sizes. Finally this info is
used to display a filtered list of pipe sizes.

Thanks again for the help Al and I'm sorry for any confusion.

Cheers

Matt

ID SIZE MATL PREFERRED
1 6 S FALSE
2 8 S TRUE
3 8 PVC TRUE
4 8 ABS FALSE
5 10 S FALSE
6 15 S TRUE
7 15 PVC TRUE
8 15 ABS TRUE
9 16 PE TRUE
10 20 S FALSE
11 20 PE TRUE
12 20 PVC TRUE
13 20 ABS TRUE
14 25 S TRUE
15 25 PE TRUE
16 25 PVC TRUE
17 25 ABS TRUE
18 32 S FALSE
19 32 PE FALSE
20 32 PVC FALSE
21 32 ABS FALSE
22 40 S TRUE
23 40 PE TRUE
24 40 PVC TRUE

SPECCODE MATL
A ABS
B S
C S
D S
E ABS
F PE
G S
H S
I S
J S
K S
L ABS
M S
N S
O PVC
P S
Q PVC
R PE
S S
T S
U S

 

[Al Campagna]

Your going to try to define a specific pipe through a series of combo (or list) boxes.
Given ex. tables of tblSpecs and tblSizes...
You can select a SpecCode in the first combo (cboSpec)... but...
SpecCode itself does not relate to the data in your tblPipeSize, only the Material
does... and that's a Many to Many realtionship (many Materials in Specs vs. many Materials
in Sizes.

Say you select "B" in the SpecCode with an associated/related Material of "S"
The 2nd combo would have a query like this as the RowSource (tblSpecs related to table
Sizes by Material)
Spec Size
Material ------> Material
= Forms!frmYourForm!cboSpec

which would yield ANY Size Record with an "S" Material.

Now... if some of those Size selections don't really apply to a "B" SpecCode... that's
where your problem is, and it's not workable with the table design you have. How can the
user know that the size they choose is really a "B" Spec? Sizes data does not relate to
SpecCode.
Are we clear so far?

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[FreoDockers]

Hi Al,

I'll try and reply to you comments as best I can.

Yes, I am trying to define a specific pipe through a series of combo boxes.

I've though of how I can simplify the realtionships by using the program for
related drop down boxes you've psted on your website.

Firstly users select the material type from the grouped list of four (S,
ABS, PVC, PE). Then a filtered list of available pipe specs will be selected
next (A though to U). The first selection will also be used to filter the
available pipe sizes.

I've almost completed the first part (by copying your example data base) but
have hit a bit of a snag. Only the result in the first ro of the data base in
the MATL column is used to filter the Spec Code. Eg, if an S is in the first
row then all of the spec types are populated with the choices for S.

Matt

 

[Al Campagna]

Matt,
OK, bear with me...
I'm still a bit confused about your drill down process.
I lost you on what you want to see in combo 2... vs what you're getting now.

Please indicate, * as you go from one combo to another...*
(A real life drill down... using your sample values...)
1. What you "want" see in EACH combo/list... vs. what you see there now.
2. The criteria you have behind each of the combos.
(an SQL or a description of the field/s crireria)
3. Use exact object names...

ex. 1. "Here's my RowSource for lstOne, and here's what I see in lstOne, and here's the
value that lstOne will have."
2. "Here's the RowSource for lstTwo, and here's what I see in lstTwo, and here's
what's wrong with that, and here's what I want to see in lstTwo."

Patience... we'll get there. With problems like these, we have to be very exact as to
"what I have now" vs "what I want"...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[FreoDockers]

Hi Al,

Thanks very much for your help, I got it to work.

I ended up just building on the example from your website.

Thanks again.

Matt