Updating a multivalue combobox in its afterupdate event

Posted on 2012-08-24
Medium Priority
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
  • 3
  • 2
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 38330914
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

ID: 38332225
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
ID: 38338153
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

ID: 38340756
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
ID: 38341753


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

839 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