Combo Box not Refreshing (1 Viewer)

[Alansidman]

I have three combo boxes on a form. The result of selecting the first two should dictate the available results in the third one. It works fine the first time you open the form. But when you clear the form and attempt a second selection, it holds the values from the first selection in the third combo box. I have tried a requery and a refresh on the third combo box in the afterupdate event. That did not work. The only solution that seems to work right now is to close the form and re-open it. While that gets the right answer, it is not the solution to the problem. File is attached. What am I missing to make it work?

 

[VilaRestal]

Why not just set the rowsource of the third in the afterupdate events of the other two?

Private Sub MyColor_AfterUpdate()
    MyAnswerRowSource
End Sub

Private Sub MySelector_AfterUpdate()
    MyAnswerRowSource
End Sub

Private Sub MyAnswerRowSource()
    MyAnswer.RowSource = "SELECT ID, cID, fvID, Desc FROM Table3 WHERE cID=" _
            & Nz(Me.MyColor, 0) & " And fvID=" & Nz(Me.MySelector, 0)
End Sub

And there's surely no need for the middle two columns (both hidden and both constant in the list).

 

[bob fitz]

Hi Alan,
I think you need
Me.MyAnswer.Requery
in the After Update event of both combo boxes.

 

[Alansidman]

Maybe I mis-understood, but I get this message (attached)when I attempt to do that and then execute the first combo box.

Checking out the alternate solutions since this posting. Will post back in a few minutes.

 

[VilaRestal]

By keeping the expression method?

One of many reasons to prefer VBA over expressions. Always do it the VBA way. The fact that you're having to call Forms!... shows it's inefficient and means if the form name changes it breaks. Likewise if control names change it breaks but it doesn't tell you how and why, which VBA would. And it's easier to read the VBA and control the flow - such as handling null values.

 

[Alansidman]

Tried both suggestions and both failed. Have either of you tested this on the sample db, I attached? Logically, I understand what is happening, but it fails for me.

 

[VilaRestal]

Yes mine works fine for me. I'll upload it

 

[bob fitz]

Yes, I have. It seems to work.

 

[Alansidman]

Guys,
I have downloaded both of your files and tested them. They fail if you try to clear the filter and then re-enter new data. The answers do not come back with the correct solutions. It appears (as in my case) to only work correctly upon entering the values after loading the form.

 

[VilaRestal]

I have tested doing just that on mine and it works numerous times through:

Fruit > Red > Apples, Raspberries, Strawberries
Clear Filters
Fruit > Green > Beets, Tomato
Clear Filters
etc
etc

Do you get an error message?

 

[VilaRestal]

And Bob's works too after clearing the filters.

They both definitely work fine. Correct filtered in list in third combobox no matter how many times I clear the filters and reapply new ones.

 

[Alansidman]

Thanks guys. My bad. I set up the table with the wrong colors for some of the items. Therefore, I was getting (in my mind) bad data. I like both ideas. Thanks for getting me over this hump.