asked on

Updating a multivalue combobox in its afterupdate event

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.

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

colevalleygirl

ASKER

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).

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

colevalleygirl

ASKER

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

Jeffrey Coachman

OK
Great

Jeff