Test whether a multivalue combobox still contains a particular value

Posted on 2012-08-22
Last Modified: 2012-08-29
I have a Access 2010  form with a multivalued combo box on it. (Please, no lectures about this being a bad idea -- take it as read that I understand the pros and cons).

When the user updates this combo, I need to test whether they've removed a particular value from it and check with them whether they really meant to do so or if it was a mistake.

My preferred approach would be to code the beforeupdate event for the combobox, so need to be able to access the values in the form (rather than the underlying multivalued field in the data table). How can I code this in VBA?

[I guess my fallback will be to code the afterupdate event, access the underlying multivalued field (which I know how to do)  and re-add the required value if the user made a mistake.]
Question by:colevalleygirl
    LVL 119

    Expert Comment

    by:Rey Obrero
    see if this link helps

    How to: Work With Attachments In DAO

    Author Comment

    capricorn1, no that doesn't help -- it's still about accessing the underlying data in a table, not the data in a form.
    However, I've answered my own question and it's embarrassingly simple: use myCombobox.value as an array. I was expecting it to be much more complex (because it almost always is with multivalued fields).

    Author Comment

    And no, it's not that easy. I can test the value array in the combobox looking for the item I'm interested in. But even if it has been removed, it shows up because it's the beforeupdate event (I think?) so the combobox hasn't been updated yet.

    Or am I misunderstanding things?
    LVL 119

    Expert Comment

    by:Rey Obrero
    yes, that is correct.

    you can use the controls OldValue and Value property to make comparison in the before update event of the form.
    LVL 119

    Expert Comment

    by:Rey Obrero
    try using the method use in this link for comparing the OldValue and the current Value of the control

    see the function AuditTrail

    Author Comment

    When the before update event triggers, old value and value are both equal -- I wonder if this is a peculiarity of multivalue combobox.

    Ditto for the afterupdate event.

    Accepted Solution

    I now have code in the afterupdate event as follows:

    150             If Not IsNull(myComboBox.Value) Then
    160                 For intIndex = LBound(myComboBox.Value) To UBound(myComboBox.Value)
    170                     If myComboBox.Value(intIndex) = lngMasterID Then
                                'The value of interest has been found
    180                         GoTo PROC_EXIT
    190                     End If
    200                 Next intIndex
                    End If
                    'The value of interest has not been found -- check whether the user intended to
                    'remove it and reinstate it if not

    Open in new window

    I guess this answers own my question. I still can't work out how to reinstate the value in the combobox without updating the underlying recordset, but that's another question.

    Author Closing Comment

    The suggestions made by the experts didn't work with a multivalue combobox because of oddities in the way MS has implemented them.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 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

    14 Experts available now in Live!

    Get 1:1 Help Now