On Change occuring when it's not supposed to

[wally]

I have 4 drop down combo boxes in my form. The other three update when the first one is changed erasing the value in the field. The problem am having is... when the user click on the dropdown list, if instead of clicking the arrow again to close the dropdown list they click the same value that is currently in the field, it sees this as a change and erases the values in the other boxes. I've messed around with this in On Change as well as After Update. Is there a way to do this so if the user clicks on the dropdown and clicks on the name that was already in the field to begin with it doesn't see this as a change and doesn't erase the other values? Here's my code"

Private Sub Departments_Change()

Me.pTrainer = Null
Me.pTrainer.Requery
Me.pTrainer = Me.pTrainer.ItemData(-1)
Me.Strings = Null
Me.Strings.Requery
Me.Strings = Me.Strings.ItemData(-1)
Me.STrainer = Null
Me.STrainer.Requery
Me.STrainer = Me.STrainer.ItemData(-1)
End Sub

 

[Pat Hartman]

On Change is the wrong event to use for this purpose. On change runs multiple times - once for each character typed into the control.

The best event to use is the control's BeforeUpdate event because you want to compare the current value with the previous value.

Private Sub Departments_BeforeUpdate(Cancel)
If Me.Departments = Me.Departments.OldValue Then
Else
    Me.pTrainer = Null
    Me.pTrainer.Requery
    Me.pTrainer = Me.pTrainer.ItemData(-1)
    Me.Strings = Null
    Me.Strings.Requery
    Me.Strings = Me.Strings.ItemData(-1)
    Me.STrainer = Null
    Me.STrainer.Requery
    Me.STrainer = Me.STrainer.ItemData(-1)
End If
End Sub

I specifically coded a positive test, ie when the fields are = do nothing because that way, you don't have to worry about nulls.

 

[wally]

Thanks for the reply. It created an error when i applied it to beforeupdate. This now created another problem however... If the user changes the field to a new value it clears all the values in the other combo boxes like it's supposed to, but if the user then changes it back to the original value after that, it won't run the command and the other combo boxes won't be requeried with the proper values. Does this make sense?

 

[wally]

That meant to say it works well when applied to the afterupdate.

 

[wally]

Nevermind I added a Me.Refresh and it works! Thanks!

 

[Pat Hartman]

Are you aware that the refesh is saving the current record? Is that what you want to do? Me.Undo will back out all changes to the current record and so that would restore the original values to the combos. The undo has the downside of backing out changes not related to this discussion as well.

 

[wally]

I do. Its a form with information on in that if one cell is changed the other cells must me changed or left blank. The before update seems to do that well, unless there is something I'm missing... :?

 

[Pat Hartman]

Except for calling controls "cells", you're good to go Time to take off the Excel hat. It will cause nothing but trouble when working with Access.