Trouble with Cascading ComboBoxes

[shane201980]

Ok, so I'm fighting Cascading (Dynamic) Combo Boxes.

I originally created a form to input contacts which has 3 combo boxes cascaded together; this works like a charm (zero issues). Now I'm creating a contact edit form to retrieve the contact info for editing purposes. My new form was copied from the original input form with some adjustments to fit my requirements.

Problem 1: If I use the form as it is, only the first combo box populates with the value stored for the contact. The other 2 boxes are blank, and their drop selections are null (No Options). I've figured out that this is due to the rowsource not being set, so I have now set my RowSource to the correct table and fields as necessary. All the boxes now populate the values stored for the contact. Which leads to problem 2...

Problem 2: Now that the information is populated in the appropriate combo box, I might want to change some values. If I select any one of the boxes, they show the list of options depending on the upper box controling the table/query. For example, I select a new item in combo box 1. The other combo boxes clear as they are suppose to since I've made a change, but when I go to the second combo box to make my next selection and hope to see values for based on the first box; I get null.

Here's and example of the after_update code from the first combo (Ignore the txt... items, they are my way of handling some wildcards items):

Combo Boxes are AF1 / AF1_2 / AF1_3


Private Sub AF1_AfterUpdate()
Me.AF1_2.RowSource = "SELECT SecondaryID, [Secondary Title] FROM " & "tbl2Secondary WHERE MainID=" & Me.AF1 & " ORDER By [Secondary Title]"
Me.AF1_2 = Me.AF1_2.ItemData(-1)


Me.AF1_3.RowSource = ""
Me.txt1stManual = ""
Me.txt1stManual2 = ""
If IsNull(Me.AF1_2.ItemData(0)) Then
Me.AF1_2.Visible = False
Me.AF1_3.Visible = False
Me.txt1stManual.Visible = True
Me.txt1stManual2.Visible = True
Else
Me.AF1_2.Visible = True
Me.AF1_3.Visible = True
Me.txt1stManual.Visible = False
Me.txt1stManual2.Visible = False
End If
End Sub

Any ideas would be a great help at this point, Thanks!

[June7]

Try requerying dependent comboboxes in the AfterUpdate event:
Me.Af1_2.Requery

Not sure makes a difference, but the SQL statement is missing semi-colon at the end. Access might add this in for you, but best to just include it.

You are setting textboxes to empty string. Are these textboxes bound to field? Do you want empty string saved in field? I don't allow empty string in tables.

Want to provide db for analysis? Follow instructions at bottom of my post.

[rpeare]

June's correct, every time you change the contents of one of your combo boxes you have to refresh the query running all subsequent boxes in the cascade to properly show/hide values that should/should not be visible.

so in terms of your code

Private Sub AF1_AfterUpdate()
    
Me.AF1_2.RowSource = "SELECT SecondaryID, [Secondary Title] FROM "  & "tbl2Secondary WHERE MainID=" & Me.AF1 & " ORDER By  [Secondary Title]"
    Me.AF1_2 = Me.AF1_2.ItemData(-1)
me.af1_2.requery
    Me.AF1_3.RowSource = ""
me.af1_3.requery
    Me.txt1stManual = ""
    Me.txt1stManual2 = ""

    If IsNull(Me.AF1_2.ItemData(0)) Then
         Me.AF1_2.Visible = False
         Me.AF1_3.Visible = False
         Me.txt1stManual.Visible = True
         Me.txt1stManual2.Visible = True
    Else
         Me.AF1_2.Visible = True
         Me.AF1_3.Visible = True
         Me.txt1stManual.Visible = False
         Me.txt1stManual2.Visible = False
End If
End Sub

[shane201980]

Wow, I'm such a noob... lol!

My problem was that I set the RowSource with 2 different formats. In the Rowsource properties, I set the rowsource with 3 columns so that I could use the criteria to filter the results by ID. However with the AfterUpdate event, I only produce 2 columns and filter by the ID. The information was there, I just need to format both rowsources the same so they show with the columns view for the combo box. (Hope that makes since, I think I confused myself.)

Thank you both, I will make the corrections as listed too. I'd never have found that without re-evaluating the coding. And the text boxes are bound to another field in the database that stores wildcard inputs. Unfortunately I can't control all the inputs by the users as they have some special circumstances.

"Sometimes you just have to look right in front of you!"