Listbox selection limited by current form record

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

Any reason to use List Boxs rather than Combo boxes?
The Cascading combo box technique works with List Boxes as well. If you are
not familiar with this, the way it is done is to filter Combo2's row source
query on the value in Combo1 and filter Combo3's row source query on the
value in Combo2.

To refresh the list, you requery the downstrean combos in the After Update
event of the combo. That is, in Combo1, requery combo2 and combo3 and in
Combo2, requery combo3.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

My only reason for using the List box was because I am under the impression
that a combo box only shows the currently selected option as opposed to all
available options (which is what I want to be able to see). Is that correct?

"Klatuu" wrote:

Any reason to use List Boxs rather than Combo boxes?
The Cascading combo box technique works with List Boxes as well. If you are
not familiar with this, the way it is done is to filter Combo2's row source
query on the value in Combo1 and filter Combo3's row source query on the
value in Combo2.

To refresh the list, you requery the downstrean combos in the After Update
event of the combo. That is, in Combo1, requery combo2 and combo3 and in
Combo2, requery combo3.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

Also, I appear to be doing something wrong in my selection. I currently have
this:
SELECT [City].[ID], [City].[CityName] FROM [City] WHERE
((([City].[State])=[Forms]![Frm StateForm]![StateName])) ORDER BY
[City].[CityName];

"mt_pelion" wrote:

My only reason for using the List box was because I am under the impression
that a combo box only shows the currently selected option as opposed to all
available options (which is what I want to be able to see). Is that correct?

"Klatuu" wrote:

Any reason to use List Boxs rather than Combo boxes?
The Cascading combo box technique works with List Boxes as well. If you are
not familiar with this, the way it is done is to filter Combo2's row source
query on the value in Combo1 and filter Combo3's row source query on the
value in Combo2.

To refresh the list, you requery the downstrean combos in the After Update
event of the combo. That is, in Combo1, requery combo2 and combo3 and in
Combo2, requery combo3.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

If that is what you want, a List Box is fine.
You don't say what the problem is with the query.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

Also, I appear to be doing something wrong in my selection. I currently have
this:
SELECT [City].[ID], [City].[CityName] FROM [City] WHERE
((([City].[State])=[Forms]![Frm StateForm]![StateName])) ORDER BY
[City].[CityName];

"mt_pelion" wrote:

My only reason for using the List box was because I am under the impression
that a combo box only shows the currently selected option as opposed to all
available options (which is what I want to be able to see). Is that correct?

"Klatuu" wrote:

Any reason to use List Boxs rather than Combo boxes?
The Cascading combo box technique works with List Boxes as well. If you are
not familiar with this, the way it is done is to filter Combo2's row source
query on the value in Combo1 and filter Combo3's row source query on the
value in Combo2.

To refresh the list, you requery the downstrean combos in the After Update
event of the combo. That is, in Combo1, requery combo2 and combo3 and in
Combo2, requery combo3.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

Sorry! The problem is that Access tells me that the expression is typed wrong
or too complex.

"Klatuu" wrote:

If that is what you want, a List Box is fine.
You don't say what the problem is with the query.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

Also, I appear to be doing something wrong in my selection. I currently have
this:
SELECT [City].[ID], [City].[CityName] FROM [City] WHERE
((([City].[State])=[Forms]![Frm StateForm]![StateName])) ORDER BY
[City].[CityName];

"mt_pelion" wrote:

My only reason for using the List box was because I am under the impression
that a combo box only shows the currently selected option as opposed to all
available options (which is what I want to be able to see). Is that correct?

"Klatuu" wrote:

Any reason to use List Boxs rather than Combo boxes?
The Cascading combo box technique works with List Boxes as well. If you are
not familiar with this, the way it is done is to filter Combo2's row source
query on the value in Combo1 and filter Combo3's row source query on the
value in Combo2.

To refresh the list, you requery the downstrean combos in the After Update
event of the combo. That is, in Combo1, requery combo2 and combo3 and in
Combo2, requery combo3.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

That means there is something wrong with the query, but I don't see the problem
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

Sorry! The problem is that Access tells me that the expression is typed wrong
or too complex.

"Klatuu" wrote:

If that is what you want, a List Box is fine.
You don't say what the problem is with the query.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

Also, I appear to be doing something wrong in my selection. I currently have
this:
SELECT [City].[ID], [City].[CityName] FROM [City] WHERE
((([City].[State])=[Forms]![Frm StateForm]![StateName])) ORDER BY
[City].[CityName];

"mt_pelion" wrote:

My only reason for using the List box was because I am under the impression
that a combo box only shows the currently selected option as opposed to all
available options (which is what I want to be able to see). Is that correct?

"Klatuu" wrote:

Any reason to use List Boxs rather than Combo boxes?
The Cascading combo box technique works with List Boxes as well. If you are
not familiar with this, the way it is done is to filter Combo2's row source
query on the value in Combo1 and filter Combo3's row source query on the
value in Combo2.

To refresh the list, you requery the downstrean combos in the After Update
event of the combo. That is, in Combo1, requery combo2 and combo3 and in
Combo2, requery combo3.
--
Dave Hargis, Microsoft Access MVP


"mt_pelion" wrote:

An example of what I am trying to do is this:
I have a Table for States, Cities, and Streets
I have a States Form that allows you to select a single State and see info
on it.
I would like to have 2 list boxes on this form.
The first list box would only show available Cities in the currently
selected State record and would change based on whichever State was the
active record.
The second list box would only show available Streets in the City selected
in the first box.
From there, a subform would show the crew assigned to that street.
I do not want more than one option selected at a time in either list box.

Thanks!

On Thu, 17 Sep 2009 10:28:01 -0700, mt_pelion
wrote:

Sorry! The problem is that Access tells me that the expression is typed wrong
or too complex.

Try explicitly specifying the parameter:

PARAMETERS [Forms]![Frm StateForm]![StateName] Text;
SELECT [City].[ID], [City].[CityName] FROM [City] WHERE
((([City].[State])=[Forms]![Frm StateForm]![StateName])) ORDER BY
[City].[CityName];
--

John W. Vinson [MVP]