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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 07: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 06:17 AM |
How To Create Form with Tabs In Tabular Layout? | hylow | Using Forms | 1 | December 17th, 2004 12:02 PM |
open a form through a subform in access 2000 | Tammy | Setting Up & Running Reports | 12 | October 22nd, 2004 02:43 PM |