Test whether a multivalue combobox still contains a particular value
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.]
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.]
ASKER
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).
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).
ASKER
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?
Or am I misunderstanding things?
yes, that is correct.
you can use the controls OldValue and Value property to make comparison in the before update event of the form.
you can use the controls OldValue and Value property to make comparison in the before update event of the form.
try using the method use in this link for comparing the OldValue and the current Value of the control
http://support.microsoft.com/?kbid=197592
see the function AuditTrail
http://support.microsoft.com/?kbid=197592
see the function AuditTrail
ASKER
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.
Ditto for the afterupdate event.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The suggestions made by the experts didn't work with a multivalue combobox because of oddities in the way MS has implemented them.
How to: Work With Attachments In DAO
http://msdn.microsoft.com/en-us/library/bb258184.aspx