Link to home
Start Free TrialLog in
Avatar of Jenkins
JenkinsFlag for United States of America

asked on

Undo a combobox change

I have a combobox on a form.

I'm trying to be able to undo a selection.

Under the Before Update event, I tried the following:

Me.MyCombobox.Undo

It's not working.  Any other way to undo a selection?  Thanks
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Have you tried:

Cancel = True

Or

Cancel = True
Me.MyCombo.Undo
Use this code:

MyCombobox.Value=Nothing

Open in new window

You can't directly set the Value in the control's BeforeUpdate event.
Avatar of Jenkins

ASKER

Well, how can I set the value back to what it was then? I don't care if I can't do it under the BeforeUpdate event.  Any event would be fine.

By the way,  I had already tried  Cancel = True and  
Cancel = True
Me.MyCombobox.Undo  

and they don't work.

MyCombobox.Value=Nothing   doesn't work either.

I don't get an error with them. It's just that they don't affect the combobox in any way.

Again, I tried all of this all under the BeforeUpdate event.
Cancel = True
Me.MyCombo.Undo

Works on my machine - it stops the update, and "undoes" the change I made, and reverts it back to the original value.

What does it do on yours?

And what do you expect to happen in the combo?
Avatar of Jenkins

ASKER

OK.   Here's the code under my Before Update event of the combobox.  Please read down to see my comment about where I'm trying to undo the combobox change.  I can't get it to work.

Private Sub MyComboBox_BeforeUpdate(Cancel As Integer)

Dim strField1 As String
Dim strMsg As String

‘Check if subform is displaying Nulls. This will be the case when MyForm is first opened.    

If [Forms]![MyForm]![MySubForm].Form.RecordsetClone.RecordCount = 0 Then
   Exit Sub
End If

'Set the strField1 variable to the value of the Field1 displayed in the subform.

strField1 = [Forms]![MyForm]![MySubForm].[Form]![Field1]

If Me.MyComboBox <> strField1 Then
   strMsg = "You have selected a different value than the one currently displayed in Field1 of the subform.”
 
 If MsgBox(strMsg, vbCritical + vbOKCancel) = vbCancel Then
      If MsgBox(strMsg, vbCritical + vbOKCancel) = vbCancel Then
          Cancel = True
          'I AM TRYING TO UNDO THE COMBOBOX CHANGE HERE
  'Me.MyComboBox = Nothing   ¿--tried but not working, so commented out
 
  'Me.MyComboBox.Undo ¿- tried but not working, so commented out
          Exit Sub
 
 Else
 
   Etc, Etc
Are you sure you're actually getting to that code segment? The user would have to click the Cancel button on two different (but identical) MsgBox calls in order to even run that code.

Set a breakpoint in the "If Me.myCombobox <> strField1" and see where that takes you.
Avatar of Jenkins

ASKER

when I pasted the code into here I doubled that line by mistake. it's not actually like that in the program.  This is how it is in the program and it doesn't work:


Private Sub MyComboBox_BeforeUpdate(Cancel As Integer)

Dim strField1 As String
Dim strMsg As String

‘Check if subform is displaying Nulls. This will be the case when MyForm is first opened.    

If [Forms]![MyForm]![MySubForm].Form.RecordsetClone.RecordCount = 0 Then
   Exit Sub
End If

'Set the strField1 variable to the value of the Field1 displayed in the subform.

strField1 = [Forms]![MyForm]![MySubForm].[Form]![Field1]

If Me.MyComboBox <> strField1 Then
   strMsg = "You have selected a different value than the one currently displayed in Field1 of the subform.”
 
 If MsgBox(strMsg, vbCritical + vbOKCancel) = vbCancel Then
      Cancel = True
          'I AM TRYING TO UNDO THE COMBOBOX CHANGE HERE
  'Me.MyComboBox = Nothing   ¿--tried but not working, so commented out
 
  'Me.MyComboBox.Undo ¿- tried but not working, so commented out
          Exit Sub
 
 Else
 
   Etc, Etc
Avatar of Jenkins

ASKER

Just to make sure that there wasn't something I overlooked since there's a lot of code associated with my existing combobox,  I decided to just try to create another  very simple combobox for testing purposes.

I placed a combobox from the toolbox onto my form.

I typed in the values I want.  I used YES and NO.

The default value is YES.

Under the combobox's Before Update event, I put the following code:

Private Sub Combo58_BeforeUpdate(Cancel As Integer)
Me.Combo58.Undo
End Sub

I opened the form, the combobox was defaulted to Yes.  I selected No.

It does absolutely nothing. It stays on yes. It does not work. Thank you.
I did the same thing - created a form with a combo, set the RowSourceType to "Value List", and the Values to "yes;no;cancel".

I bound the combo to a field in a table, and that field was populated with nonsense data - for example, the value in the first row for that field was "tttt". When I opened the form, the value of "tttt" was showing. I selected "yes" from the dropdown. The value remained at "tttt", and the value in the underlying table never changed.

Here's the code I used:

Private Sub Combo0_BeforeUpdate(Cancel As Integer)
  Cancel = True
  Me.Combo0.Undo
End Sub

If I then commented out the two lines of code, the update completed, and the selected value was written to my table.

So I cannot duplicate your issue here.

Perhaps you could upload your database for us to look at? If you do, be sure to obfuscate any sensitive data.

If not, see if you can build another database that replicates the issue, and upload that here.
Avatar of Jenkins

ASKER

I've attached a test database.

In it, there is a form.  On the form is a combobox that has a default value of "YES".

When I select "NO", I'm expecting the value to return to "YES". See the code under the combobox's Before Update event. Thanks.
I don't see the attachment - could you try again?
Avatar of Jenkins

ASKER

I attached it.
Avatar of Jenkins

ASKER

I think I figured out how it had to be done. Still working on it but I think what has to happen is under the Before_Update event of the combobox, the Form_Undo subroutine has to be called.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial