dynamic comboboxes (1 Viewer)

[Lancer]

How do I make a set of comboboxes whose contents 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.

...then of course, once the city is chosen, "town" would likewise be narrowed down.

Please help me on this. I've been RTFM-ing MS-Access for days and am still stumped... just a newbie.

 

[ghudson]

My FilteredComboBoxes sample should show you just how easy it is to do what you want. My sample is querying the table based on the selected state. Then the city combo box is filtered (query criteria) for only the selected state. Then the county combo box is filtered for only the selected city. Ensure that you are requerying the combo boxes that you will be selecting "next" so that you will have the correct data listed. Check out the Row Source for each combo box and also the Properties for each query for I have selected Unique Values within the query to avoid listing duplicate values. I am using the AfterUpdate event of each combo box to requery the data source of the next combo box.

HTH

 

[Oldsoftboss]

http://users.impulse.net.au/predator/accessdownloads.htm has several access examples that may help.
Dave

 

[Lancer]

Wow! Thanks both of you

I've already posted this and other Access questions to various MS-Win forums, and so far Access seems to be avoided. Now at last I know where I can get some help on the one MS-software package that still elludes me.

Quick answers too.

ghudson ... I have played a little with your DB... it's good I'm currently examining it piece by piece, making a new version to suit my own purposes. Thanks heaps for getting me past the rutt.

Oldsoftboss Thanks for the link to those downloads. I've RTFM'ed a few books on Access but none have explained things like Cmd Refresh which was part of this problem. Examples are good for that.

Hopefully tomorrow, I'll be able to reverse engineer this to make my real "big objective" database utilise it.

 

[Oldsoftboss]

Glad to help
Dave

 

[chewy]

ghudson

how do you inplement that I cant figure out how

 

[Lancer]

I figured it out chewey, with help from a few. Here's a slow step-by-step on how it's done...

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

Dynamic Comboboxes Tutorial - Alpha release 0.0.3 - Feb 7 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".

Contents - This tutorial is in the form of...
readme 0x0x3.txt (this document)
dynacombo.mdb (example database in Access 2000 format)
dyncom97.mdb (the same database converted to Access 97 format)

==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==


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 "dynacombo.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.

Okay - that's all I have room for in a single post to this forum.

(More to come...)

 

[Lancer]

(continued from above)

...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"
[cb2] = 0
DoCmd.Requery "cb3"
[cb3] = 0
End Sub

This seems like quite a bit of code for one button, so make sure you understand it. The DoCmd.Requery "cb2" is the most important line, which refreshes the contents of the pull down list / options of combobox cb2. The next line, [cb2]=0, causes the second combobox to lose its data whenever the first is altered. The lines involving cb3 likewise reset the third combobox. In other words, if the main category (cb1) is ever altered, the other fields must be wiped of old data.

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"
[cb3] = 0
End Sub

...these lines refresh & reset combobox cb3 whenever data is altered in cb2.


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

Important: You must associate a table with each of these fields or data will be lost as you navigate from one record to the next.

There's one more navigation problem to fix. When you've made a few records, searching through previous records has an illusion that some field values seem to disappear. This phenomenon is actually caused because the fields are not refreshed when hitting the navigation arrows, and old values are left in the comboboxes. To force refresh each time a prev / next form is looked at, perform the following...

* make sure the Properties window is showing.
* Click on the main form (probably twice) until "Form" is shown in the titlebar of the Properties window. This should also have "On Current" as the first field under the "Event" tab.
* Click the three dowts after "On Currwnt" then choose "Code Builder"
* Make the new procedure as follows...

Private Sub Form_Current()
DoCmd.Requery "cb2"
DoCmd.Requery "cb3"
End Sub

...the effect here is that everytime the form page is renewed (nav bars at bottom to show "prev / next record) the comboboxes, cb2 and cb3 get refreshed. Combobox cb1 does not need the refresh as it does not rely on querying valid pull down list to obtain its data.


Revision History
================
5/02/03 - 0.0.1 alpha release. Tutorial complete with issues highlighted, such as the problem of database forgetting fields when navigations bar used to move through form to form.
7/02/03 - 0.0.3 alpha release. Questions from previous version now fixed. This release made in haste without bothering for much proof reading (hence, "unstable" version 0.0.3 instead of 0.0.2)

Please email me on zxen1ov02@sneakemail.com for any bugs / feedback on this tutorial. Help me improve readability in the next version


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. Thank you "AccessPhil" for pointing out the useful "On Current" event property.

Lancer.

Apologies about the length of this post. The forum is not allowing me to add a renewed attachment after deleting the first (updated this post) so I'll add the (fully working this time) example database in a post a bit further down...

 

[Oldsoftboss]

That would have to be the longest reply I've ever seen !!

 

[Lancer]

Here's a zipfile of the working database from the tutorial above.

 

[Oldsoftboss]

Thats a far better idea
Dave

 

[Lancer]

Cheers Dave.

The new attachment is a modified (updated) version of the post that was up before. I fixed certain parts that were giving strange results when using form nav buttons.

 

[Oldsoftboss]

How is the Wuthu in New Zulund these days? Looking forward to kicking your butt in the World Cup
Dave

 

[croby]

The tutorial - Dynamic Comboxes ?

Hi,

I clicked the link filteredcomboboxes.zip (from ghudson), which led me to Lancer’s message page. Here I found a Tutorial --Dynamic Comboboxes . I don’t know who wrote this tutorial.(there are many names on that page.) It is pretty good. I followed the samples and made my 3 Combos (System, Subsytem and Major as I posted on 2/28/03) work.

Now my questions is, how to store my entered data into a table? The tutorial mentioned about this, but I still did not get the way to do it.

I want to store the data in to a table called IC. It has 4 fields: ic_ID, ic_System,ic_SubSystem and ic_Major. They are all Number fields. Then I opened the 3 Combos’s property sheet. Under the Data tab I found the Control Source. I filled in the 3 field names of the IC table. Then all the 3 Combos become Bound control. When I run the form. I can no longer select the data in the drop down list. ACCESS displays a message:

“Control can’t be edited; it’s bound to unknown field ‘ic_System’.”
(When I use Code builder, the field name becomes an expression [ic]![ic_System])

The tutorial said, “ Link the control Source property of each of the three comboboxes as follows….”. How to LINK? I’m sure I did not do the right LINK.
Please help. I send my great appreciation to you all. Chris

 

[Copeland]

i followed the example from ghudson to create these dynamic combo boxes (using a single table as my source). I select a category from the first combo box, and then i get a prompt to "select parameter value." if i re-type the category i selected then i get the correct information in the second combo box. any thoughts on why this is happening? any help on this is greatly appreciated!!! Thanks.

 

[CFP - Russell]

Hello, sorry to dig up this old thread but does anyone have the dyncombo.zip file that Lancer posted.


Thanks,
Russell

 

[Oldsoftboss]

Doesn't the link above work ?

 

[astra]

Hi there,

Sorry to dig up an old thread... But I am hoping for a quick resolution.

I have pretty much a similar situation. Trouble is I am within a subform.. So

[Forms]![FormParent]![cboStatus] is not working
nor is [Forms]![FormChild]![cboStatus]


Please help at the earliest.

Regards,

Varkey

 

[Oldsoftboss]

Try:

Forms!FormParentName.Form!FormChildName!cboStatus

Dave

 

[astra]

Thanks a tonne.... It worked...