How do you trap a combo box index change and cancel it if required

Hi,

I need to stop and ask the user if they really want to change the selected index in a combo box whenever there are changes to the data on the form.  If the user cancels the index change then I need to set the index back to the original value and prevent the values in any other controls dependent on the combo box selection from being changed.  I got it working but its very convoluted and I figured there must be an easier way.

Thanks
ahillierAsked:
Who is Participating?
 
Shiju SasidharanAssoc Project ManagerCommented:
'Change combo box style to drop down
'---------------------------------------------------

Dim iComboIndex As Integer
Private Sub Combo1_Click()
    If Not Combo1.Visible Then
        iComboIndex = Combo1.ListIndex
        Exit Sub 'No message when form is loading
    End If
   
    If iComboIndex <> Combo1.ListIndex Then
        If MsgBox("Do you want to change item to " & Combo1.Text, vbQuestion + vbYesNo) = vbYes Then
            iComboIndex = Combo1.ListIndex
        Else
            Combo1.ListIndex = iComboIndex
        End If
    End If
End Sub
Private Sub Form_Load()
Dim i As Integer
    For i = 1 To 10
        Combo1.AddItem "Item" & i
    Next
    Combo1.ListIndex = 0
End Sub
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Use the combo box's BeforeUpdate to read the value, and based on whatever logic you choose function accordingly...

Private Sub YourComboBox_BeforeUpdate(Cancel as Integer)

Select Case Me.YourComboBox.Value
   Case "Banana"
        if msgBox("Are you really sure you want to change this to banana?", vbYesNo, "?") = vbNo then
              'User hit 'No button.  Bail.
              Cancel = True
              Docmd.CancelEvent
              exit sub
       else
             'User hit 'Yes' button. ok.
       end if
   Case "Orange"
       'Do Orange stuff here
End Select

End Sub
0
 
ahillierAuthor Commented:
Am I missing something?  There is no method "BeforeUpdate" in the list of available methods for a combo box.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please disregard my comment.  I gave you an Access answer, and didn't notice that you asked the question in the VB Controls TA.
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Is this less convoluted that what you have?

Option Explicit

Private lastIndex As Integer
Private dirty As Boolean

Private Sub Form_Load()
    lastIndex = -1
    dirty = True ' set this when your "record" has been changed
End Sub

Private Sub Combo1_Click()
    Dim curIndex As Integer
    curIndex = Combo1.ListIndex
    If lastIndex <> -1 And curIndex <> lastIndex Then
        If dirty Then
            If MsgBox("Change without saving?", vbYesNo, "Changes will be lost") = vbNo Then
                Combo1.ListIndex = lastIndex
                Exit Sub
            End If
        End If
    End If
   
    If curIndex <> lastIndex Then
        lastIndex = curIndex
        Debug.Print "Index Changed: " & curIndex
    End If
End Sub
0
 
Shiju SasidharanAssoc Project ManagerCommented:
;-)  Little bit late

0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
*sigh*...
0
 
ahillierAuthor Commented:
sorry - next time I'll pay more attention to who answered first
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
=)
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Actually Idle_Mind deserved the grade
he made the move first

0
 
ahillierAuthor Commented:
I already appologized to him :(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.