ComboBox on 2 tables

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

Guest

I have a form set up that has a combo box to choose one of four possible
types of items. Based on the specific choice, the next field, a seconf combo
box, should provide the options available to that particular choice. (Nested
combo boxes?) The problem I am having relates to the record source for the
second combo box. In the case that the user chooses item 1 of the first
combo box, the second box should show options based on table A (or query A).
If the second choice is used, the second box should provide options based on
table B (or query B). If the third or fourth choices are used, the combo box
control should go dark and not allow any data. Here it would be great to be
able to use conditional formating, but the wizard assumes that the formatting
will be based on the choice made in the same control that it is formatting,
which is not the case here. Can I achieve all this?
 
Rich said:
I have a form set up that has a combo box to choose one of four
possible types of items. Based on the specific choice, the next
field, a seconf combo box, should provide the options available to
that particular choice. (Nested combo boxes?) The problem I am
having relates to the record source for the second combo box. In the
case that the user chooses item 1 of the first combo box, the second
box should show options based on table A (or query A). If the second
choice is used, the second box should provide options based on table
B (or query B). If the third or fourth choices are used, the combo
box control should go dark and not allow any data. Here it would be
great to be able to use conditional formating, but the wizard assumes
that the formatting will be based on the choice made in the same
control that it is formatting, which is not the case here. Can I
achieve all this?
Click to expand...

For the first part of your question, what is sometimes called "cascading
combo boxes", see this article on the Access Web:

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

For the second part, the conditional formatting, you can actually do
more with conditional formatting than you think. In this case, you
could use the "Expression Is" option, write an expression like:

Combo1=3 Or Combo1=4

and make Combo2 be disabled when that expression is true.
 
Dirk,

I've studied the mvps reference you sent me and think it isn't quite what I
am trying to do. It's possible that I should rethink the premise. What I
think I want to do is: Base the Record Source of a combo box on the choice
made in another combo box, BUT that the Record Source for one choice would be
a different table/query than the Record Source for a second choice. Is this
just bad table design? Should I try somehow to combine the two tables and
use the mvps code to filter the cascaded cbx?

I think your suggestion regarding conditional formatting for the other two
choices, which require no input in the cascaded combo box, will probably work
for me if I can figure out the format of the wizard.

Rich
 
Rich said:
Dirk,

I've studied the mvps reference you sent me and think it isn't quite
what I am trying to do. It's possible that I should rethink the
premise. What I think I want to do is: Base the Record Source of a
combo box on the choice made in another combo box, BUT that the
Record Source for one choice would be a different table/query than
the Record Source for a second choice. Is this just bad table
design?
Click to expand...

Not necessarily. It may be, but I don't know enough about your table
design to say.
Should I try somehow to combine the two tables and use the
mvps code to filter the cascaded cbx?
Click to expand...

No. I don't see why you think the code I pointed you to won't serve
your need. The first of the two techniques shown involves changing the
rowsource of the second combo box in the AfterUpdate event of the first,
and that sound like exactly what you want to do. In the example, the
rowsource is being set to pull a different field from the same table,
but there's nothing stopping you from simply setting an entirely
different table or query to be the rowsource, depending on what was
chosen in the first combo. For (simplified) example,

Private Sub Combo1_AfterUpdate()

Select Case Me.Combo1
Case 1
Me.Combo2.RowSource = "Table1"
Case 2
Me.Combo2.RowSource = "Table2"
Case 3
Me.Combo2.RowSource = "Query3"
End Select

End Sub
 
Back
Top