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
Microsoft Access

Avatar of undefined
Last Comment
Scott McDaniel (EE MVE )

8/22/2022 - Mon
Scott McDaniel (EE MVE )

Have you tried:

Cancel = True

Or

Cancel = True
Me.MyCombo.Undo
unknown_routine

Use this code:

MyCombobox.Value=Nothing

Open in new window

Scott McDaniel (EE MVE )

You can't directly set the Value in the control's BeforeUpdate event.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Jenkins

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.
Scott McDaniel (EE MVE )

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?
ASKER
Jenkins

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

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.
ASKER
Jenkins

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
ASKER
Jenkins

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott McDaniel (EE MVE )

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.
ASKER
Jenkins

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.
Scott McDaniel (EE MVE )

I don't see the attachment - could you try again?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Jenkins

ASKER
Jenkins

I attached it.
ASKER
Jenkins

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question