Cascading lists continued

[Kassie]

In response to my previous post, titled cascading lists, pietlinden suggested
a solution as offered by http://www.mvps.org/access/forms/frm0028.htm, adding
on that I should place my categories and subcategories in one table, indexing
Categories and allowing duplicates.

I tried this solution, but cannot get it to work!

I work in tblDecisions, and use an input form to input data into this table.
As stated previously, the fields in this table include inter alia the
following:
DecNr - Number;
DecDate - Date;
DecCat - I changed this to text;
DecSubCat - Text;
DecRevAsk - Date;
DecRevTaken - Date; and
DecAct - Yes/No

I then created a table - tblCategory, with fields Cat and SubCat, with
field Cat indexed as
Dup OK, and entered a list of categories and subcategories as you suggested.

On the input form, I have inter alia cbxCat and cbxSubCat, which would
obviously then
not get info from tblDecisions, but from tblCategories? Be that as it may,
if I set cbxCat's RowSourceType to Field list, and
RowSource to tblCategory, then when I click on the combobox, I see "Cat" and
"SubCat", instead of a listing of the categories. If I select Cat, I get a
listing of Categories under cbxSubCat - all instances of each category. If I
select SubCat, I get a listing of ALL the sub categories under cbxSubCat.

If I set the RowSourceType for cbxCategory to value list, and click on the
down arrow, I get "tblCategory". cbxSubCat obviously remains blank.

If I set the RowSourceType to Table/Query, and click on the down arrow, I
get a listing of all the records under Category in tblCategory, and not an
indexed list. If a category has 6 sub categories, I will see 6 instances of
that category. If I then select a category, and click the down arrow of
cbxSubCat, I get an inputbox "Enter Parameter Value" with the name of the
category I selected in cbxCat,above the textbox, and with OK and Cancel
buttons below the textbox.

By the way, I am using Acces 2002. Am I totally on the wrong track here, or
should I stick
to what I know, Excel, where this is a very simple and easy thing to do!
The church wants this in a database, rather than Excel, and I am merely
trying to oblige, but it is becoming a pain in the butt exercise!

Regards

 

[Magius96]

Just to restate what I believe your asking...You want multiple combo-boxes on
your form, so that when you select a value on the first combo box, it limits
the values visible in the rest, and when you select a value on the 2nd
combo-box, it limits the rest, and so forth.

If that's what you need, you don't even have to write any VBA code for it.
Instead, create the form, open the properties dialog for each combobox
control, and edit the "Row Source" property using the "..." button. (thus
opening the query builder). On secondary combo-boxes you can reference back
to the forms other combo-boxes to limit the results.

 

[Kassie]

Not quite. This form has several text boxes, and two combo boxes. The
selection made in the first combobox should determine the options available
in the second combo box. I have created a table with fields Cat and SubCat,
to be used to populate these two boxes. What I need to know, is how to
reference back to the first combo box.

 

[OssieMac]

Hi Kassie,

I am by no means an expert on Access but I thought from your answer that you
might like a step by step explanation of how to set up cascading combo boxes
so that the drop down displays are limited by the previous combo selection.
The following explanation is to set up the Dropdowns for Town, State and
Postcode that are all in the one table. As you can imagine some towns appear
multiple times due to the same names in different States. The States all
appear a multitude of times as do the Postcodes.

Table name is Town_State_Postcode
Fields are: Town, State, Postcode

Set up Town Combo box.
Properties:
Rowsource Type: Table/Query
Rowsource: Click the 3 dots at end of line to open Query builder.
(If a table has already been selected at Rowsource, the next 4 steps are
omitted)
Show table dialog displays.
Select the table (Town_State_Postcode) and click Add.
Table fields display in top left of query builder.
Close the Show table dialog box.

Drag Town field down to first Field in matrix.
Click in Sort row of matrix and select the drop down arrow.
Select Ascending.
Right Click in the large blank area at top to right of where table fields
display.
Select properties.
Set Unique values to Yes.
Close Query properties dialog.
Close the SQL Statement: Query Builder and Select Yes to save.
Close the Combo box properties.

Set up State combo box.
Repeat above up to “Close the Show table dialog box.â€
Drag State field into FIRST column of matrix.
Set State Sort to Ascending.
Drag Town into second column of matrix.
Uncheck the Show box under Town.
Right Click in the large blank area at top to right of where table fields
display.
Select properties.
Set Unique values to Yes.
Criteria under Town should be like the following but you can use the Build
wizard if you like to create the criteria for the SQL filter part of the
statement.
[Forms]![MyForm]![Town]
Where Town is the combo name where you select the Town.

Set up Postcode combo box.
As above but this time Postcode goes in the FIRST field, Town in the second
and State in the third.
Set Unique values to Yes. (As above)
Only Postcode should have Show box checked and sort Ascending.
Set Town criteria identical to previous.
Set State criteria to [Forms]![MyForm]![State]

While still in Design mode, do the following for each of the combo boxes or
they don’t work properly.

Select a Combobox -> Properties -> Event tab.
On Enter property click the three dots at the right.
In Choose builder select Code builder then OK.
The sub name and end sub that will run as soon as the combo is selected will
be created.
Insert the following code:

Me.Recalc

This forces the form to update the queries ready for you to make the
selection.
If you would like the drop down to display as soon as you click in the combo
without having to click the drop down arrow then insert the following after
the above line.

Me.Town.Dropdown

Repeat the above for State and Postcode.

 

[Kassie]

Thanks for the advise, Ossie Mac! I will try it and get back to you. I
really appreciate your effort

--


Kassie Kasselman
Change xxx to hotmail

Hi Kassie,

I am by no means an expert on Access but I thought from your answer that you
might like a step by step explanation of how to set up cascading combo boxes
so that the drop down displays are limited by the previous combo selection.
The following explanation is to set up the Dropdowns for Town, State and
Postcode that are all in the one table. As you can imagine some towns appear
multiple times due to the same names in different States. The States all
appear a multitude of times as do the Postcodes.

Table name is Town_State_Postcode
Fields are: Town, State, Postcode

Set up Town Combo box.
Properties:
Rowsource Type: Table/Query
Rowsource: Click the 3 dots at end of line to open Query builder.
(If a table has already been selected at Rowsource, the next 4 steps are
omitted)
Show table dialog displays.
Select the table (Town_State_Postcode) and click Add.
Table fields display in top left of query builder.
Close the Show table dialog box.

Drag Town field down to first Field in matrix.
Click in Sort row of matrix and select the drop down arrow.
Select Ascending.
Right Click in the large blank area at top to right of where table fields
display.
Select properties.
Set Unique values to Yes.
Close Query properties dialog.
Close the SQL Statement: Query Builder and Select Yes to save.
Close the Combo box properties.

Set up State combo box.
Repeat above up to “Close the Show table dialog box.â€
Drag State field into FIRST column of matrix.
Set State Sort to Ascending.
Drag Town into second column of matrix.
Uncheck the Show box under Town.
Right Click in the large blank area at top to right of where table fields
display.
Select properties.
Set Unique values to Yes.
Criteria under Town should be like the following but you can use the Build
wizard if you like to create the criteria for the SQL filter part of the
statement.
[Forms]![MyForm]![Town]
Where Town is the combo name where you select the Town.

Set up Postcode combo box.
As above but this time Postcode goes in the FIRST field, Town in the second
and State in the third.
Set Unique values to Yes. (As above)
Only Postcode should have Show box checked and sort Ascending.
Set Town criteria identical to previous.
Set State criteria to [Forms]![MyForm]![State]

While still in Design mode, do the following for each of the combo boxes or
they don’t work properly.

Select a Combobox -> Properties -> Event tab.
On Enter property click the three dots at the right.
In Choose builder select Code builder then OK.
The sub name and end sub that will run as soon as the combo is selected will
be created.
Insert the following code:

Me.Recalc

This forces the form to update the queries ready for you to make the
selection.
If you would like the drop down to display as soon as you click in the combo
without having to click the drop down arrow then insert the following after
the above line.

Me.Town.Dropdown

Repeat the above for State and Postcode.

 

[Kassie]

Ossie

You may not see yourself as an expert, but you know Access! Thanks mate,
this works like a charm!

--


Kassie Kasselman
Change xxx to hotmail

Hi Kassie,

I am by no means an expert on Access but I thought from your answer that you
might like a step by step explanation of how to set up cascading combo boxes
so that the drop down displays are limited by the previous combo selection.
The following explanation is to set up the Dropdowns for Town, State and
Postcode that are all in the one table. As you can imagine some towns appear
multiple times due to the same names in different States. The States all
appear a multitude of times as do the Postcodes.

Table name is Town_State_Postcode
Fields are: Town, State, Postcode

Set up Town Combo box.
Properties:
Rowsource Type: Table/Query
Rowsource: Click the 3 dots at end of line to open Query builder.
(If a table has already been selected at Rowsource, the next 4 steps are
omitted)
Show table dialog displays.
Select the table (Town_State_Postcode) and click Add.
Table fields display in top left of query builder.
Close the Show table dialog box.

Drag Town field down to first Field in matrix.
Click in Sort row of matrix and select the drop down arrow.
Select Ascending.
Right Click in the large blank area at top to right of where table fields
display.
Select properties.
Set Unique values to Yes.
Close Query properties dialog.
Close the SQL Statement: Query Builder and Select Yes to save.
Close the Combo box properties.

Set up State combo box.
Repeat above up to “Close the Show table dialog box.â€
Drag State field into FIRST column of matrix.
Set State Sort to Ascending.
Drag Town into second column of matrix.
Uncheck the Show box under Town.
Right Click in the large blank area at top to right of where table fields
display.
Select properties.
Set Unique values to Yes.
Criteria under Town should be like the following but you can use the Build
wizard if you like to create the criteria for the SQL filter part of the
statement.
[Forms]![MyForm]![Town]
Where Town is the combo name where you select the Town.

Set up Postcode combo box.
As above but this time Postcode goes in the FIRST field, Town in the second
and State in the third.
Set Unique values to Yes. (As above)
Only Postcode should have Show box checked and sort Ascending.
Set Town criteria identical to previous.
Set State criteria to [Forms]![MyForm]![State]

While still in Design mode, do the following for each of the combo boxes or
they don’t work properly.

Select a Combobox -> Properties -> Event tab.
On Enter property click the three dots at the right.
In Choose builder select Code builder then OK.
The sub name and end sub that will run as soon as the combo is selected will
be created.
Insert the following code:

Me.Recalc

This forces the form to update the queries ready for you to make the
selection.
If you would like the drop down to display as soon as you click in the combo
without having to click the drop down arrow then insert the following after
the above line.

Me.Town.Dropdown

Repeat the above for State and Postcode.