Populate combo box based on previous combo box selection

[tgw7078]

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):
Expenses.gif

Income (5 records):
Income.gif

Refunds (2 records):
Refunds.gif

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).

[tgw7078]

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

[bnorthby]

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

[bnorthby]

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

[tgw7078]

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!

[bnorthby]

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

[tgw7078]

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