How can I create a form with a cascaded lookup?

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

Guest

i.e. In a database of cars, I want to create a form where the user selects a
"Make" of car from a drop-down box and then selects a "Model" from a
drop-down which *only contains the valid Models* for the currently selected
Make. There doesn't seem to be a good way of doing this except by using VB
and dynamically altering the filter of the "Model" drop-down whenever the
"Make" is changed.
 
There is another way

In the RowSource Property of the Model combo create the SQL that includes
the filter build in, in the sql

Select ModalField From ModalTable Where Make =
Forms![FormName]![MakeComboName]
 
Is there any way to make it in the datasheet view of the table?
I usually enter the data in datasheet view and I don't make forms.

Ofer said:
There is another way

In the RowSource Property of the Model combo create the SQL that includes
the filter build in, in the sql

Select ModalField From ModalTable Where Make =
Forms![FormName]![MakeComboName]

i.e. In a database of cars, I want to create a form where the user selects a
"Make" of car from a drop-down box and then selects a "Model" from a
drop-down which *only contains the valid Models* for the currently selected
Make. There doesn't seem to be a good way of doing this except by using VB
and dynamically altering the filter of the "Model" drop-down whenever the
"Make" is changed.
 
Data entry in tables should be discouraged because of the lack of control,
security, and ease of losing data integrity. If you really want to enter
data in a datasheet, make a form and use the datasheet view. If you are only
using a 1 table database, you might as well be using a spreadsheet.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ahmed said:
Is there any way to make it in the datasheet view of the table?
I usually enter the data in datasheet view and I don't make forms.

Ofer said:
There is another way

In the RowSource Property of the Model combo create the SQL that includes
the filter build in, in the sql

Select ModalField From ModalTable Where Make =
Forms![FormName]![MakeComboName]

i.e. In a database of cars, I want to create a form where the user
selects a
"Make" of car from a drop-down box and then selects a "Model" from a
drop-down which *only contains the valid Models* for the currently
selected
Make. There doesn't seem to be a good way of doing this except by using
VB
and dynamically altering the filter of the "Model" drop-down whenever
the
"Make" is changed.
 
This worked beautifully for me with regular combo box,, but now i have added
multivalue select combo boxes to my forms that I need to synch up . They were
created via access 2007 using the mult select lookup feature in the
underlying tables.


I am trying to sycnh a Market Combo Box to a Market Region Combo Box.

both have multiselection checkboxes when i select the dropdowns in the Combo
Box.


The SQL below does not work, i get nothing in the Market Combo
Box regardless of what i select in the Market Region CB

SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region], [MARKET REGION
TABLE].[Market Region ID]
FROM [MARKETS TABLE] INNER JOIN [MARKET REGION TABLE] ON [MARKETS
TABLE].[Market Region ID] = [MARKET REGION TABLE].[Market Region ID]
WHERE ((([MARKET REGION TABLE].[Market Region ID])=[Forms]![Maintain
Products Form]![MarketRegionCB] ORDER BY [MARKETS TABLE].Market;


What I see in the Market Region CB when i make selections are
Northern
Southern
Eastern
Central

But what is bound is the ID values 1
1
2
3
4

If I hard code the values,, then the combo box does get synched up,,, see
below sql

is there any way to synch the combo boxes using sql ?

SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region], [MARKET REGION
TABLE].[Market Region ID]
FROM [MARKETS TABLE] INNER JOIN [MARKET REGION TABLE] ON [MARKETS
TABLE].[Market Region ID] = [MARKET REGION TABLE].[Market Region ID]
WHERE ((([MARKET REGION TABLE].[Market Region ID])=[Forms]![Maintain
Products Form]![MarketRegionCB] Or ([MARKET REGION TABLE].[Market Region
ID])=2 Or ([MARKET REGION TABLE].[Market Region ID])=3 Or ([MARKET REGION
TABLE].[Market Region ID])=4 Or ([MARKET REGION TABLE].[Market Region ID])=5
Or ([MARKET REGION TABLE].[Market Region ID])=6 Or ([MARKET REGION
TABLE].[Market Region ID])=7))
ORDER BY [MARKETS TABLE].Market;





--
Nancy


Ofer said:
There is another way

In the RowSource Property of the Model combo create the SQL that includes
the filter build in, in the sql

Select ModalField From ModalTable Where Make =
Forms![FormName]![MakeComboName]

i.e. In a database of cars, I want to create a form where the user selects a
"Make" of car from a drop-down box and then selects a "Model" from a
drop-down which *only contains the valid Models* for the currently selected
Make. There doesn't seem to be a good way of doing this except by using VB
and dynamically altering the filter of the "Model" drop-down whenever the
"Make" is changed.
 
I am still looking for any help anyone can give me on synching combo boxes
that are multi-select.

i found these posts,, and think they might help but don't know exactly how
to tweak it to work for my needs of synching the combo boxes.

http://support.microsoft.com/kb/827423 - how to store the list as a string
http://support.microsoft.com/kb/135546 - how to use mulit-select to filter
a form


can anyone help me use this logic to synch my combo boxes?

thanks
n
--
Nancy


Nurse Nancy said:
This worked beautifully for me with regular combo box,, but now i have added
multivalue select combo boxes to my forms that I need to synch up . They were
created via access 2007 using the mult select lookup feature in the
underlying tables.


I am trying to sycnh a Market Combo Box to a Market Region Combo Box.

both have multiselection checkboxes when i select the dropdowns in the Combo
Box.


The SQL below does not work, i get nothing in the Market Combo
Box regardless of what i select in the Market Region CB

SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region], [MARKET REGION
TABLE].[Market Region ID]
FROM [MARKETS TABLE] INNER JOIN [MARKET REGION TABLE] ON [MARKETS
TABLE].[Market Region ID] = [MARKET REGION TABLE].[Market Region ID]
WHERE ((([MARKET REGION TABLE].[Market Region ID])=[Forms]![Maintain
Products Form]![MarketRegionCB] ORDER BY [MARKETS TABLE].Market;


What I see in the Market Region CB when i make selections are
Northern
Southern
Eastern
Central

But what is bound is the ID values 1
1
2
3
4

If I hard code the values,, then the combo box does get synched up,,, see
below sql

is there any way to synch the combo boxes using sql ?

SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region], [MARKET REGION
TABLE].[Market Region ID]
FROM [MARKETS TABLE] INNER JOIN [MARKET REGION TABLE] ON [MARKETS
TABLE].[Market Region ID] = [MARKET REGION TABLE].[Market Region ID]
WHERE ((([MARKET REGION TABLE].[Market Region ID])=[Forms]![Maintain
Products Form]![MarketRegionCB] Or ([MARKET REGION TABLE].[Market Region
ID])=2 Or ([MARKET REGION TABLE].[Market Region ID])=3 Or ([MARKET REGION
TABLE].[Market Region ID])=4 Or ([MARKET REGION TABLE].[Market Region ID])=5
Or ([MARKET REGION TABLE].[Market Region ID])=6 Or ([MARKET REGION
TABLE].[Market Region ID])=7))
ORDER BY [MARKETS TABLE].Market;





--
Nancy


Ofer said:
There is another way

In the RowSource Property of the Model combo create the SQL that includes
the filter build in, in the sql

Select ModalField From ModalTable Where Make =
Forms![FormName]![MakeComboName]

i.e. In a database of cars, I want to create a form where the user selects a
"Make" of car from a drop-down box and then selects a "Model" from a
drop-down which *only contains the valid Models* for the currently selected
Make. There doesn't seem to be a good way of doing this except by using VB
and dynamically altering the filter of the "Model" drop-down whenever the
"Make" is changed.
 
Nurse said:
I am still looking for any help anyone can give me on synching combo boxes
that are multi-select.

When you mention combo boxes, do you actually mean list boxes?

The reason I ask is because I thought the multi-select property was only
supported for list boxes, not combo boxes.

Both the Knowledge Base article links you supplied are about list boxes.
i found these posts,, and think they might help but don't know exactly how
to tweak it to work for my needs of synching the combo boxes.

http://support.microsoft.com/kb/827423 - how to store the list as a string
http://support.microsoft.com/kb/135546 - how to use mulit-select to filter
a form

can anyone help me use this logic to synch my combo boxes?

I'm lost, Nancy. If you actually have a *combo* box bound to an Access
2007 multi-valued field, perhaps the combo displays multiple selections.
But I have no experience with that combination of features. WRT
multi-valued fields, I am following (my understanding of) MVP advice ...
don't use them.

If you clarify your situation, perhaps someone can offer useful help.
 
they are definately both combo boxes,, in ms2007 you can create a
multi-select combo box where you check the check boxes to select mutliple and
there is an OK and Cancel Button on the drop down. I am using them as seach
criteria for a query



Here is the SQL behind the [Market Region Combo Box]
SELECT [MARKET REGION TABLE].[Market Region ID], [MARKET REGION
TABLE].[Market Region]
FROM [MARKET REGION TABLE]
ORDER BY [MARKET REGION TABLE].[Market Region];

And the values are
1 = Norther
2 = West Coast
3 = Southern
4 = East Coast
5 = Mid West
6 = Central

What gets bound is the numeric values



Here is the SQL behind the Market Combo Box
SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID] AS State, [MARKET REGION TABLE].[Market Region], [MARKETS
TABLE].[Market Rank] AS Rank, [MARKET REGION TABLE].[Market Region ID]
FROM [MARKETS TABLE] INNER JOIN [MARKET REGION TABLE] ON [MARKETS
TABLE].[Market Region ID] = [MARKET REGION TABLE].[Market Region ID]
ORDER BY [MARKET REGION TABLE].[Market Region], [MARKETS TABLE].Market;

There are approx 300 Markets and each is assigned to a Region in the Market
Table


So for Intstance
Los Angeles Market has a region = 2 (West Coaast)

Does this help at all
I want to be able to select West Coast in the Market Region Combo Box and
only Regions in West Coast would be displayed in the Market Combo Box (which
is also Multi Select)
 
Nurse said:
they are definately both combo boxes,, in ms2007 you can create a
multi-select combo box where you check the check boxes to select mutliple and
there is an OK and Cancel Button on the drop down. I am using them as seach
criteria for a query

I think our wires were crossed regarding multi-select.

If you open the property sheet for a list box control, then select the
"Other" tab, "Multi Select" is one of the available properties and you
can choose None, Simple, or Extended as the value for that property.

That Multi Select property is not available for a combo box control.

My best guess is that your combo box is bound to a multi-valued field.
So apparently you can use it to change the values in the bound field of
the current record in your form's underlying record source. However I
don't understand why you want to alter table data in order to set up
search criteria.

Use a list box for Market Regions and set the Multi Select property to
Simple or Extended. Then, in the After Update event of that list box,
create a SQL statement which limits available Market choices to only
those included in the selected Regions, and assign that SQL statement to
the Row Source of the Markets control. If you want to allow the user to
select more than one Market, use a Multi Select list box (instead of a
combo box) for that one, too.
 
Nancy, I'm needing to do the same thing - did you ever find a solution to
this? I know this is an old thread.

I know it is possible to create a drop-down check box list using a combo box
and a look-up table, and select multiple values from it - but is there any
way to link two such drop down lists so that the second is dependant on the
first?

I want to select multiple records from a combo box list of islands, and have
a second combobox list to select specific places from each of the selected
islands.

Any help would be great!
Thanks.

Nurse Nancy said:
they are definately both combo boxes,, in ms2007 you can create a
multi-select combo box where you check the check boxes to select mutliple and
there is an OK and Cancel Button on the drop down. I am using them as seach
criteria for a query



Here is the SQL behind the [Market Region Combo Box]
SELECT [MARKET REGION TABLE].[Market Region ID], [MARKET REGION
TABLE].[Market Region]
FROM [MARKET REGION TABLE]
ORDER BY [MARKET REGION TABLE].[Market Region];

And the values are
1 = Norther
2 = West Coast
3 = Southern
4 = East Coast
5 = Mid West
6 = Central

What gets bound is the numeric values



Here is the SQL behind the Market Combo Box
SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID] AS State, [MARKET REGION TABLE].[Market Region], [MARKETS
TABLE].[Market Rank] AS Rank, [MARKET REGION TABLE].[Market Region ID]
FROM [MARKETS TABLE] INNER JOIN [MARKET REGION TABLE] ON [MARKETS
TABLE].[Market Region ID] = [MARKET REGION TABLE].[Market Region ID]
ORDER BY [MARKET REGION TABLE].[Market Region], [MARKETS TABLE].Market;

There are approx 300 Markets and each is assigned to a Region in the Market
Table


So for Intstance
Los Angeles Market has a region = 2 (West Coaast)

Does this help at all
I want to be able to select West Coast in the Market Region Combo Box and
only Regions in West Coast would be displayed in the Market Combo Box (which
is also Multi Select)
--
Nancy


Hans Up said:
When you mention combo boxes, do you actually mean list boxes?

The reason I ask is because I thought the multi-select property was only
supported for list boxes, not combo boxes.

Both the Knowledge Base article links you supplied are about list boxes.


I'm lost, Nancy. If you actually have a *combo* box bound to an Access
2007 multi-valued field, perhaps the combo displays multiple selections.
But I have no experience with that combination of features. WRT
multi-valued fields, I am following (my understanding of) MVP advice ...
don't use them.

If you clarify your situation, perhaps someone can offer useful help.
.
 
Back
Top