We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How Do I Get / Set The Value of  MultiValued Field / ComboBox in Visual Basic for Applications Under Access 2007?

Medium Priority
1,009 Views
Last Modified: 2013-11-28
Before updating the text field to a multi-value field / combo box, I was using:

 Me.<Textbox Name>.Value = <Value> to set the value

and
Me.<Textbox Name>.Value  to retrieve the value

After updating this field to a multi-value field, the above code no longer works. I've looked at all of the properties and consulted the help menu.  The help menu mentions using DAO to retrieve the parent recordset and then setting a childrecordset to the multivalue field in the parent recordset to browse the selected value.  I've been successful in doing that, but how do I set with vba which values I want selected in the child recordset and how would I pass this back in the parent recordset for submission to the database?
Here is how I would browse the value of the field and this works:
Dim db As DAO.Database
Dim rs As Recordset
Dim childRS As Recordset
 
Set db = CurrentDb
 
' Get Primary RecordSet Note
Set rs = db.OpenRecordset("SELECT * FROM Notes WHERE Note_ID = " & Me.Note_ID)
 
rs.MoveFirst
Set childRS = rs.Fields("Code").Value
 
childRS.MoveFirst
 
Do Until childRS.EOF
MsgBox (childRS.Fields("Value").Value)
childRS.MoveNext
Loop

Open in new window

Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
GRayL,  I looked at the ItemSelected property and wrote this code:

Dim CodeCount As Integer
For CodeCount = 0 To Code.ItemsSelected.Count - 1
MsgBox ("Item: " & Code.ItemsSelected.Item(CodeCount))
MsgBox ("Value: " & Code.ItemData(Code.ItemsSelected.Item(CodeCount)))
Next CodeCount

The Code.ItemSelected.Item(CodeCount) line correctly returns the row of the selected item.

However, the line Code.ItemData(Code.ItemsSelected.Item(CodeCount))) returns a blank / empty string everytime.

What am I doing wrong?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
GrayL, Thank you!  That worked like a charm.  So now I know how to get the values of the selected items in VBA.  Could you please tell me how I could programatically set which values in the combobox are selected in VBA?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I put both

Me.Code.Selected(2) = True

and

Me!Code.Selected(2) = True

but it did not work.
Any other ideas?  What's the difference between putting an exclamation mark and a period by the way?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Thanks Ray, I had forgotten about your little poem.

;-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.