Clear out dropdown value once another drodown value is changed in vba access?

Question

I have three combo box controls(MFG, Code, and GrpID) in my VBA access form. Once the user selects an option from the first combo box (MFG), rest of combo boxes give me available options. But I need to do some validation i.e. what if the user decided to change the value of first combo box? The values of rest combo box should be cleared. All I need to do is once the first combo box is changed the second and third combo box need to be cleared out or at least set to focus on them so that users will realize that they can't use old values as first value is cleared in the first combo box. I added a code block 'AfterUpdate for first combo box as shown below:

Private Sub MFG_AfterUpdate()
   Code.Value = " "
   GrpID.Value = 0    
End Sub

The problem after writing above code is: they don't get empty until they(Code and GrpID) get clicked. In other words, I need to click them to empty them every time I change the value of MFG. Can anyone direct me how do I clear them or at least focus them?

Maybe you need a Requery? Also, if that event isn't working try the Change Event — Ryan Wildry
I can't replicate this behavior with the data you provided (combo boxes instantly clear when the AferUpdate event fires when I try this). Note that the AfterUpdate event fires when either changing a combo box through the dropdown, or when you tab out of a combo box you changed through typing. Can you please provide more details? — Erik A

Minty Minty · Accepted Answer · 2017-09-20T13:45:41

Set the combo to null to wipe any displayed value in them;

Me.Code = Null
Me.GrpID = Null

This assumes your combo controls are called the same as your field names.

Edit for clarity: Including the Me. makes sure your are changing the form control values. If your field names are the same as the controls then Access will change the underlying field values, and as you have discovered on your form these aren't reflected until you click in that fields bound control.

Clear out dropdown value once another drodown value is changed in vba access?

1 Answers