Displaying a list in a combo box based on a choice from 1st combo box

[Heeeeelp]

Hi,

I would appreciate some help.

I have a subform called DII_ContractsItems with two combo boxes, cboChapter and cboSection.

The cboChapter box displays a list of chapters from the tblChapters2011. Here is the sql for that combo box.

SELECT tblChapters2011.Chapter, tblChapters2011.CategoryID, tblChapters2011.ItemID
FROM tblChapters2011
ORDER BY tblChapters2011.Chapter;

The cboSection combo box displays a list of sections which come from the table named tblChapterSections2011. Here is the sql
SELECT tblChapterSection2011.Section, tblChapterSection2011.CategoryID, tblChapters2011.CategoryID
FROM tblChapters2011 LEFT JOIN tblChapterSection2011 ON tblChapters2011.CategoryID = tblChapterSection2011.CategoryID
ORDER BY tblChapterSection2011.Section;

I would like the cboSection list to be limited based on the selection a user picks in the cboChapter combo box. The two tables, tblChapterSection2011 and tblChapters2011 are linked by the CategoryID field.

I would greatly appreciate some help with to code this.

Kind regards,
Tess

 

[MajP]

Click on the FAQ button at the top of the thread. There are several faq on this.

 

[Heeeeelp]

Thank you MajP

I followed the steps from FAQ. The first CBO works fine. However, regarding cboSection, i am getting the following message:
Enter Parameter Value
Forms!DII_ContractsItems!cboChapter

The form DII_ContractsItems is a subform. Can you please tell me what is the right way to make this call as i'm guessing that's what the problem is.
Also, which property do I check to make sure I have the right name for the forms?
Thank you,
Tess

 

[CharlieT302]

Hi Tess,

There are several methods for doing this.

Assuming the sections are based upon the chosen chapter?..

In cboSection, use the "Row Source" property field to create a relational query that links both tables (used by each combo box).

On the criteria row, specify a criteria for the Chapter field that references the cboChapter combo box name (the name is found on the Other tab of the Property Sheet box).

Ex: [forms]![MainFormName].[cboChapter]

When you close the query, say yes to saving the SQL code. Then move to a different property in the property box. Access will sometimes erase your changes if you don't move out of the property line.

The name of the subform is obtained in the "Name" property of the Other tab. First pull up the Property sheet. Then click off the subform and then click it's top ruler only once. This places sizing handles around the form and reveals the subform name. If you click a second time, the name is not visible.

Does this help.

 

[TheAceMan1]

How are ya Heeeeelp . . .

Post the [blue]Row Source[/blue] for cboSection ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Replace this:
Forms!DII_ContractsItems!cboChapter
with something like this:
Forms[!]![name of main form][/!]!DII_ContractsItems[!].Form[/!]!cboChapter

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Heeeeelp]

Hello Aceman...I am well ty, hope you are as well.

Thank you everyone for your help, I really do appreciate it.

I've redone the subform hoping I'd catch an error but I'm still having problems with the Section box (cboSection). I can select the chapter from the cboChapter are control but when i click on cboSection, i am still getting the enter parameter value box popping up.

The SQL code for cboChapter is:

SELECT tblChapters2011.CategoryID, tblChapters2011.Chapter
FROM tblChapters2011
ORDER BY tblChapters2011.Chapter;

The SQL code for cboSection is:

SELECT tblChapterSection2011.ID, tblChapterSection2011.Section, tblChapterSection2011.CategoryID
FROM tblChapterSection2011
WHERE (((tblChapterSection2011.CategoryID)=[Forms]![DII_Contracts]![DII_ContractsItems].[Form]![cboChapter]))
ORDER BY tblChapterSection2011.Section;

Thank you in advance,

Tess

 

[dhookom]

You must understand that you use the name of the subform control which is not necessarily the name of the subform object. Typically the two are the same but DII_ContractsItems may not be the name of the subform control on the main form DII_Contracts.

[Forms]![DII_Contracts]![red][DII_ContractsItems][/red].[Form]![cboChapter]))


Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Heeeeelp . . .

Your criteria for [blue]cboSection[/blue] is dependent on [blue]CategoryID[/blue]. The problem is [blue]CategoryID[/blue] is the 3rd column in your select statement, whereas [Forms]![DII_Contracts]![DII_ContractsItems].[Form]![cboChapter] returns the bound or 1st column ... and since we can't use the [blue]column[/blue] property in a query we'll have to wrap the 3rd column in a function. So in a module in the modules window, copy/paste the following function:

[blue]Public Function curCat() As Long
   curCat = Forms!DII_Contracts!DII_ContractsItems.Form!cboChapter.Column(2)
End Function[/blue]

This changes the SQL to:

[blue]SELECT ID, Section, CategoryID
FROM tblChapterSection2011
WHERE ([CategoryID] = [purple][b]curCat()[/b][/purple])
ORDER BY Section;[/blue]

Perform your testing ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Heeeeelp]

Thank you everyone. I did have the incorrect subform name. I am able to select the chapter now and when i go to cboSection, it does display the correct sublist based on the chapter that is selected from cboChapter. However, when I try to enter a new record for the same client, and I select a different chapter, it automatically removes the section i have entered in the previou record.

Will the code you suggested Ace, help with this problem or is something else causing this problem?

Thank you again everyone.

Tess

 

[TheAceMan1]

Heeeeelp . . .

I was afraid of this, as I seem to detect it earlier on. Too explain ...

[blue]In datasheet view or continuous view, the Query/SQL of a combobox or listbox works for all![/blue]

To explain ...

Lets say our 1st combo is based on animal types:
Birds
Cats
Dogs

... and our 2nd combo filters according to the 1st.

So in a new record in the type combo you select [blue]birds[/blue]. And in the second you select eagles ...

We goto a new record and in the 1st we select [blue]Dogs[/blue]. Since the query for types is no longer [blue]birds[/blue], and the query determines whats displayed for all, ... how does the 2nd combo display any birds if none are returned!

See my point ...

The best you can do at this point is to make the combo's unbound (by placing them in the header or footer of the form), and replacing the combo's with textboxes. The combo's update the tetxboxes! You may have to go back to the table level to acomplish this.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I have seen solutions where a text box is placed over top of the combo box. I prefer to display all the values in the second combo box but sort them so the correct ones are on top. Consider add zzz to the non-conforming values. Then use the after-update event to notify users they have made a poor selection.

Duane
Hook'D on Access
MS Access MVP