can't reset combo box

[AlanS]

A table consists of four fields, all of which must be included in the key. The AllowZeroLength property for all four fields must remain set to No because of the requirements of the application.

On a subform based on this table, two of those fields are bound to combo boxes Combo1 and Combo2. The RowSource of each of those combo boxes is an SQL statement, and the relationship between the fields is such that the WHERE clause of Combo2's SQL statement refers to the current value of Combo1.

All works fine, except when the user selects a value from Combo1, then selects a value from Combo2 (which is then correctly limited based on the current value of Combo1), and then goes back and changes the selection in Combo1. Even though I have put Combo2.Requery in Combo1's AfterUpdate event procedure (and Combo2's list reflects that requery IF the list is then re-opened), the previously selected value in Combo2 remains, thus permiting the user to save a record which violates the relationship which is otherwise enforced by Combo2's SQL statement. If I add Combo2 = "", or Combo2 = Null, or Combo2 = Empty, to Combo1's AfterUpdate procedure, I get an error telling me that the field cannot accept that value.

What I need is a way to essentially "reset" Combo2 to its original state when the value of Combo1 is changed. I know I cannot (and certainly don't want to) save the record in that state, I only need to remove the previously selected value of Combo2, which has been rendered invalid by the change to Combo1.

 

[bbrendan]

I think this might do it!.

On combo2 in the event 'After Update'
select code and try this!


Private Sub combo1_AfterUpdate()
Me.Combo2.Requery
End Sub

 

[AlanS]

Thanks, bbrendan, but I'm already doing what you suggest, and it doesn't work. While it does requery Combo2's list, it still does not remove the now invalid selection displayed in Combo2's text area. I need to remove that to prevent the user from saving a record with a Combo2 value no longer present in Combo2's list.

 

[Rich]

Have you triedMe.Comb2Name.Undo or SendKeys "{ESC}"

 

[AlanS]

Rich, Combo2.Undo won't work, because the Undo method must be applied before the object has been updated.

However, your suggestion led me to the solution: save to local variables the values of the previous controls on the form, then execute Me.Undo (which resets all the controls on the form, and works because the form itself has not yet been updated), then restore the previous controls, then Combo2.Requery, then Me.Repaint. THANK YOU!