Im sure this is simple, but not sure what it might be called to search for it.

Looking to create a form with a drop down and then when you select one of those items it open another drop down to pick an item from.

And on the reporting side of it, is there a way to have a report "recalculate" qty's based of the second drop down menu?

The purpose of this is so that we can track faults. For example:

Dropdown 1: Transmission Tires Clutch

After selecting Transmission, you'd be prompted with another drop down with: "Bad syncros" "bad 1st gear" bad 2nd gear" Under Tires "left front" "right front"..and so forth.

Much thanks in advance for any replies!

What you want to search for is "cascading combo" - there are many examples here on this site!

Yes, the usual way to do this is via a technique known as Cascading Combo Boxes. There is a good example on Candace Tripp's web site, web page.

Not having any luck...can someone please have a look here at my sample db?

Anytime I select a Country, nothing "pops up" in the City. And all my Cities are listed even though I select a Country :sigh:

Edited by: lefthooked on Tue Jan 6 13:28:09 EST 2009.

Left,

Part of this is going to depend on your table structure.

You should have something like:

tblComponents
ComponentID
Component (Tire, Transmission, etc)

tblMalfunctions
MalfunctionID
Malfunction (Left Front, Left Rear, Bad gear, etc)
CompnentID

Your first combo you set tblCompnents as it's recordsource, two columns, widths to 0;1(or whatever to properly display the user friendly text), bound to column 1

the second combobox will use tblMalfunctions as it's recordsource to start with, then click the build button, add all fields from the table to the query grid. under ComponentID enter =Forms!YourFormName.YourFirstComboboxName

Set your columns to 2, widths 0 and whatever is needed again to show the text.

Back at combobox 1, use the onclick event to requery the second combobox

Me.SecondComboboxName.Requery

That should get you rolling.

J

JVanKirk, can you have a look at the file I attached?

I have fixed it. The major problem is that you were using different names in the code than the controls actually had. For instance, you were using cboCountry in the code but the actual name of the combo box is Combo12. Also you were doing a Select Case on the value in the SECOND column of the combo box not the first column, which is a number from 1 to 3.

Anyway, it is attached.

Much thanks. I will have a look closer at this code so I can understand it more.

Ok, so I thought I understood how to do it so I tried in my (copied) current DB that I'll be adding this to...here is my code:
Private Sub cboMainCAT_AfterUpdate()
On Error Resume Next
Select Case cboMainCAT.Value
Case 1
Combo414.RowSource = "tblIGBT FAILURE"
Case 2
Combo414.RowSource = "tblCONTACTOR DAMAGED"
Case 3
Combo414.RowSource = "tblSCR/DIODE DAMAGED"
End Select
Combo414.Requery

End Sub

Private Sub cboCombo416_AfterUpdate()
On Error Resume Next
Select Case Combo416.Value
Case 1
Combo416.RowSource = "IGBT FAILURE"
Case 2
Combo416.RowSource = "CONTACTOR DAMAGED"
Case 3
Combo416.RowSource = "SCR/DIODE DAMAGED"
End Select

End Sub

Both Combo Boxes bring in the data correctly..however, same as above is happening. No matter what I select from MainCAT combo, everything is listed in my Combo416 box.

Any thoughts? Or do I need to post more information..?

:bangsheadondesk:

Can someone chim in and give me a pointer here..cant seem to figure it out.

Getting nowhere quick with this.

I fixed your other one by changing the names in the code window to the actual names of the controls on the form. Have you checked the names, are you sure they are correct? Not just the control names, but the table names as well?

Left..you're going about it all wrong. i redid your attachment. You would not use a seperate table for each Country to hold the cities for that country...imagine the massive amount of tables you could end up with, or the redesign issues all the time...check out the attachment to get an idea of how this is accomplished and read a few of these links, they will do you worlds of good!!

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• DataModel.org: Rules of Data Normalization
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables

Let us know if you have more questions after checking out the attachment and reading a couple of links.

J

JVanKirk, great read/links. Took me a while to get thru all of them, but things make alittle more sense now.

I have everything work, no I have more more question:

With selecting from the new drop downs, I'd like these selections to be saved for reporting purposes. Not sure how to do this.

How to get these drop down selections to save to a table?