Test whether a multivalue combobox still contains a particular value

Posted on 2012-08-22
Medium Priority
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
  • 5
  • 3
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38320311
see if this link helps

How to: Work With Attachments In DAO

Author Comment

ID: 38320392
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

ID: 38320458
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?
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38320541
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38320554
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

ID: 38328594
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

colevalleygirl earned 0 total points
ID: 38329974
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

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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 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