combobox list based on two other comboboxs?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am using Access 2003. On a form, I have two combo boxes (queries) I have
set up to show two different numbers. Depending on the combination of these
boxes, I want a third box (also a query) to show a list. Unfortunately, the
return variable that is associated with the third list is not consistent.

I am having trouble wrapping my mind around the logic of how to set the
tables up for this. I have a pdf that shows the form and options I am
looking for but don't see a way to attach it here. I can email it if needed.
 
Mike,
your code can sort through the selections like this

Select Case Me.cboFirst
Case SomeNumber
If Me.cboSecond = SomeNumber Then
Me.cboThird.RowSource = query or sql here
ElseIfMe.cboSecond = SomeOtherNumber Then
Me.cboThird.RowSource = query or sql here
Else
Me.cboThird.RowSource = query or sql here
End if

Case SomeOtherNumber
If Me.cboSecond = SomeNumber Then
Me.cboThird.RowSource = query or sql here
ElseIfMe.cboSecond = SomeOtherNumber Then
Me.cboThird.RowSource = query or sql here
Else
Me.cboThird.RowSource = query or sql here
End if

'more case statements here if needed
End Select

Jeanette Cunningham
 
That would probably work. I would need to have 15 seperate queries for this.

It seems that should be an easier way with relational tables. Kind of like
a double filter. If CboBG = 1 then show record where BGID <2 and if CboAG =
4 then show records where ABID<5.

Again, I am a newbie so don't know how.
 
Mike,
here is an example to show what I am trying to say:
Make a query based on cboFirst to use as the row source for cboSecond.

In query design drag the table or tables needed to create a row source query
for cboSecond.
Your query will need to include the field from cboFirst that determines what
will be shown in the list for cboSecond
Assume 2 tables, People and Colors, related one to many with PeopleID
Use the Colors table, drag the field Color, ColorID and PeopleID on to the
query grid.

Note: People ID is also in the row source for cboFirst as the bound column
(cboFirst has a query based on People, it has 2 fields - People ID and
People.LastName)

Back in the query we are building:
In the Criteria row for the PeopleID field, type a reference to cboFirst
like this:
Forms!TheFormName!cboFirst
check the show box for this column to not show this column.
Save the query with a name such as qlkpColorPeople.
In your form set the row source for cboSecond to qlkpColorPeople

Now every time you choose a different person in cboFirst, its PeopleID will
change and cboSecond will have its rowsource changed to only the colors
available for the PersonID chosen in cboFirst.

Hope the above explanation makes some sense for you.
You could search for posts about cascading combos - this question comes up
quite regularly.

Jeanette Cunningham
 
Back
Top