• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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.]
0
colevalleygirl
Asked:
colevalleygirl
  • 5
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
see if this link helps


How to: Work With Attachments In DAO
http://msdn.microsoft.com/en-us/library/bb258184.aspx
0
 
colevalleygirlAuthor Commented:
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).
0
 
colevalleygirlAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
yes, that is correct.

you can use the controls OldValue and Value property to make comparison in the before update event of the form.
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
colevalleygirlAuthor Commented:
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.
0
 
colevalleygirlAuthor Commented:
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

PROC_EXIT:

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.
0
 
colevalleygirlAuthor Commented:
The suggestions made by the experts didn't work with a multivalue combobox because of oddities in the way MS has implemented them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now