Using one combo box to narrow the selection in another combo box

[Paul]

Ok, so here's a good one:

I would like to dynamically limit and sort the choices shown in a combo
box based on a user's selection:

In the [RawMaterials] table I have the fields [RawMaterial#] (text) and
[RawMaterialTypeCode] (text from lookup).

In the finished products table I have the field [FinishedProduct#]
(text)

I am using a [Usage] table to create a many-to-many relationship. It
contains the fields [RawMaterial#] and [FinishedProduct#].

I am using a form and subform to enter data. The main form adds data to
the [RawMaterials] table while the subform adds data to the [Usage]
table (and is linked to the main form by [FinishedProduct#] field).

With me so far?

The subform's default view is "Continuous Forms" so allows entry of
several raw material codes, building up the association between Raw
Materials and Finished Products in the Usage table. [RawMaterial#]
values are unique across all types of raw materials. This means the
list is very long. I would like to use a combo box for selecting
[RawMaterial#] values but the length of the list is prohibitive.

I imagine a solution being to have one combo box to select
[RawMaterialTypeCode] and somehow getting an adjacent combo box to
display choices of only those [RawMaterial#] values that correspond to
that [RawMaterialTypeCode] value. The [RawMaterial#] value would be
written to the [Usage] table whilst the [RawMaterialTypeCode] value
would simply be discarded.

I imagine the way to do this would be through the use of queries but I
seem to be getting myself into knots! Is there anyone with the patience
to read all of this, make sense of it and solve it?!?

Hoping to hear from someone soon (and thanks in advance!).

Paul

 

[Paul]

Brilliant - thank you! That was exactly what I was looking for. But I
now have a slight change I need to make and am not sure how. Martin's
explanation tells me to put the following code into the After Update
properties of the cboCountry combo box:

Private Sub cboCountry_AfterUpdate()
On Error Resume Next
cboCity.RowSource = "Select tblAll.City " & _
"FROM tblAll " & _
"WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
"ORDER BY tblAll.City;"
End Sub

That worked like a charm, simply substituring table and field names. My
problem is that I am not using countries and cities, but batch numbers.
The number of them will grow very quickly and the list will become
ridiculously long. I expect there must be a simple way to do the
following:

The [Finished products] table contains the field [TestDate] (and the
main form has a conrtol for this). The [Raw materials] table has a
similar field. I would like to display in the dependent combo box only
the [RawMaterial#] values from the 10 records of the selected
[RawMaterialTypeCode] whose [Raw materials].[TestDate] values are
closest in the past to the [Finished products].[TestDate] of the
finished product record open in the main form. This is different from
simply the most recent 10 records to the current date: If I were to go
back and edit records I would want the 10 most recent raw materials to
that finished product, not to today.

I rewrote that paragraph at least 5 times and it's as clear as I can
make it! If you understand what I'm trying to achieve I'd be really
grateful for your advice!

Paul

Hi Paul,
It is called Cascading Combos. See if Martin's explanation helps.http://www.fontstuff.com/access/acctut10.htm

Ok, so here's a good one:

I would like to dynamically limit and sort the choices shown in a combo
box based on a user's selection:

In the [RawMaterials] table I have the fields [RawMaterial#] (text) and
[RawMaterialTypeCode] (text from lookup).

In the finished products table I have the field [FinishedProduct#]
(text)

I am using a [Usage] table to create a many-to-many relationship. It
contains the fields [RawMaterial#] and [FinishedProduct#].

I am using a form and subform to enter data. The main form adds data to
the [RawMaterials] table while the subform adds data to the [Usage]
table (and is linked to the main form by [FinishedProduct#] field).

With me so far?

The subform's default view is "Continuous Forms" so allows entry of
several raw material codes, building up the association between Raw
Materials and Finished Products in the Usage table. [RawMaterial#]
values are unique across all types of raw materials. This means the
list is very long. I would like to use a combo box for selecting
[RawMaterial#] values but the length of the list is prohibitive.

I imagine a solution being to have one combo box to select
[RawMaterialTypeCode] and somehow getting an adjacent combo box to
display choices of only those [RawMaterial#] values that correspond to
that [RawMaterialTypeCode] value. The [RawMaterial#] value would be
written to the [Usage] table whilst the [RawMaterialTypeCode] value
would simply be discarded.

I imagine the way to do this would be through the use of queries but I
seem to be getting myself into knots! Is there anyone with the patience
to read all of this, make sense of it and solve it?!?

Hoping to hear from someone soon (and thanks in advance!).

Paul--

HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted viahttp://www.accessmonster.com

 

[Paul]

Ok, I'll give that a go - thanks!

Hi Paul,
You might consider posting this in the query section.

Brilliant - thank you! That was exactly what I was looking for. But I
now have a slight change I need to make and am not sure how. Martin's
explanation tells me to put the following code into the After Update
properties of the cboCountry combo box:

Private Sub cboCountry_AfterUpdate()
On Error Resume Next
cboCity.RowSource = "Select tblAll.City " & _
"FROM tblAll " & _
"WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
"ORDER BY tblAll.City;"
End Sub

That worked like a charm, simply substituring table and field names. My
problem is that I am not using countries and cities, but batch numbers.
The number of them will grow very quickly and the list will become
ridiculously long. I expect there must be a simple way to do the
following:

The [Finished products] table contains the field [TestDate] (and the
main form has a conrtol for this). The [Raw materials] table has a
similar field. I would like to display in the dependent combo box only
the [RawMaterial#] values from the 10 records of the selected
[RawMaterialTypeCode] whose [Raw materials].[TestDate] values are
closest in the past to the [Finished products].[TestDate] of the
finished product record open in the main form. This is different from
simply the most recent 10 records to the current date: If I were to go
back and edit records I would want the 10 most recent raw materials to
that finished product, not to today.

I rewrote that paragraph at least 5 times and it's as clear as I can
make it! If you understand what I'm trying to achieve I'd be really
grateful for your advice!

Paul

Hi Paul,
It is called Cascading Combos. See if Martin's explanation helps.http://www.fontstuff.com/access/acctut10.htm

[quoted text clipped - 44 lines]

Message posted viahttp://www.accessmonster.com--

HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted viahttp://www.accessmonster.com

 

[Paul]

I've found an alternative solution for the time being as I would like
to get this thing up and running ASAP. The List Rows property for the
combo box will limit what is viewed so the list won't get too annoying
for some time. Because I'm only trying to manipulate the choices
available for selection (as opposed to the data itself) finding a more
sensible solution at a later date shouldn't cause me problems.

Ok, I'll give that a go - thanks!

Hi Paul,
You might consider posting this in the query section.

Brilliant - thank you! That was exactly what I was looking for. But I
now have a slight change I need to make and am not sure how. Martin's
explanation tells me to put the following code into the After Update
properties of the cboCountry combo box:
Private Sub cboCountry_AfterUpdate()
On Error Resume Next
cboCity.RowSource = "Select tblAll.City " & _
"FROM tblAll " & _
"WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
"ORDER BY tblAll.City;"
End Sub
That worked like a charm, simply substituring table and field names. My
problem is that I am not using countries and cities, but batch numbers.
The number of them will grow very quickly and the list will become
ridiculously long. I expect there must be a simple way to do the
following:
The [Finished products] table contains the field [TestDate] (and the
main form has a conrtol for this). The [Raw materials] table has a
similar field. I would like to display in the dependent combo box only
the [RawMaterial#] values from the 10 records of the selected
[RawMaterialTypeCode] whose [Raw materials].[TestDate] values are
closest in the past to the [Finished products].[TestDate] of the
finished product record open in the main form. This is different from
simply the most recent 10 records to the current date: If I were to go
back and edit records I would want the 10 most recent raw materials to
that finished product, not to today.
I rewrote that paragraph at least 5 times and it's as clear as I can
make it! If you understand what I'm trying to achieve I'd be really
grateful for your advice!
Paul
On Jan 24, 8:26 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
Hi Paul,
It is called Cascading Combos. See if Martin's explanation helps.http://www.fontstuff.com/access/acctut10.htm
[quoted text clipped - 44 lines]
Message posted viahttp://www.accessmonster.com--

HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted viahttp://www.accessmonster.com