Updating a multivalue combobox in its afterupdate event

Posted on 2012-08-24
Last Modified: 2012-08-28
I have a multivalued combobox (myComboBox) that I need to update in its afterupdate event to add a single value to the list of selected values (restoring a value that has been unselected in error).

Writing to the current record underlying the form fails with an error 3188 , because it is locked until the afterupdate event completes.

I've tried myComboBox.undo, but that doesn't have any effect.

And I can't put the code in the beforeupdate event because myComboBox.Value isn't updated at that point.

Is there a way to redimension and then add an item to the myCombobox.Value array instead? or some other way of doing this.
Question by:colevalleygirl
    LVL 74

    Accepted Solution

    Not sure if you have heard this or not, but most Access developers do not use these new Multivalued fields.
    They have some issues:

    Because of the lack of use, you may not find all that much support for what you need to do here.

    Multivalued fields are basically a way to represent data in a Many-To-Many relationship.
        One Student can select Many Classes-->One Class can select Many Students.

    To be honest, creating a form for data entry with a structure like this is not easy for the beginning developer.
    Hence "Multivalued Fields" were created.
    Unfortunately, they are not easy to manipulate if you need something custom.
    (Like I imagine you are looking for here)

    1. You can investigate using the "OldValue" property:
    2.  But just for laughs, try pressing the ESC key and see if the old value returns.
    3. Finally, what's the reason why the user just cant check the box again?


    Author Comment

    Yes, multivalued fields (MVFs from now on) have more than a few issues -- if I was starting this application again, I wouldn't use one (although their user interface is perfect for what I need for this item of data). Everywhere else, I'm using many-to-many relationships without problems.  Trouble is, there's an unknown number of copies of the application out there in the wild, so I either need:

    (A)  to code an upgrade routine that will convert the MVFs into a set of tables implementing the equivalent many to many relationship (now, that'd be a useful piece of code to find somewhere on the net),  recode the class that handles the relevant combo box and modify all the associated forms -- including working out how to implement a field that behaves the same as the multivalued combobox in user interface terms. This would be a bit of a long term project, but I'm increasingly thinking it might be worthwhile; or

    (B) fix this issue.

    Going through your suggestions:

    1. One of the problems with MVFs is that they don't handle Value and OldValue correctly (or at least as you or I might expect them to). In the BeforeUpdate event, Value is equal to OldValue and both represent the "old" state of the combobox, i.e. before the user's changes are applied, so there's no way of telling which items have been selected or unselected. In the AfterUpdate event, Value is still equal to Oldvalue but both now represent the "new" state of the combobox i.e. after the user's changes have been applied.

    2. The ESC key does restore the old value of the combobox (and so does mycombobox.parent.undo in VBA) which is the solution I will adopt if I can't just restore the one item. Doing things this way throws away all the changes made to the MVF, but that might be a compromise I have to make.

    3. The MVFs in this application are used to select a number of "Categories" to which data records (of various types) can be assigned; each data record can belong to no, one or more Categories.  It's possible to select a single Category (the current Master Category) and display all the records that belong to that Master Category in a navigation tree structure that allows you to move around the records and (among other edits) edit the categories to which they belong.  If you remove a record from the Master Category, it is removed from the navigation tree and no longer displayed as the current record, so you can't simply check the box again -- you would have to navigate elsewhere in the application, find the relevant record, recheck the box, and then navigate back to the Master Category to continue whatever you were doing. (I hope this makes sense).
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    OK, ...great we appear to be on the same page.

    Again, I will admit that MVF are *very* appealing.

    It's just that I learned Access using V97 so I kinda got used to doing things the "old way"

    My other fear is that, if I started using them, there would be some other "limitation" that might be even harder to work around.

    Keep me posted

    Author Closing Comment

    I'm going with mycombobox.parent.undo (the VBA equivalent of ESC). It's not ideal but at least it works.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now