limit the contents of one combo/list box based on what's selected inanother combo/list box

[CDF]

Ok so I think I've totally stuffed this up.

As the heading goes I'm trying to fill a combo box with items
depending on what is chosen in another combo box.

I've got a Vehicle Make combo box and a Vehicle Model combo box.
Therefore if someone chooses Honda in the 'make' cbx, then only Honda
models will populate the 'model' cbx.

Simple!!...not for an amateur like me!!

I read this article on mvps.org but I couldn't work it out to fit with
my scenario:

http://www.mvps.org/access/forms/frm0028.htm

It says to set my Make cbx RowSourceType to "Field List" and RowSource
to a table Category, but all I get in the Make cbx list are 2 items:
Make_ID and Make, not the actual list of cars????? But if I set the
rowsourcetype to table/query and the rowsource to an available query
then the 'make' cbx works.

From the example on the mvps site it looked like it was trying to run
an SQL query so I ended up trying to put my own SQL statement in the
afterupdate event of the first cbx and ended up with this:

Private Sub cbx_vehicle_Make_AfterUpdate()

Dim strSQL As String
Dim StrSQLL As String

strSQL = Me!vehicle_Make
StrSQLL = "Select " & tbl_Model.Model & "from " & tbl_Make & "
INNER JOIN " & tbl_Model & " ON " & tbl_Make.Make_ID =
tbl_Model.Make_ID & " where Make = " & strSQL
Me!vehicle_Model.RowSourceType = "Table/Query"
Me!vehicle_Model.RowSource = StrSQLL

End Sub

It doesn't work...what a surprise!! Now before you gurus start
laughing like you've never laughed before, I must point out I'm not a
programmer of VB or SQL but understand both if told the proper code to
use.

Also I've got 4 tables in this database, tbl_Customer, tbl_Vehicle,
tbl_Vehicle_Make, and tbl_Vehicle_Model. Cust and Veh are linked via
Customer_ID. Make and Model are linked via Make_ID, BUT all four are
not linked somehow. Should they be?????? I thought not as the make
and model tables are only used to populate the cbx's on the vehicle
form. Do they still need some sort of link to the other 2 tables????

I'm sure this is a simple question but I just can't get my head around
it.

I've totally confused myself (and probably you by now). If I've left
out any crucial details in order for you to help me, please ask me any
questions you need and I'll answer them asap.

Any help would be appreciated.

 

[Tom Wickerath]

Hi CDF,

Try this MSKB article:

How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
http://support.microsoft.com/?id=289670


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[CDF]

Thanks Tom but I'm just as confused.

I read the article and ended up with this mess:

------------------------------------------------

Private Sub cbx_vehicle_Make_AfterUpdate()

Me.cbx_vehicle_Model.RowSource = "SELECT tbl_Model.Model FROM" & _
" tbl_Make INNER JOIN tbl_Model ON
tbl_Make.Make_ID=tbl_Model.Make_ID WHERE tbl_Make.Make = " &
Me.cbx_vehicle_Make & _
" ORDER BY tbl_Model.Model"
Me.cbx_vehicle_Model = Me.cbx_vehicle_Model.ItemData(0)

End Sub

------------------------------------------------

One thing I read in the article that has confused me even more now is
"Create a new form that is not based on any table or query". The form
I'm using needs the query it's attached to. Without it It won't work?

I'm still confused. Do you have any other suggestions?

 

[ken]

I'm assuming the two combo boxes are bound to MakeID and ModelID
columns (fields) in the tbl_Vehicle table, which I'd guess is
recording sales (or similar) of vehicles to customers, and this table
is included in the form's underlying query. If so, this in fact
answers part of your question, because this table is actually
modelling a many-to-many relationship between tbl_Customer,
tbl_Vehicle_Make and tbl_Vehicle_Model, so they are in fact 'linked',
albeit not directly as would be the case with a one-to-many
relationship. A many-to-many relationship like this is always
modelled by a table which resolves it into two or more one-to-many
relationships.

To answer your main question:

Set the MakeID combo box up like this:

Name: cboMake

ControlSource: MakeID

RowSource: SELECT MakeID, Make FROM tbl_Vehicle_Make ORDER BY
Make;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.

Set the model combo box up like this:

Name: cboModel

ControlSource: ModelID

RowSource: SELECT ModelID, Model FROM tbl_Vehicle_Model WHERE
MakeID = Form!cboMake ORDER BY Model;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In cboMake's AfterUpdate event procedure, and in the form's Current
event procedure requery the model combo box with:

Me.cboModel.Requery

BTW by including both ModelID and MakeID in the tbl_Vehicle table its
not correctly normalized in fact as ModelID determines MakeID, so
MakeID is redundant in this table. Most people would probably live
with this degree of redundancy, and the consequent risk of
inconsistent data, but if you want to see how this sort of thing can
be done and maintain a normalized design then take a look at my demo
file at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

It uses geographical data and a 3-layer rather than 2-layer hierarchy,
but the principles are the same.

Ken Sheridan
Stafford, England

 

[CDF]

Hi Ken,

Thanks for your response, please read below.

I'm assuming the two combo boxes are bound to MakeID and ModelID
columns (fields) in the tbl_Vehicle table, which I'd guess is
recording sales (or similar) of vehicles to customers, and this table
is included in the form's underlying query.  

Um...no That's where I was confused. As described above, I've got
4 tables. 2 are linked to each other. The other 2 are linked to each
other, but there is no link inbetween. Ie if I open up relationships
in access, there's one table with the relationship to the next table,
then a gap then another table with a relationship to the next table.

tlb_Customer 1:M tbl_Vehicles <gap> tbl_Make 1:M tbl_Model

Should I link the tbl_Make.Make_ID to the vehicles table? If so do I
need to also link the tbl_Model.Model_ID to it?

Oh and the forms underlying query does not have any details from the
tbl_Make or tbl_Model tables as they're not linked (as per above).

I've completely screwed it up haven't I ???

Thanks for your patience. Any help would be appreciated. Ask me
whatever you need to understand my dilemma.

 

[CDF]

I did it!!

I got the query in the 2nd combo boxes row source to work. I had
quote marks in places they were not meant to be. It's now:

SELECT Model, Model_ID
FROM tbl_Make INNER JOIN tbl_Model ON
tbl_Make.Make_ID=tbl_Model.Make_ID
WHERE (((Make)=Forms![Vehicle Installation Form - Add NEW VEHICLE]!
cbx_vehicle_Make))
ORDER BY Model;

There was no need to link the Make and Model tables to the Vehicle
table.

Thank you Ken and Tom for your time.

 

[ken]

I have to admit that I don't fully understand your logical model. The
customers, makes and models entity types are clear enough and are
modelled by their respective tables. But what is the tbl_Vehicle
table modelling? If its modelling a specific vehicle bought, hired or
whatever by a particular customer then I'd expect a many-to-one
relationship between it and tbl_Model on Model_ID. Not 'needing' a
relationship doesn't necessarily mean there shouldn't be one as
without it referential integrity can't be enforced. The database will
work without it, but its open to inconsistent data.

Ken Sheridan
Stafford, England

 

[CDF]

Hi Ken,

Firstly let me explain my 'Access Experience'. I know databases like
I know the Greek language. I'm not Greek and I don't know the language
but I've been told all the swear words... and they can get you into
trouble.

Likewise I've dabbled in Access and I know bits and pieces of SQL and
VB (the swear words) and because I don't know what I'm doing it can
get me into trouble!!

I hope that clears things up (...probably clear as mud :0)

So, because of my lack of experience I don't understand what you mean
by 'Logical Model'. I'll try and explain what I've done.

I have Customers (tbl_Customer) (who are recorded on the client form)
who have a vehicle/s (tbl_Vehicle) but each vehicle only has one
owner, hence the one to many relationship. The vehicles have alarms
and tracking devices installed into them and this information is
entered via the vehicle form.

Also on the vehicle form is the vehicle Make and Model fields (combos)
which are vehicle_Make and vehicle_Model in the vehicle table. I
created a Make table (tbl_Make) and a Model table (tbl_Model) for the
sole purpose of populating the combo boxes. In order to populate the
Model cbox based on what is selected in the Make cbox, I had to create
a relationship between the Model table and the Make table.

Does this make sense? Should I have gone about it another way? The
thought of linking the Model (and/or Make) table/s to the Vehicle
table has totally confused me. I can't picture in my mind how this
would work in my scenario.

 

[ken]

It makes perfect sense, and in fact confirms what I thought.

'Logical model' merely means the way in which that part of the real
world which the database is modelling is represented by a set of
related tables. A well designed database follows certain rules which
ensure that it accurately models the reality.

In a relational database each table represents an 'entity type' and
each column of a table represents an 'attribute' of the entity type.
In your case the customers, makes and models entity types are self-
explanatory. The vehicles entity type is a little more complex,
however, as it is not only an entity type, but also a relationship
type. You are used to thinking of relationships as being the 'links'
between two tables which you set up in the relationships window and
appear in it as the lines between two tables, with details of the type
of relationship in the 'Edit Relationship' dialogue. Those are one-to-
many, or occasionally one-to-one relationships types. However, there
are also many-to-many relationship types, which cannot be represented
in this way and are represented by a table.

In your case there is a one-to-many relationship between customers and
vehicles, and there is a one-to-many relationship between makes and
models. There is also a many-to-many relationship between customers
and models in that each customer can own one or more models (of the
same or different makes), and each model can be owned by one or more
customers. Its this relationship which the vehicles table models so
as well as representing an entity type this table also represents a
relationship type. This can be more easily seen if the logical model
is drawn out diagrammatically:

Customers----<Vehicles>----Models>----Makes

where the > and < signs indicate the 'many' end of each relationship.
As you see the many-to-many relationship is resolved into two one-to-
many relationships by the vehicles table, which is how this type of
relationship is always represented.

You have a Vehicle_Model column in tbl_Vehicle so you should create
and enforce a relationship on this with tbl_Model (if you've used the
Lookup Field wizard to create this column in the table what you might
no realize is that, while you see the make the actual value in the
column is the Make_ID, and it will also have created a relationship
with tbl_Model behind the scenes. The same goes for Vehicle_Make).
You'll notice, however that in my diagram above there is no similar
relationship between vehicles and models. This is because the
Vehicle_Makecolumn in tbl_Vehicle is in fact redundant. By having
just the Vehicle_Model foreign key column referencing the Model_ID
primary key column of tbl_Model we know what make the vehicle is
because the row in tbl_Model which is referenced itself references a
row in tbl_Make.

Ideally you should delete the Vehicle_Make column in tbl_Vehicle, but
that does make your use of correlated combo boxes when inserting a row
in tbl_Vehicle more tricky; you'd have to use an unbound combo box for
the make or, if the form is in continuous form view, a 'hybrid'
control as in my demo file to which I gave you the link. That does
require a bit more work in setting up the form, though.

What you can do if you don't want to normalize the table to that
degree (and many people don't do so in similar situations) is to
relate the tbl_Vehicle table to the tbl_Model table on the two columns
Model_ID and Make_ID, which you do by adding a second line in the Edit
Relationship dialogue with Make_ID, in addition to the first line with
Model_ID, making sure that referential integrity is also enforced.
This will mean that only a valid combination of Make_ID and Model_ID
can be entered in each row in tbl_Vehicle. This is OK with a small
relationship 'chain' like this, but with a longer set of tables it can
get cumbersome, e.g. with Properties>----Streets>----Cities>----
Regions>----Countries the relationship between Properties and Streets
would be on 4 columns! And if apartments and neighbourhoods are
thrown into the mix, well….The use of 'surrogate' numeric keys like
CityID means we can avoid this and relate each pair of tables on one
column from each,as we know from the distcinct numbers which is Paris,
France and which is Paris, Texas.

Finally, to increase your Greek vocabulary, the structure of a
relational database in terms of its tables and relationships is known
by the Greek word 'schema', which you can think of as being the
implementation of the 'logical model'.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

Does this make sense? Should I have gone about it another way? The
thought of linking the Model (and/or Make) table/s to the Vehicle
table has totally confused me. I can't picture in my mind how this
would work in my scenario.

The logic is actually pretty simple:

Every Vehicle is of one and only one Model.
Every Model may pertain to zero, one, or many Vehicles.

You have a one (Model) to many (Vehicles) relationship.

Establishing the relationship in your Tables prevents you from entering a
vehicle with a nonexistant Model - sure, the combo box would make it easier to
add from the list, but there'd be nothing in principle to stop a user from
opening the table directly, or a query on it, and entering a Starsplitter as
the model for their car. The relationship would ensure that this didn't
happen.

 

[CDF]

Thank you John and Ken for spending the time explaining this in
detail. Ken I actually understood most of what you wrote

I will have to sit down, put my thinking cap on, create the
relationship between tbl_vehicle and tbl_model as suggested and make
the necessary changes to my database.

No doubt I'll keep you informed of my progress as I'm sure this will
raise more questions.

Thanks again for your time.

 

[John M. Wolff]

Hello. How are you