Hi,
I'm trying to set up three cascading combo boxes. I have the first two set up and working but when I try to add the third I get a blank drop down list. The second combobox is a mutliple row source and for the third, I'm trying to do a single source table. Don't know much about code.

My first box (cboreservoir) has the following code AfterUpdate:

Private Sub cboreservoir_AfterUpdate()
On Error Resume Next
Select Case cboreservoir.Value
Case "Burnt"
cbostructurenumber.RowSource = "tblBurnt"
Case "Cat Arm"
cbostructurenumber.RowSource = "tblCatArm"
Case "Granite"
cbostructurenumber.RowSource = "tblGranite"
Case "Hinds Lake"
cbostructurenumber.RowSource = "tblHindsLake"
Case "Long Pond"
cbostructurenumber.RowSource = "tblLongPond"
Case "Meelpaeg"
cbostructurenumber.RowSource = "tblmeelpaeg"
Case "Snook's Arm"
cbostructurenumber.RowSource = "tblsnooksarm"
Case "Upper Salmon"
cbostructurenumber.RowSource = "tbluppersalmon"
Case "Venam's Bight"
cbostructurenumber.RowSource = "tblvenamsbight"
Case "Victoria"
cbostructurenumber.RowSource = "tblvictoria"
End Select
End Sub



My second box (cbostructurenumber) has the following code AfterUpdate

Private Sub cbostructurenumber_AfterUpdate()
On Error Resume Next
cbostructurename.RowSource = "Select tblstructurename.structurename " & _
"FROM tblstructurename " & _
"WHERE tblstructurename.label = '" & cbostructurename.Value & "' " & _
"ORDER BY tblstructurename.structurename;"

End Sub

My single source table for the third combobox is tblstructurename where [label] is a full list of the structure numbers and [structurename] is the corresponding structure name.

Can anyone help me with this coding? I'm lost at this point.

Thanks

First, I would ask about your table design. It's never a good idea to hard-code like that. From the little I see, it looks like you have separate tables for something like locations where they all capture the same information. What type of database is this? What is it that it will be used for?

This is only a guess, based on my interpretation of the tablenames you provided ...

I suspect that you have one table per (?lake, river, body of water, ...?). If that is a fairly accurate guess, your database would benefit from further normalization. ... and you'd need/want to do that BEFORE working out your cascading comboboxes.

Please post a description of the underlying tables, and what kind of data your storing in them.

Please provide the version of Access you're using.

(by the way, basing a combobox directly on a table doesn't give you much flexability. Instead, consider building a query against the table(s) and using that query as the rowsource for the combobox.)

Good luck!

Here is an example of what sounds like what you want to do, however this only has two combo boxes. After you take a look and I explain, I think you will get the idea.

The First Combo Box uses a Query to get the Distinct Last Names from a Field of a Table. The Second Combo Box also uses a Query to get the Cities the People are from. The second Combo Box then uses the First Combo Box as the Criteria, to only show the City or Cities of the LastName selected. When the LastName Combo Box is changed then the City Combo Box is set to blank and refreshed.

I have separate tables set up with the number of structures (LD-1, LD-2LD-3, etc) in specific reservoirs so tbllongpond has all the structures in long pond reservoir, tblgranite has all the structures is granite reservoir, etc. In trying to get my third cascading combobox set up, I also set up a table that lists all of the structure numbers regardless of reservoir along with the common names of each structure.

To answer Jeff's post, I have a database set up to collect inspection information on dams, with the ultimate purpose being to search on various structures, resevoirs, etc and find outstanding repair works required. I've developed a form for data entry of inspection reports with drop down boxes to populate the main database table with information like year of inspection, recommendation, structure name, structure number, reservoir. Since we have 80 + structures, I didn't want the drop down box to have a list of 80 items, so I developed a table for each of 11 reservoirs and listed the structures in each reservoir so I could point the combo box to a shortened list of structures once the reservoir was selected. So of 11 reservoirs, the user would select say Long Pond in the reservoir box. Then in the structure number box, only those structures in Long Pond would be visible (LD-1 through LD-7). The user would then make the selection. From there, I would like to populate another box that indicates what the common name of that structure is, hence another table with all the structures numbers listed in the label field as well as the common structure names in the structure names field.

I could try a query but I'm thinking I would have to make the entry in the structure number box a parameter to the query and then get that field result back into the structure name entry box.

It would be so much easier for me to just provide a label including all the structures and their common names and make users input the info themselves.
(IMG:style_emoticons/default/iconfused.gif)
I'm using 2010.

Thanks for the ideas. I'll try a few and see if I can't get this to work.

If "normalization" and "relational database design" are unfamiliar terms, plan to brush up on them before expecting to get good use out of Access. I'm still concerned that the table structure of your data base might be more suitable to working with spreadsheets.

Good luck!

As Jeff and I have been pointing out, you want to make sure that your tables are setup correctly. I'm assuming that the table for long pond reservoir and granite reservoir are capturing the same type of information about structures.

Based on not knowing all your information. I'm assuming that you have a list of different reservoirs e.g. Long Pong, Granite, etc. Each one of these reservoirs has structures setup in them. So, that being the case, your basic table design should look something like this.



tbl_Reservoir_Structures would be your main table where you tie reservoirs to structures. This would enable you to add multiple structures for a given reservoir.

Hey RAZMaddaz,

Your method is easier than what I had set up. Thanks (IMG:style_emoticons/default/thanks.gif)

Awesome!!! I'm glad you like my method and I'm glad I could help you!!!

RAZMaddaz
(IMG:style_emoticons/default/thumbup.gif)

Glad you got it working but I would seriously suggest that you normalize your tables and have them setup correctly. The way you have it setup now is not business friendly. This means that down the road if they decide they have additional reservoirs they need tracked or ones they no longer need, it's going to require hours upon hours of work of recoding, creating new tables, etc., which is going to cause them unnecessary downtime whereas if you just have the tables setup correctly, then down the road if they decide to make additions (which is often the case), it's no problem. All they need to do is simply add the new reservoir or structure. Absolutely no coding involved. It's business friendly where it's flexible for their business and they're not having to have all that downtime. It's just as easy, actually easier to do this if you have your tables setup correctly and the benefit of doing this is astronomically better than how it's setup now.

I'll echo Dan's comments -- you may be facing a "pay now (and normalize) or pay later (and keep coming up with work-arounds)" situation.

Thanks for the advice. Problem is that there is a pile of inspection and structure information I imported from an excel spreadsheet, since that was the way it was decided to record all this data originally. I suggested a database would be easier to manage so I have a hybrid type of database set up where I have some normalized tables relating to the reservoirs and unique structure id's but still have to deal with the original data that came from the spreadsheet.

Thanks again for your help.

Just FYI ...

A LOT of Access "databases" start out life as Excel spreadsheets. Just because the data STARTS in Excel doesn't mean you have to force-feed Access that 'sheet data.

A common approach to using Access with Excel data is to:

1) turn off the PC, take up paper/pencil, sketch out the entities and relationships (?unknown terms? brush up! Access 'expects' well-normalized data)
2) use step 1 to design your tables ... note that Excel has NOT come into the picture yet ...
3) link to the Excel data. Consider this the "raw" data.
4) draw a map that shows where the Excel data fits in your well-normalized table structure.
5) build queries that will parse the raw data into those locations.
6) build your Access queries, forms, reports, etc., based on the Access data, not the Excel version.

Good luck!

Jeff's advice is the way to go. The most important part of any database is the tables and relationships and making sure all your data is normalized. Think of it as building a house. You would not just start building the house, No. You would draw out blueprints for how you wanted it built, so you had a plan to follow. The same holds true here. You need to draw out that blueprint of your table structures.

With what you're telling us, I'm assuming that the data in the spreadsheet probably looks something like this.



As Jeff pointed out, you can link to that spreadsheet or even just import it to work with. With the above, you would have 3 tables as I outlined in a previous post. The simplest way to do this would be to create a query just using the "Reservoir" fieldand then select "Group By". This will give you all the unique reservoir names. You would then create a table with this, you would do the same for the structures. Theres more steps than this, which I'm not going to take the time to explain if you're set on having your tables setup incorrectly. If you're open for having the database setup the correct way, let us know and Jeff and I can assist you on doing that.