A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How can I create a form with a cascaded lookup?



 
 
Thread Tools Display Modes
  #1  
Old September 5th, 2005, 04:55 PM
Ben04Ram
external usenet poster
 
Posts: n/a
Default How can I create a form with a cascaded lookup?

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.
  #2  
Old September 5th, 2005, 05:07 PM
Ofer
external usenet poster
 
Posts: n/a
Default

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]

On the after update event of the Make combo, write the code
Me.[ModalComboName].requery


"Ben04Ram" wrote:

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.

  #3  
Old September 10th, 2006, 08:15 AM posted to microsoft.public.access
ahmed
external usenet poster
 
Posts: 34
Default How can I create a form with a cascaded lookup?

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" wrote:

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]

On the after update event of the Make combo, write the code
Me.[ModalComboName].requery


"Ben04Ram" wrote:

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.

  #4  
Old September 10th, 2006, 04:10 PM posted to microsoft.public.access
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default How can I create a form with a cascaded lookup?

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" wrote in message
...
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" wrote:

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]

On the after update event of the Make combo, write the code
Me.[ModalComboName].requery


"Ben04Ram" wrote:

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.



  #5  
Old October 1st, 2009, 04:08 PM posted to microsoft.public.access
Nurse Nancy
external usenet poster
 
Posts: 67
Default How can I create a form with a cascaded lookup?

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" wrote:

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]

On the after update event of the Make combo, write the code
Me.[ModalComboName].requery


"Ben04Ram" wrote:

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.

  #6  
Old October 15th, 2009, 04:35 PM posted to microsoft.public.access
Nurse Nancy
external usenet poster
 
Posts: 67
Default How can I create a form with a cascaded lookup?

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" wrote:

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" wrote:

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]

On the after update event of the Make combo, write the code
Me.[ModalComboName].requery


"Ben04Ram" wrote:

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.

  #7  
Old October 16th, 2009, 05:18 PM posted to microsoft.public.access
Hans Up
external usenet poster
 
Posts: 284
Default How can I create a form with a cascaded lookup?

Nurse Nancy wrote:
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.
  #8  
Old October 24th, 2009, 01:30 AM posted to microsoft.public.access
Nurse Nancy
external usenet poster
 
Posts: 67
Default How can I create a form with a cascaded lookup?

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" wrote:

Nurse Nancy wrote:
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.
.

  #9  
Old October 24th, 2009, 07:24 PM posted to microsoft.public.access
Hans Up
external usenet poster
 
Posts: 284
Default How can I create a form with a cascaded lookup?

Nurse Nancy wrote:
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.
  #10  
Old October 24th, 2009, 07:38 PM posted to microsoft.public.access
Hans Up
external usenet poster
 
Posts: 284
Default How can I create a form with a cascaded lookup?

Hans Up wrote:
Use a list box for Market Regions and set the Multi Select property to


I meant to say an unbound list box.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Requerying a pop up form to display in the main form Jennifer P Using Forms 13 April 5th, 2005 06:59 PM
automatically fill field with form information Tina L. Using Forms 2 January 14th, 2005 08:18 AM
If I create a form with the wizard and later decide I need to add another field do I have to re-create the form? Mister John Doe Using Forms 3 January 2nd, 2005 07:17 AM
How To Create Form with Tabs In Tabular Layout? hylow Using Forms 1 December 17th, 2004 01:02 PM
open a form through a subform in access 2000 Tammy Setting Up & Running Reports 12 October 22nd, 2004 02:43 PM


All times are GMT +1. The time now is 11:51 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.