P
Paul
Apologies in advance but I think the best way to ask this question is
to cut and paste a previous discussion from the general group; it was
suggested that I post it here (see quoted text).
=======
Paul:
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!).
=======
ruralguy via AccessMonster.com:
Hi Paul,
It is called Cascading Combos. See if Martin's explanation helps.
http://www.fontstuff.com/access/acctut10.htm
=======
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!
=======
ruralguy via AccessMonster.com:
Hi Paul,
You might consider posting this in the query section.
=======
And here we are! Can anyone help me?
Paul
to cut and paste a previous discussion from the general group; it was
suggested that I post it here (see quoted text).
=======
Paul:
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!).
=======
ruralguy via AccessMonster.com:
Hi Paul,
It is called Cascading Combos. See if Martin's explanation helps.
http://www.fontstuff.com/access/acctut10.htm
=======
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!
=======
ruralguy via AccessMonster.com:
Hi Paul,
You might consider posting this in the query section.
=======
And here we are! Can anyone help me?
Paul