Cancelling the Change of a Combo-box

[M_S_Jones]

Hello all,

I have a form which allows amendments of records. Before a combo-box on the form can have its contents changed, I am displaying a vbyes/no message box, which explains the consequences of a change and offers the facility to cancel the change. When the change is cancelled, I'd like the combo-box to retain/revert back to its old value. I can do this with

Cancel = True

or

Me.Undo

But these reverse all of the changes to the form, whereas I only want the change to this one field reversed. I've tried storing the ID in the combo-box in a public variable in the before update event, in the hope that if the user selects the cancel option of the message box (vbno) then in after update I can change the (now changed) combo-box back to its original value. However, the ID value stored in the public variable in the before update event is actually the value that it is being updated to. Why doesn't this work? And is there any easier way to do this?

Thanks in advance,

Matthew

 

[RuralGuy]

Instead of: Me.Undo -- just do Me.ComboBoxName.Undo and it will only affect the cbo; using the name of your ComboBox of course.

 

[M_S_Jones]

Thank you very much for your reply. I've tried this and although it works better than Me.Undo in the sense that it does not reverse changes to other objects, it doesn't reverse the change to the combo-box. Here is the code I am using, minus its error trap, in the after update event of the combo-box (cmbGroup_ID):

Private Sub cmbGroup_ID_AfterUpdate()

Dim Response As String
Dim Mssg As String

Mssg = "Do you wish to continue?"

Response = MsgBox(Mssg, vbQuestion + vbYesNo, "Warning")

If Response = vbNo Then
    Me.cmbGroup_ID.Undo
Else
    Me.Starting_Week = 0
    Me.Alternating = 0
End If

End Sub

At the moment it doesn't matter which option I chose from the vbyes/no message box, the combo-box changes whichever option I choose. Thanks for any advice you can offer.

Matthew

 

[Rich]

Use the BeforeUpdate event of the control

 

[M_S_Jones]

Thanks but I have tried that and it didn't work. Any other ideas? I'm still confused as to why even at the beginning of the before update event the ID in the combo-box is that of the value it is being changed to, not changing from.

Matthew

 

[gemma-the-husky]

try

sendkeys "{esc}" instead of undo

supposed to be deprecated, but it always works for me

sendleys simulates typing lkeys - in this case, the escape key

 

[M_S_Jones]

Thanks for your reply, this is what I usually do, but it reverses all of the changes to the form's data, not just the one for that object. I may have to use this if I can't find any other solution, as most of the time users will only have one detail to change.

 

[M_S_Jones]

Thanks for all of your replies. I've found a way around this now, it's not perfect but I've created a public variable and I am now using it to store the value of the combo-box on open. Then after update if the user opts for the vbno option of the message box the value of the combo-box is set to be the value of the variable. If the user selects vbyes then the value of the variable will be updated to the new combo-box value.

Matthew