Populate combo box based on previous combo box selection

Topic

New Reply

Hi all,
I am trying to populate a second combobox which depends on selection in first combobox. To this end I have created four tables:

categoryType – This table has the data for the first combo box and contains three records, Expenses, Income and Refunds

categoryRef – This table contains about 10 records and the data should be displayed when Refunds is selected in the first combo box

categoryInc – This table contains about 15 records and the data should be displayed when Income is selected in the first combo box

categoryExp – This table contains about 20 records and the data should be displayed when Expenses is selected in the first combo box

Is the above the best way to achieve what I am trying to do? Are there any alternatives?
I need this to be fairly low maintenance as there may be a need to add/edit/delete records from any of the tables from time to time.

Thanks in advance.

Bill

Reply | Quote

Replies

I think the easiest way is to have 3 combo boxes, one each for Refunds, Income, and Expenses. Then based on the Type (in the forms Current event and in the AfterUpdate event of cboType), you just change which of the 3 is visible.

The alternative is to have 1 combobox for all 3 Types and change the rowsource in those same events.

Reply | Quote

Thanks Mark.
If I understand you correctly, I don’t think the first solution will work. Seems a little clunky having to change which of the three is visible. I was hoping to select one of the three categoryType options and then the appropriate options will become available for selection depending on what of the three options were selected in the first field. I’m having a little trouble understanding where your fits in there. However I am a newbie and I sure it’s my lack of understanding.

How would your second suggestion work?

Thanks again for your help.

Bill

Reply | Quote

Bill,
I don’t know why you think this is so difficult. The code to determine which to make visible is this:

cboRefund.Visible = (cboType=”Refund”)
cboIncome.Visible = (cboType=”Income”)
cboExpense.Visible = (cboType=”Expense”)

Reply | Quote

Bill,
I don’t know why you think this is so difficult.

Because it’s a while since I’ve touched Access and some of the terminology is killing me:-)

Thanks for your advice on the code. Much appreciated. Where do I put it?

Reply | Quote

Bill,
I don’t know why you think this is so difficult. The code to determine which to make visible is this:

cboRefund.Visible = (cboType=”Refund”)
cboIncome.Visible = (cboType=”Income”)
cboExpense.Visible = (cboType=”Expense”)

Mark – Why would you recommend having three controls, when one synchronized combo box should do the job just fine? With separate combo boxes, a person would need to make form design changes every time they needed to add a new Categorytype. For example, if your customer needed a new Categorytype of “Investments”, any such design should allow the user to do this by adding records to tables only. I do not see that this would be possible by having dedicated combo boxes, with code to control visibility. The fact that your solution involves any code means that it would only work in a trusted location, similar to the synchronized combo boxes sample I provided…

Reply | Quote

Tom,
Thanks for your assistance. Fantastic.

I have a trip in hospital in front of me for the next few days, but will put your suggestion in place when I get back.

Have a good day, you have just made mine:-)

Bill

Reply | Quote

Hi Bill,

> Is the above the best way to achieve what I am trying to do? Are there any alternatives?

No and yes to your two questions. Having similar information (catagory subtype) in three different tables is not in accordance with database normalization rules. The much better alternative is to have a categoryType lookup table, as you do, and a categorySubType table that includes all of the data in the three tables. You will relate the data by creating a relationship between the primary key of the categoryType table and the foreign key of the categorySubType table. You can use either a text-based primary key in the categoryType table (ie. the actual data: Expenses, Income and Refunds), or a meaningless autonumber (surrogate) primary key. I prefer using autonumbers myself. I’ll use an autonumber primary key (pk) and Long Integer foreign key (fk) in my example.

tblCategoryTypes
pkCatType (autonumber / set as primary key)
CategoryType

tblCategorySubTypes
pkCatSubType (autonumber / set as primary key Note: Pretty much every table should have a primary key.)
CatSubTypeName (this would likely be the same data that you have in the three tables).
fkCatType (long integer. If you are using Access 2003 or earlier, remove the default value of 0).
plus any other fields that describe attributes specific to category subtypes.

The fkCatType field will contain values from the pkCatType field in the tblCategoryTypes table.

Here is an example, from the Northwind sample database for Access 2002 and 2003 (the same logic works in any version of Access). This example looks to use numeric keys:

How to synchronize two combo boxes on a form in Access 2002 or in Access 2003
http://support.microsoft.com/?id=289670

In your case, the field “CatSubTypeName” would be used in place of ProductName in the Northwind sample, I think something like this [untested “air code”]:
[Code]
Me.cboCatSubType.RowSource = “SELECT CatSubTypeName FROM” & _
” tblCategorySubTypes WHERE fkCatType = ” & Me.cboCategories & _
” ORDER BY PCatSubTypeName
Me.cboCatSubType = Me.cboCatSubType.ItemData(0)
[/Code]

In the above example, I have used a commonly used naming convention, “cbo”, as part of the names of two combo boxes on your form. Try working the example out first, in the Northwind sample database. Then try mimicking this example in your database, that has table and field names specific to your application.

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp → http://www.xoc.net/standards/rvbanc.asp#Access

Using a Naming Convention
http://msdn2.microsoft.com/en-us/library/aa164529(office.10).aspx

Reserved Words
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Reply | Quote

Wow, this is more difficult than I had hoped. Probably beyond my meager abilities.
Thanks all so much for your help but I may have to think of another way of achieving what I’m after.

Bill

Reply | Quote

Hi Bill,

Implementiong a synchronized combo box is really not that difficult, if you spend some time working with the Northwind sample database. I agree that implementing proper database design can be difficult, especially for beginners, but doing so will pay dividends in the future. There is lots of good information on the topic of database normalization:
.

Database Design Tips by Michael Hernandez:
http://www.accessmvp.com/JConrad/downloads/DatabaseDesignTips.zip

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Good Luck[/FONT]

Reply | Quote

Arhh!

Perhaps someone can see where I have gone wrong.

In the attached db, in the Category Management form I am trying the populate the Category combo box depending on the selection in the Type combo box. I can’t get it to work.

Anyone see where I have gone wrong?

Thanks heaps.

Bill

Reply | Quote

Thanks Tom, appreciate your encouragement.

I will give it another go, but I find Microsoft’s Help and accompanying explanations quite frustrating and in the end demoralising.

Will see how I go.

Thanks.

Bill

Reply | Quote

Hi Bill,

Feel free to post a compacted and zipped sample, that shows your efforts, if you run into problems. Of course, remove any sensitive data first, or just create a new database importing just the bare minimum to show the functionality. Note: I have a personal preference for the .mdb file format.

Reply | Quote

Standby….I’m looking at your database now.

Reply | Quote

Thanks Tom.

Reply | Quote

Hi Tom.

Thanks heaps for your efforts with this. However there is one problem.

No matter what is selected in the Category Type field, the same options appear in the Category field. I was hoping that only each relevant category would be displayed whenever either Income, Expense, Refund were selected in the Category Type field.

Can this be done?

Thanks.

Bill

Reply | Quote

Hi Bill,

It sounds to me as if you are running your database in an untrusted folder, thus, the AfterUpdate event procedure I mentioned is not being run. Please refer to this article for more information:

Dealing with the Trust Center (Access 2007 only)
http://www.accessmvp.com/TWickerath/articles/trust.htm

As an experiment, try removing the RowSource for the lower combo box entirely (it is currently a SQL statement: “SELECT pkCategoryID, Category FROM Category;”. I suspect the symptom you will now observe, with the VBA code disabled, is that no matter which CategoryType you select (Expenses, Income or Refunds), the lower, dependent, combo box has no records.

Your Category table has 56 records total; (49) are classified as Expenses, (5) are classified as Income, and (2) are classified as Refunds. Here is what I see when I run the revised sample I provided (in a trusted folder location, of course):

Expenses (49 records):
31162-Expenses

Income (5 records):
31163-Income

Refunds (2 records):
31164-Refunds

49 + 5 + 2 = 56 records, as found in your Category table. Later tonight, I can post an amended sample, which includes an Autoexec macro that helps alert the user if they are running the database in an untrusted location. I’ll also add an ORDER BY statement, to the SQL in the AfterUpdate event procedure, as I see this morning that I missed that little detail (Category records are currently not shown in alphabetical order all the time).

Reply | Quote

Tom,
I just opened Access, Opened the file you corrected, Enabled All Content through the Trust Centre pop up box, and the form worked perfectly. Thanks heaps.

This forum is fantastic.

Bill

Reply | Quote

Hi Bill,

I hope your trip to the hospital goes great, and that it is nothing too serious.

I have made some improvements to this sample:

[*]Added the ORDER BY clause, as discussed earlier. Now, the Categories should always display in alphabetical order.
[*]Added an Autoexec macro that came from John Viescas and Jeff Conrad’s Access 2007 book, “Microsoft® Office Access(TM) 2007 Inside Out“. The purpose of this macro is to detect if the folder is trusted. If it is not trusted, display a message to the user.
[*]Determined that you have (6) duplicate Category entries. This was evident, as soon as I added the ORDER BY clause. I created a new Grouped query, named “z_qryDuplicateCategoryEntries”, to reveal these duplicate records. So, after removing the duplicates, you should have (50) Category records. I left the duplicates for now, so that you can see how this new query works.

Note: I name informational queries, that are not used as rowsources or recordsources for other objects, with the leading “z_” prefix. This way, they sort to the bottom of the list, and I know for certain that I can change the query without affecting any other functionality.
[*]I forgot to mention this last night, but I added a Referential Integrity (RI) constraint to your existing relationship, between the Category and CategoryType tables. A relationship without RI is little more than an exercise in drawing lines. I also set the fkCategoryType field, in the Category field, as Required. This will prevent the possibility of entering a Category, but having the corresponding fkCategoryType value remain null (unknown).
[*]In order to prevent the problem outlined in the third bullet, above, with duplicate entries, you can add an index to the Category field, as follows. However, you will need to remove the (6) duplicate records first:
.
Indexed: Yes (No Duplicates)
.
If you need the same Category available for more than one CategoryType, then use a combined field index such that the combination of Category + fkCategoryType cannot be duplicated.

Please see new attached sample.

Happy Learning!

Reply | Quote

Hi Bill,

The first two changes I made have nothing to do with the combo boxes, but I consider important nonetheless:
1.) Disabled Name Autocorrect.
2.) Disabled the option that allows one to make design changes to a table in Datasheet view.

Name Autocorrect (AKA Name Autocorrupt) has always been buggy. I recommend always disabling this “feature” in all Access databases. I guess I’m just a purist, but it violates my senses to have the Add a column feature available in datasheet view. I have nicknamed this feature the denormalization wizard, since it encourages new users to add fields to tables, without necessarily thinking through the database design.

In the Category table, you had a field named fkCategoryType, but this field was a text data type, and had the same values as the lookup table, CategoryTypes. The text data would only be appropriate if you were using text-based primary and foreign keys. I deleted this field, and renamed the empty field with this name. I then populated this field with the appropriate numeric values. Note: I’ve already forgotten the original name of the 4th empty field, and at this point, I would have to restart a virtual machine, with Access 2007 installed, just to quote the original fieldname correctly.

Your two combo boxes were named “Text0” and “Combo2”. I renamed these to “cboCategoryType” and “cboCategory”, respectively. I also reset the bound column of the Text0/cboCategoryType combo box from 2 to 1. This way, when one selects a value in this top combo box, the bound column will be the first column in the rowsource, which is the numeric primary key.

Make sure to enable code in your database now, since the first combo box, cboCategoryType, now has an AfterUpdate event procedure. This code will not run unless the database is in a trusted folder (Thank You, Microsoft! [NOT]).

Hope this helps,

Reply | Quote

Hi Tom,
Just got out of hospital, thanks for your wishes and apologies for the delayed response. Knee replacement operation, a procedure I hope never to have to repeat.

Thanks for the revised db and efforts in helping me. The end result is exactly where I hoped to be.

You will see a bit of me from time to time from now on. As part of my recovery from my operation I have set myself a goal of learning Access and have started on this db as part of that process. Hope not to be too much of a pain.

I appreciate your help.

Bill

Reply | Quote

Hi Bill,
Isn’t it amazing how modern medicine allows doctors to replace worn out parts, like knees and hips?

You’ve likely got lots of rest and recuperation time now, so no time like the present to start working on your goal. I hope you have a few good books available for reference.

Best wishes for a speedy recovery.

Tom

Reply | Quote