Cascading ComboBox - Duplicated

[Jrtulare]

Hi folks, so some background first. I have an review database built to review our teams quality when taking calls and emails. I have 1 main database table and 1 main form for both review types. The first Dropdown box lets the reviewer select "Call" or "Email". I then have a Category and a Sub Category, duplicated 5 times just in case multiple errors are made in the same category. The Category and Subcategory are currently using a simple VBA script for the cascading combobox.

My Problem: the set of categories and subcategories are different for call and email. I'd like to set up the Category Combobox to be a cascading combobox that works off of the Audit type, "Call" or "Email". So, if a user selects Email, the Category Boxes (the boxes (all 5) will all display the same selection options) will update to only show the Email options. This sounds simple but i get stuck where we have the possibility of issuing 5 of the same category errors. If I only had 1 category box option i'd be fine, but I have 5. I was only able to figure out how to trigger the After Update event to 1 of the 5 category boxes. How do I get all 5 category boxes tied to the Audit Type After Update event?

CB-Audit Type	Call or Email
CB-Category#1	7 options
CB-Detail#1	options vary
CB-Category#2	7 options, same as #1
CB-Detail#2	Options Vary, same as #1
CB-Category#3	7 options, same as #1&2
CB-Detail#3	Options vary, same as #1&2

thanks

[ItsMe]

You say that ...

The first Dropdown box lets the reviewer select "Call" or "Email".

And you also say ...

The Category and Subcategory are currently using a simple VBA script for the cascading combobox.

Then you say that you need ...

I'd like to set up the Category Combobox to be a cascading combobox that works off of the Audit type, "Call" or "Email".

So you want to have VBA code determine the Rowsource of your combo based on the parent combo's selected value.

Post your VBA code and which events your code is using. If your VBA is using named and saved query objects, include the SQL of the Query Objects, too.

[Jrtulare]

I have two of the five cascading comboboxes set up, both do not include the Main combobox "Audit Type".

here is the vb.:

Private Sub ErrorCategory1_AfterUpdate()
Me.ErrorDetail1.RowSource = "SELECT CallDetail FROM" & _
" TblCallErrorDetails WHERE CallCategory = " & _
Me.ErrorCategory1 & _
" ORDER BY CallDetail"

Me.ErrorDetail1 = Me.ErrorDetail1.ItemData(0)
End Sub


Private Sub ErrorCategory2_AfterUpdate()
Me.ErrorDetail2.RowSource = "SELECT CallDetail FROM" & _
" TblCallErrorDetails WHERE CallCategory = " & _
Me.ErrorCategory2 & _
" ORDER BY CallDetail"

Me.ErrorDetail2 = Me.ErrorDetail2.ItemData(0)
End Sub


So far I'm using the AfterUpdate event on each ErrorCategory combobox to trigger the vba. I'd like to use the AfterUpdate event on the AuditType combobox to trigger a vba that updates each of the ErrorCategory Comboboxes at the same time.

thanks

[ItsMe]

My first impression is that your table structure is violating Rules of Normalization. Because of this, the development of queries and a user interface is difficult and cumbersome.

As I was reading post #1 I thought to myself that you will need to have Email vs. Phone as a top level combo. Then, as I read on, you proclaimed you have this as the top of the hierarchy. If that is so, your lower level combo's RowSources should reflect this.

When I look at the SQL, I do not see multiple columns being selected and I do not see multiple parameters for the WHERE clause. In other words I do not see cascading dependencies. Add to that, the names you are using, ie ErrorCategory1 and ErrorCategory2, and one must conclude the table structure needs work before dependencies can be defined.

This is not a direct answer to your question. But, I feel you are not at a point where your question is a legitimate question. Perhaps you can provide an example of your table structure and an explanation of these five levels in your hierarchy.

[June7]

Multiple fields for same kind of data is not a normalized structure. Whether or not this will cause you issues depends on how you want to analyze and report the data. It is a balancing act between normalization and ease of data entry/output.

[Jrtulare]

It seems that my work system is restricting me from uploading any attachments, sorry about that. Lets see if this helps at all. I have multiple tables but one main table that houses the audit data. This main table has a lot of Lookups to pull form other tables, makes user entry in a form easy.

In this main table (picture layout view) I have Audit Type (its a lookup) (Call/Email) listed once. I then have 5 sets of Error Category and 5 sets of Error Detail. As of now, the Error category field (each 5) use the same lookup to table: TblCallErrorDetails. Error Details is useing the VBA from ErrorCategory to call the ErrorDteail table in the Audit Form. I guess this is what i was calling cascading comboboxes.

The first reason I have 5 sets of Error Category and Error Details is because a user could make multiple errors in one category. Here is an example: user renders two peices of incorrect information in a call, both are in different areas. Error Category1, the user will select Incorrect Informaion. The Error Detail will now only display the Incorrect Informaion detail options. The user selects Incorrect Purchase Price. The second incorrect information will be listed in Error Category2, the user will select Incorrect Informaion. The Error Detail will now only display the Incorrect Informaion detail options. The user selects Incorrect Product ID.

The second reason for the 5 is that a user could make up to 5 errors in one transaction and we need to list each one.

I thought about creating table and form structures for both types (call/email) but thought it would be easier to work with one main table.

I'm pretty new at all of this so if you all have a better way to set this up i'm all ears and more then willing to learn. It may not be pretty now but the entry of audits works great and I'm able to pull reports using queries and such. This being said, if there is a better way i'm willing to change.

thanks

[ItsMe]

Sometimes it makes sense to create a non-normalized structure. I have seen examples of audit and also survey applications that do this. For instance, a survey will only have five questions and each question is represented by a column in a table.

Even if this non-normalized structure is suitable for you application, I would try to make it fit as a normalized structure first. In other words, it is unlikely you will be able to do everything with one large table or even only two or three tables.

For your case, you have an additional issue. It seems you are trying to analyze an existing operation. Hard to explain what I am talking about, but you need to pass judgment on something else. This something else could be defined as its own operation or even database. You might consider looking at it as, on one hand, you have a help desk and this help desk has its own database and its own application. Now, on the other hand, you need a second database and a second application that judges the first, help desk, application.

I have come across these situations before. In a perfect world, you would have an existing model, the model of the help desk. Then, you would use that existing model to define entities that are important for the second data model. Then, you would define additional entities that do not exist in the first model but are necessary for the second model. Because of this perfect world scenario, it will be difficult to define an Entity Relationship Diagram for the second model. If you do not have the help desk model to reference, you will struggle to define some sort of foundation.

You need to get your entities defined and understand how to apply business rules to your ERD.

Maybe a 15 minute video will help provide a high level view of an ERD.
https://www.youtube.com/watch?v=-fQ-bRllhXc