Combo Box

[tbassngal]

I am trying to populate cboSubDefect with Values based upon the selection from combo box cboDefectDescription and it is not working...

I have two tables. One is tblDefectDescription where Defect Description is the only field - that populates cboDefectDescription. I have another table tblSubDefect which has two fields. Defect Description and subDefect. tblDefectDescription has a one to many relationship with tblsubDefect.

As an example, cboDefectDescription=Envelope not Sealed, I would want to see the values associated with that condition which would be Envelope missing glue or Machine malfunction as my list choices for cboSubDefect.

Additionally, I don't even want the box to be active if there are no subDefects. I want the value to be *.

Please help!

Private Sub cboDefectDescription_AfterUpdate(Cancel As Integer)

Me.cboSubDefect.RowSource = "SELECT SubDefect FROM" & _
" tblSubDefect WHERE [Defect Description] = " & Me.[cboDefectDescription] & _
" ORDER BY SubDefect"

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

[ssanfu]

This is called cascading combo boxes.

Private Sub cboDefectDescription_AfterUpdate(Cancel As Integer)

Me.cboSubDefect.RowSource = "SELECT SubDefect FROM" & _
" tblSubDefect WHERE [Defect Description] = " & Me.[cboDefectDescription] & _
" ORDER BY SubDefect"

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

Because you are using a text field to 'link' the two tables, the value from cboDefectDescription needs to be delimited with quotes. Look closely at the following WHERE caluse:

Private Sub cboDefectDescription_AfterUpdate(Cancel As Integer)

   Me.cboSubDefect.RowSource = "SELECT SubDefect FROM" & _
        " tblSubDefect WHERE [Defect Description] = '"  &   Me.[cboDefectDescription] & _
        "' ORDER BY SubDefect"
                      
    'the requery may not be necessary, but I use it
    Me.cboSubDefect.Requery      
    Me.cboSubDefect = Me.cboSubDefect.ItemData(0)
End Sub

Additionally, I don't even want the box to be active if there are no subDefects. I want the value to be *.

Which "box" shouldn't be active?



Note: you shouldn't use spaces in object names. It will cause you headaches when you create queries, forms and/or reports.

[tbassngal]

Thank you so much, I'll give it a shot! Yes, I know how the naming convention of fields and table names works best... unfortunately, I didn't develop this database and have inherited it from someone who just dabbles in MS Access. Thank you so much, I am eager to try your solution!

P.S. I don't want the second combo box active unless there are SubDefect values associated with the Defect Description... cboSubDefect inactive ?

[tbassngal]

Still having problems =(

The same error message as before:

"The expression After Update you entered as the event property setting produced the following error: procedure declaration does not match description of event or procedure you are having the same name"

While I would like the procedure to work, I would also like cboSubDefect to display only values associated with the value in cboDefectDescription. Else, I would like the cboSubDefect inactive.

This code works for the query when I hard code the SQL but I can't get my combo boxes to accept it, please help!!

SELECT tblSubDefect.SubDefect
FROM tblSubDefect
WHERE (((tblSubDefect.[Defect Description])="I-02 Envelope not Sealed"));

[ssanfu]

Attached is an A2K db with an example of what (I think) you are trying to do.

I don't like changing the combo box row source each time a new Defect is selected because it makes it a little harder to troubleshoot. But, I did it your way .

Anyway, it seems to give the correct results.......

[tbassngal]

Thank you again for your help but when I open this database, the cboSubDefect box is always inactive and doesn't allow for a drop down box... I see the tables look exactly as I have them set-up in my db, but I don't see that your combo box is working either? Again, I cannot tell you how much I appreciate your help...

[ssanfu]

I set the enabled property for cboSubDefect to FALSE in design view so it will be disabled until an item is selected in cboDefectDescription.

You have to select something in cboDefectDescription before there will be any items in cboSubDefect. Having cboSubDefect disabled by default lets you know that there were no items to select.

If you don't like it, in design view, set the ENABLED property to TRUE.

[tbassngal]

I select something from the drop down but nothing else happens, it stays dimmed out. You know what else is strange about my copy of MS Access, when I put a break in to debug, it never stops at the code so that I can walk through... additionally when I remove the single quote to watch the SQL statement, it never prompts me with it.... so strange. What in the heck is going on? I sure do appreciate your patience.

[ssanfu]

Have you tried converting the A2K db to A2K7 format?

Other things to try :

*Compile and Repair

*In design view, select each combo box, click on the event tab, (is Event Procedure visible) click on the ellipsis, does it connect to the correct sub?

[tbassngal]

I have converted the database you gave me for the combo box and my own with no success. When I open the database that you give me, the combo box stays dimmed out, it is never activated. Additionally, its almost like the code just won't run for me. When I put a break in it, it never lets me debug. I am starting to wonder if I have a restricted version of MS Access or something, maybe some attribute is not turned on... references? And yes, I do have the ellipsis and it does take me to the correct sub when I click. This is so frustrating... I am at a new company with a new computer and have never had these difficulties before.

[ssanfu]

I just downloaded the zip and didn't have any problems.

Is the A2K file in a trusted location?

You could recreate what i did in a new A2K7 mdb, using mine as a guide. New form, combo boxes, add the code, etc.

Nothing special about it.... didn't even add a reference to DAO.