Cascading Comboboxes

[DMerchen]

I am really struggling to wrap my head around how to make cascading comboboxes work. I am working on a database for returned products, and I need to break down as best as possible what the problem is. I thought the best way to do this would be cascading comboboxes. What I am trying to do is have an initial combobox select a problem, then the next populates based on the first, and the pattern follows through 5 tiers to break down the problem. What I am having difficulty with is how this information is saved to the table if it is all done with comboboxes. I need to have a record of these selections. Can you have a table set up with all of your tiers, have comboboxes query this table, and have the values saved to another table? How will this information be saved if it is not saved to a table. I can get the comboboxes to work on a form, but that is only running a query, how does this information get saved? Any help is greatly appreciated, and I hope I have explained my problem clearly.

Thanks,
Dave

 

[GinaWhipp]

Hmm, a wee bit more information needed... (And, yes, you can do that... save the information in a seperate table.) Since it sounds like it's not working for you we need to see your table set up. It will also help to know what Business (or Business Rule) you are trying to follow.

 

[DMerchen]

Perhaps that is where I need to start. Would you suggest all of the data in one table. I can give you a bit of information and hopefully that would help. We work with Diode Lasers, and these would be returns from the customer that failed for some reason. The first tier would be performance, physical, and peripherals. Based on this selection the next tier would populate. For example Performance would populate the next tier with electrical, optical, and thermal. I have attached an excel file with the separate tiers for better understanding. Thanks for the response.

Dave

 

[GinaWhipp]

WELL, that is pretty good, I got it as soon as I opened the spreadsheet! So, do you have any tables yet? If so, what are they?

 

[DMerchen]

I guess this is where I am struggling. I have seen 2 different methods. The first is to have all of the values for each tier in separate tables, and then a more widely suggested method of putting everything into one table. If this is the case, I am not sure if I have the primary problem in the first column, the second tier in the next and so on. For example, would I put 01 Performance-Electrical-Short Circuit then the next one 02 Performance-Electrical-Open Circuit, etc.

Dave

 

[GinaWhipp]

Hmm, not sure who would recommend all in one table because *that* would be a properly normalized database AND would give you problems down the road, so forget that. Each *level* needs it's own table... Now, do you plan to store Customer Information? (Before I *mock* something up, I'd like to know exactly what information you want to store that is *not* on your spreadsheet.)

 

[DMerchen]

Yes, we have a whole string of customer information, test requests, etc in the table. If you mock up something simple though, it might just get me running in the right direction. Unfortunately, I inherited this database and it has been through a lot, but I am trying to add some functionality without killing the old information. I really appreciate your assistance.

Dave

 

[GinaWhipp]

No problem, however I am running out the foor in minutes. So don't feel like I abandoned you. I'll be back!

 

[DMerchen]

No problem. I am just grateful for the assist and hopefully learning something I can use more in the future.

 

[GinaWhipp]

Sorry for the delay but I finally finished! Here's my initial draft...

tblReturn
rReturnID
rCustomerID
rDate
rReturnReasonID (FK, relate to tblReturnReasons)
rReturnReasonTypeID (FK, relate to tblReturnReasonTypes)
rProblemID (FK, relate to tblProblems)
rProblemTypeID (FK, relate to tblProblemTypes)
rProblemAreaID (FK, relate to tblProblemAreas)
rProblemAreaDescriptionID (FK, relate to tblProblemAreaDescription)
etc... (Balance of fields)


‘Your Level 0 thru Level 1
tblReturnReasons
rrReturnReasonID
rrReturnReason
rrSortOrder
rrActive

‘Your Level 2
tblReturnReasonTypes
rrtReturnReasonTypeID
rrReturnReasonID (FK, relate to tblReturnReasons)
rrtReturnReasonType
rrtSortOrder
rrtActive

‘Your Level 3
tblProblems
pProblemID
pReturnReasonTypeID (FK, relate to tblReturnReasonTypes)
pProblem
pDescription
pSortOrder
pActive

‘Your Level 4
tblProblemTypes
ptProblemTypeID
ptProblemID (FK, relate to tblProblems)
ptProblemType
ptDescription
ptSortOrder
ptActive

‘Your Level 5
tblProblemAreas
paProblemAreaID
paProblemTypeID (FK, relate to tblProblemTypes)
paProblemArea
paDescription
paSortOrder
paActive

‘Your Level 6
tblProblemAreaDescription
padProblemAreaDescriptionID
padProblemAreaID (FK, relate to tblProblemAreas)
padProblemAreaDescription
padDescription
padSortOrder
padActive

Please note I wasn't sure of the actual names of tables I did the best I could. I did note what Level I was refering to! Hope this helps...

 

[DMerchen]

Wow!! I was not expecting this!! Incredible. It will take me a bit to work through this, but I think I am understanding some of it. So is there any VBA code that goes along with this? OnUpdate, etc? Oh, help me out- -What is FK? Thanks for the assist!

 

[GinaWhipp]

Thanks... Initial draft only but by your reaction I say I must be pretty close. Those are your tables that will hold the data according to what level they are on. Here's what I would do so we can work on it together...

Before incorporating into your *live* database, create these tables in a seperate database. (I would have done so but I have to get some paid work done ) Fill them with the data according to what Level is specified. If you can create the Relationships then upload and I'll work on some queries for you. Once done and we all happy you can import into your live database.

Almost forgot...

FK - Foreign Key

A FK get related to the PK (Primary Key) in the corresponding table. Still not sure? Don't create the relationships ans when you upload I'll create them and you can study them.

 

[DMerchen]

Thanks for the explanations. FK makes sense now. I completely understand getting some real work done, I need to put a little time into another project myself, so it will take me a little bit to get back to this. I appreciate your help working together on this. I will put together the tables and do as much as I can and then upload. That is much better for me, so I can learn how this is going to work while going through the process. Then you can critique my work!!

 

[GinaWhipp]

No worries, I'll be here when you get back!

 

[DMerchen]

Here is the database with the tables. I did not set up any of the relationships, and I hope I got the data types correct. I did not enter any of the information either, my other real work is tying me up more than I hoped. Please let me know anything I could have done differently, better, or just missed.

P.S. We are using Access 2010, hopefully this does not cause any grief.

Dave

 

[GinaWhipp]

I will looking at a little later. And no, Access 2010 is not a problem and THANK YOU for posting the version!

 

[GinaWhipp]

Okay, here you go BUT I got a little confused on the spreadsheet. The fields in orange, I'm not 100% sure what table they go in. All the other data is filled in. If you would be so kind as to fill in those few values to where they go OR is another table needed?

Have a great weekend!