MS Access: Dynamic comboboxes

How do I make a set of comboboxes whose list-of-choices can change (lookup by query?) according to what has been chosen in the others?

Example:

If I create three combo boxes...
[country] [city] [town]

When someone hits "country ", they should see a list of half a dozen countries. Easily achieved by linking to a lookup list of table "countries ".

But here's the catch... how do I make it so that if "New Zealand" was chosen for country, suddenly only New Zealand cities will appear in the next [city] combobox... but if "Australia" was chosen, suddenly the combobox for "city" would be dynamically altered to only include Australian cities.

...next task of course, once the city is chosen, "town" should likewise be narrowed down.

Please help me on this. I've been RTFM-ing MS-Access for days and am still stumped (complete with a "still at square one" headache).

 

I think you need a macro. I took a quick look at my Running Microsoft Access 2000 book (ISBN 1-57231-934-8) and it looks like it explains how to do something similar. But its too complicated to explain in this forum.

Its a good book. You may want to pick it up if you are going to spend your time building databases. I think its worth every penny I paid for it.

 

I kind of got it with the following, but there are still a few bugs. Most problematic is that I can't search through records... I can, but every now and then data appears invisible.

Dynamic Comboboxes Tutorial - Alpha release 0.0.1 - Feb 5 2003
==============================================================

This tutorial is an attempt to familiarise myself with creating dynamic comboboxes in Microsoft Access. This is where a set of comboboxes change according to what is selected. For example, if the first combobox contains, "animal ", "vegetable" and "computer ", the second combo box will have choices of "cat ", "dog" and "rodent" when "animal" is selected from the first. Otherwise, if "vegetable" is selected from the first combobox, the second will change its contents to "fruit ", "herb" and "spice ".


==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==

Licence / Terms for using this Document
=======================================
This tutorial is released under GNU Public Licence (GPL). This means you can copy it, change bits you don't like, add to it and give it out to all your friends. You can even make money by charging a fee while using the information in this document as a teaching aid. However, under GPL, if anyone requests that you give them a copy of this document free of charge, you must comply. Likewise, any modifications you make working from this information must also be available free of charge. You may agree to sell information in this document, or for modifiactions you have made for a profit, as long as you make it clear that the information will be offered for free and without restriction or hesitation, should other parties request it.
This licence must remian part of the document and also included on projects using this information in their own making.

==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==GPL==


On with the tutorial...

First up, we need some information to go into the database. Let's work from the following...

<Root>
|
|__animal
| . |
| . |__cat
| . | . |_tiger
| . | . |_lion
| . | . |_panther
| . |
| . |__dog
| . | . |_wolf
| . | . |_alsation
| . | . |_doberman
| . |
| . |__rodent
| . . . |_rat
| . . . |_mouse
| . . . |_rabbit
|
|__vegetable
| . |
| . |__fruit
| . | . |_banana
| . | . |_orange
| . | . |_apple
| . |
| . |__herb
| . | . |_thyme
| . | . |_parsley
| . | . |_basil
| . |
| . |__spice
| . . . |_curry
| . . . |_tumeric
| . . . |_cinnamon
|
|__computer
. . |
. . |__operating system
. . | . |_linux
. . | . |_win32
. . | . |_mac-os
. . |
. . |__application
. . | . |_word processor
. . | . |_spreadsheet
. . | . |_database
. . |
. . |__hardware
. . . . |_scanner
. . . . |_monitor
. . . . |_floppy

The above are to be categorised as "category" => "group" => "item "

CREATING THE INITIAL DATABASE
=============================
There are several ways to do this. You could just enter the data as raw tables, or you could make nice tidy data entry forms. The second option is the best because those forms, while cumbersome to make at first, are useful for later updates, and there's less likelyhood of dataentry error when unit-ID's are autonumbered directly from the forms.

Start a new database, save as "funnythings.mdb "


CREATING TABLES:
================

Make New Table in Design View. Give two fields...
categoryID (AutoNumber) as Primary Key
categoryName (Text)
...on closing, save table as "category "

Likewise, make two more tables saved as "group" and "item "

Table: group
groupID (AutoNumber) as Primary Key
catID (Number)
groupName (Text)

Table: item
itemID (AutoNumber) as Primary Key
grpID (Number)
itemName (Text)

Relationships ==========================
Once all tables are closed & saved, hit the relationships button. add all three tables to the view, and link...
* categoryID to catID (choose to enforce referential integrity)
* groupID to grpID (choose to enforce referential integrity)
Close the relationships section, opting to save layout changes when prompted.
========================================

Finally make one more table in design view. Give it four fields...
* choiceID (AutoNumber) - Primary key
* chosencat (Number)
* chosengrp (Number)
* chosenitm (Number)
Save the table as "choices ". This is where all the records are going to be stored during data entry.

CREATING DATA ENTRY FORMS
=========================

For some bizarre reason, my autoform wizards only work about 25% of the time, even though I'm on a 1.2Ghz machine, a clean install of Win98-SE and only a few apps installed (nothing heavy... I haven't even set up the internet). Oh well - manual construction of forms works pretty well every time, so here I go...

Category dataentry form:
Create new form in design view. Base it on the "category" table. Slide the "categoryName" fields onto the canvas as a text box. You do not need the "categoryID" field. When closing, save the table as "newcategory "

Group dataentry form:
Create a new form in Design View based on group. From the drag'n'drop field window, you only need to add the field "Group Name ". You will also need an option to choose which category the new group is under (when you do create the new group "dog ", you will need to specify that it belons to the category "animal "). It is neater to create a pull down list than having a text box (which is why you haven't added the field from the drag'n'drop field window... this would have given you a plain textbox).
From the toolbox, add a new combobox (again, I normally have the wizard button down, but *sigh* I guess I'll have to do this one manually as well)
Control Source => CatID
Make sure "Row Source Type" is down as Table/Query. Under "Row Source ", hit the three dots and add the category table to the SQL Statement view. Have "CategoryID" and "categoryName" as two columns. If done correctly, "SELECT category.categoryID, category.categoryName FROM category; " should now bw written into the "Row Source" field.
Make the following changes to change the combobox (pulldown list) to a more friendly format than displaying raw ID numbers...
* Set Column Count to 2
* Set Column Widths to 0 ";1 "
Rename the field lable from "Combo1:" to "Category: "
Optional: because you've added the combobox after the initial textbox, go the tab order is altered, so hitting the ENTER key after making a new field will refocus onto the pulldown list. Change the TabIndex of the groupName textbox from 0 to 1
On closing, save the form as "newgroup "

Item dataentry form:
Create new form in Design view, based on the "item" table.
Slide on the item field (as textbox) from the field list window.
Add new combobox from toolbox window. Select grpID as its Control Source.
Make sure "Control SourceType" for the combobox is set to Table/Query and hit the three dots at the end of "Row Source ". In the SQL Statement view, add the "group" table. Next add "groupID" and "GroupName" as columns for the SQL Statement. This will result in "SELECT group.groupID, group.groupName FROM [group];" being written into the "Row Source" property for the combo box.

To make the combobox have userfriendly pulldown list items...
* Set Column Count to 2
* Set Column Widths to 0 ";1 "
Optional: change the Tab Index of the itemName textbox from 0 to 1
On exit, save the form as "newitem "

ENTERING THE DATA INTO THE TABLES
=================================
Open the newcategory form in Form View, ready for data entry. Enter each of "animal ", "vegetable" and "computer" before closing the form.

Open the newgroup form in Form View.
selecting "animal" from the pull down list each time, enter records for "cat ", "dog" and "rodent "
Do the same for...
pulldown: vegetable => "fruit ", "herb" and "spice "
pulldown: computer => "operating system ", "application" and "hardware "

Open the newitem form in Form View
Under "cat" (combobox pull down list) add entries for "tiger ", "lion" and "panther "
Enter other data as in tree diagram at the beginning of this tutorial.


...And now, for our feature presentation....

(continued...)

 

(continued from above [forum restriction on size of single post])

...And now, for our feature presentation....



MAKING A FORM WITH DYNAMIC COMBOBOXES
=====================================

Create a blank new form under Design View. Base the form on "choices" table.
Add three comboboxes.

First of all, you should rename (Name field under properties) the combo boxes to "cb1 ", "cb2" and "cb3 ", and then relable them "Category (cb1): ", "Group (cp2):" and "Item (cb3):" respectively.

For properties of the each combobox, set Column Count to 2, and the Column Widths field to 0 ";1" This will make them more userfriendly whne choosing tems. They will list literal names instead of ID numbers.

First Combobox (cb1):
This combobox is not dynamic. It just pulls items straight from whatever is in the "category" table. Under properties of combobox cb1, hit the down arrow of "Row Source" and choose "category "

It's probably a good time to save the form. Call it "chooser "

Second Combobox (cb2):
Hit the three dots after "Row Source" for cb2. Add group table to view.
Add two columns, groupID and groupName.
Add a third column, CatID, as invisible. Right click on the Criteria field and Build...
In the left dialog box, open folders...
Forms => Loaded Forms => chooser =>
Now double click cb1 in the middle dialogue box. This will enter "Forms![chooser]![cb1]" into the top text area.

This will give you...
SELECT group.groupID, group.groupName FROM [group] WHERE (((group.catID)=[Forms]![chooser]![cb1]));

...in the Control Source of cb2 (this is automatic, you shouldn't have to write it out but if you do, you can skip the steps to get to this point)

That's pretty much it for combobox-cb2, except... when you try it out, it's not working. Closer inspection would reveal the cb2 combobox is working, but it's not refreshing its contents when the cb1 combobox is changed.

Hit the "Event" tab under properties of cb1. Click the three dots after "After Update" and choose "code builder ". You will get a sheet wher you can add the middle line as shown...

Private Sub cb1_AfterUpdate()
DoCmd.Requery "cb2 "
End Sub

Test it and it should be working...
Two boxes down - one to go

Click the three dots after "Row Source" on properties for combobox-cb3.
Add the "item" table to the SQL Statement view.
In order, add the columns itemID, ItemName and grpID
in the Criteria: row under the grpID column, right click and choose "Build... "
In the "Build..." session on the left dialogue, open the folders as so:
Forms => Loaded Forms => Chooser
...and then, in the middle dialogue box, double click on cb2, which will give you "Forms![chooser]![cb2]" in the top text area.
Just before closing the SQL Statement view, uncheck the tickbox under grpID column (this will make it invisible)
Go back to the cb2 combobox and add an "After Update" event as follows...

Private Sub cb2_AfterUpdate()
DoCmd.Requery "cb3 "
End Sub

Now it's working

...at least as far as the dynamic comboboxes it's working. To get make the form useful, you have to have data recorded somewhere. Link the Control Source property of each of the three comboboxes as follows...

cb1 => chosencat
cb2 => chosengrp
cb3 => chosenitm

Now you're done


More questions ========================

Problem#1 (an important one)
I cannot use the nav bar (bottom) to search through the records because the informations for fields seems to disappear eratically. Closer inspection (easy to see by showing the form in Datasheet view) reveals that while the form is currently selected on category 3 (computer), all other records will be made invisible (perhaps by the combobox query script?) All data is recorded securely, it just becomes invisible on the form itself. How can I make it so that scrolling through "next record" doesn't make some fields appear as invsible?

Problem #2
There's a problem in that if I change my mind about a Criteria (change from "animal" to "vegetable ", the second combobox (cb2) resets, but the last one (cb3) does not. Adding the code...
Private Sub cb1_AfterUpdate()

DoCmd.Requery "cb2 "
DoCmd.Requery "cb3 "
End Sub

...does not reset both records. Any ideas?

Problem#3:
My recording table contains all 3 comboboxes, however I am really only interested in obtaining the one. Is there a way of making the form remember what was chosen for each individual form, without having to store the cb1 and cb2? (space saver)

========================================

Credits
=======
This tutorial has been made following a question I placed on a forum website at http://www.access-programmers.co.uk/forums

Thanks to "ghudson" and "Oldsoftboss" for assisting in working towards a solution to my original question, and to "chewy" for prompting me to investigate the subject in a more thorough manner.

Lancer.

P.S: Sure wish this forum would allow me to post the final database from above as a zip file. Oh well.