Linking Combo Boxes in Access 2003

[BadJooJooJen]

I want to link a series of combo boxes, so that the option picked in the
first box drives the options available in the next combo box and so on. What
is the best way to do this? I'm not very familiar with VBA, so if it has to
be done with code, please provide an example.

 

[Paul Ponzelli]

You don't have to go into VBA code to do it, you can do it in the Criteria
row of the query you're using as the Row Source of the second (dependent)
combo box.

Suppose the objects in your form have the following names:

form: frmMyForm
combo box 1 name: cmbOne
combo box 1 Control Source: Program
combo box 2 name: cmbTwo
combo box 2 Control Source: WorkCode
combo box 2 Row Source: qryCmbTwo

In qryCmbTwo, the underlying table will have a Program field and a Work Code
field.

So in the Criteria row of qryCmbTwo, put

forms!frmMyForm!cmbOne

This will restrict the recordset in cmbTwo to only those records where the
value in the Program field is equal to the selection you made in cmbOne.

Hope this helps,

Paul

 

[Dirk Goldgar]

I want to link a series of combo boxes, so that the option picked in
the first box drives the options available in the next combo box and
so on. What is the best way to do this? I'm not very familiar with
VBA, so if it has to be done with code, please provide an example.

There are two simple methods described here:

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

 

[Dirk Goldgar]

You don't have to go into VBA code to do it, you can do it in the
Criteria row of the query you're using as the Row Source of the
second (dependent) combo box.

Suppose the objects in your form have the following names:

form: frmMyForm
combo box 1 name: cmbOne
combo box 1 Control Source: Program
combo box 2 name: cmbTwo
combo box 2 Control Source: WorkCode
combo box 2 Row Source: qryCmbTwo

In qryCmbTwo, the underlying table will have a Program field and a
Work Code field.

So in the Criteria row of qryCmbTwo, put

forms!frmMyForm!cmbOne

This will restrict the recordset in cmbTwo to only those records
where the value in the Program field is equal to the selection you
made in cmbOne.

Actually, a little code *is* required, because the second combo box must
be requeried when the value in the first one changes.

 

[BadJooJooJen]

This information has pointed me in the right direction! But what I'm trying
to do is a bit more than limiting the options available in combo boxes, based
on the choice in another combo box. (Sorry I wasn't more specific in my last
post) Here's what I want to do: choose Item1 in Combo1, Combo2 appears.
Choose Item2 in Combo1, Combo3 appears, etc. Each new combo box, aside from
Combo1, has items relevant to the Item selected in Combo1. I'm probably
getting in way over my head, but you were fast and helpful last time, so I'm
going for it!!

 

[Dirk Goldgar]

This information has pointed me in the right direction! But what I'm
trying to do is a bit more than limiting the options available in
combo boxes, based on the choice in another combo box. (Sorry I
wasn't more specific in my last post) Here's what I want to do:
choose Item1 in Combo1, Combo2 appears. Choose Item2 in Combo1,
Combo3 appears, etc. Each new combo box, aside from Combo1, has
items relevant to the Item selected in Combo1. I'm probably getting
in way over my head, but you were fast and helpful last time, so I'm
going for it!!

Making one combo box or another visible is easy enough, but I'd like
more information about what you're really trying to accomplish before
advising you. What's the actual table structure that you're trying to
display and update on your form? What are the logical relationships
among these various objects? How many dependent combo boxes are we
talking about? Are these combo boxes supposed to be bound to fields in
the form's recordsource? If so, to what fields?

 

[BadJooJooJen]

I'm building a database to track the changes that are made to my company's
email client. I wanted to stay away from too many text entry fields. I have
combo1 (tblChangeMadeto) with items Users, Rules, Queues, Departments, etc,
When I choose Rules, I want combo2 (tblTypeofChange) to appear, with options
Change, New, Delete. When I choose Change in combo2, I want combo3
(tblRuleGroups) to appear. Going back, if I choose Users from combo1, I want
combo2 (tblTypeofChange) to appear, with the options that are relevant to
Users only (New, Delete, Change Password reset, etc). (or could have an
entirely separate combo box.) I don't want to have all the combo boxes
showing on the form, I was hoping to hide them until the options are
selected, then have the relevant combo boxes appear. 4 deep is the most I
have at this time. I'm a beginner, so I apologize if I haven't quite
answered your question.

 

[Dirk Goldgar]

I'm building a database to track the changes that are made to my
company's email client. I wanted to stay away from too many text
entry fields. I have combo1 (tblChangeMadeto) with items Users,
Rules, Queues, Departments, etc, When I choose Rules, I want combo2
(tblTypeofChange) to appear, with options Change, New, Delete. When
I choose Change in combo2, I want combo3 (tblRuleGroups) to appear.
Going back, if I choose Users from combo1, I want combo2
(tblTypeofChange) to appear, with the options that are relevant to
Users only (New, Delete, Change Password reset, etc). (or could have
an entirely separate combo box.) I don't want to have all the combo
boxes showing on the form, I was hoping to hide them until the
options are selected, then have the relevant combo boxes appear. 4
deep is the most I have at this time. I'm a beginner, so I apologize
if I haven't quite answered your question.

We're doing okay so far, but I have more questions to ask.

If Combo2 is always going to be "type of change", but with a different
set of possible types depending on Combo1, that's pretty
straightforward. But it sounds like things begin to diverge after that,
with not all combo boxes being required for all cases.

This is certainly very doable, but I still need to know what tables and
fields are involved, as I asked before. Please look over my original
set of questions and answer them as best you can.