revert unbound combobox to original data using BeforeUpdate (A97)

Posted on 2005-05-15
Last Modified: 2011-10-03
i have an unbound combo box on an unbound form.
on the BeforeUpdate event i want the user to confirm the data change using a messagebox
if the user selects 'No', then i want the combo box to revert back to it's original data.

normally i would do this using:
(see below for full code)

however, this doesn't make any difference - the data in the combobox is still the new data.
(NOTE: pressing ESC twice doesn't revert back to original data either)

Question: How can i revert back to the original data in an unbound combobox, on an unbound form, using BeforeUpdate?


Private Sub cbo_customer_id_BeforeUpdate(Cancel As Integer)

    If MsgBox("Are you sure you want to change the Customer on this Rental Order?", vbQuestion + vbYesNo, "Change Customer") = vbNo Then
        Cancel = True
     End If
End Sub
Question by:CLL
    LVL 74

    Accepted Solution


    The problem is that since the ComboBox is unbound, the value does not exist until "After" it is updated.

    You could do the Reversion "After Update", (The code is a lot more complex, but I tried it and it works)

    1. Set the Default Value of "cbo_customer_id" to:   0

    (Note that I used a Module Variable in the code to store the "Original" value of the ComboBox)

    Here is the entire code:

    Option Compare Database
    Option Explicit
    Private lngNowValue As Long  'Module Variable, stores the Original Value of the CustID ComboBox

    Private Sub cbo_customer_id_AfterUpdate()
        'If the ComboBox does not match the Module Variable
        If Me.cbo_customer_id <> lngNowValue Then
            'Ask if change should be made
            'If No, then set the Customer ID ComboBox
            ' back to it's original Value
            If MsgBox("Are you sure you want to change the Customer on this Rental Order?" _
            , vbQuestion + vbYesNo, "Change Customer") = vbNo Then
                Me.cbo_customer_id = lngNowValue
            'If Yes, then set the Module Variable to this new Customer ID
                lngNowValue = Me.cbo_customer_id
            End If
        End If

    End Sub

    Private Sub cbo_customer_id_BeforeUpdate(Cancel As Integer)
    'Prevent user form leaving the Customer ID ComboBox blank
        If IsNull(Me.cbo_customer_id) Then
            MsgBox "You cannot leave the Customer ID Field blank"
            Cancel = True
        End If

    End Sub

    Private Sub cbo_customer_id_GotFocus()
    ' Sets the Module Variable to the value of the Customer ID Combobox
        lngNowValue = Me.cbo_customer_id
    End Sub
    LVL 1

    Author Comment

    thats works sweet putting the code into the AfterUpdate event.

    i also put the following code:

    If IsNull(Me.cbo_customer_id) Then
            MsgBox "You cannot leave the Customer ID Field blank"
            me.cbo_customer_id = lngNowValue
    End If

    into the AfterUpdate event so that i could revert back to the original value if the user had deleted it.

    thanks for working that out for me.
    great solution.

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Glad I could help!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now