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
dbfromnewjerseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried:

Cancel = True

Or

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

MyCombobox.Value=Nothing

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't directly set the Value in the control's BeforeUpdate event.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dbfromnewjerseyAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
dbfromnewjerseyAuthor Commented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
dbfromnewjerseyAuthor Commented:
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
0
dbfromnewjerseyAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
dbfromnewjerseyAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't see the attachment - could you try again?
0
dbfromnewjerseyAuthor Commented:
0
dbfromnewjerseyAuthor Commented:
I attached it.
0
dbfromnewjerseyAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The combo is unbound, therefore there is nothing to "undo" (since there was no value previously).

If you bind it to a field, the code I suggested will work.

If you do NOT bind it to a field, then you'd have to do something to the effect of saving the value of the combo (like in the Enter or OnFocus event) and then restoring that value in the Exit or LostFocus event.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.