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

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

davysouthernboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
Look at the property ItemsSelected.  When you selected multiple rows the row numbers are stored in a zero based array called Items Selected.  Help has some good examples in 2003 for multi-select list boxes - I imagine similar for these new Combo boxes in A2007.
0
davysouthernboyAuthor 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?
0
GRayLCommented:
The format for this is:

forms!frmName!cboName.column(0,forms!frmName!cboName.ItemsSelected(CodeCount))

If you want the second column of the combo, set the 0 to 1.  If you are calling the routine from within the code module of the form, you can replace 'forms!frmName!' with 'Me!'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

davysouthernboyAuthor 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?
0
Jeffrey CoachmanMIS LiasonCommented:
If it's anything like listboxes, you will have to select items by the "Row" number.

Something like this:
Me.List0.Selected(2) = True
Me.List0.Selected(5) = True

JeffCoachman
0
davysouthernboyAuthor 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?
0
Jeffrey CoachmanMIS LiasonCommented:
< What's the difference between putting an exclamation mark and a period by the way?>

You could debate this for days.
Basically the Exclamation point (Bang) is used to list the "Members" of an object.
The Period (Dot) is used to list properties.
Many times they are interchangeable.

Is this a listbox, Combobox, or some sort hybrid that is only available in Access 2007?
Be very careful when jumping to use these new Multi-Valued-Fields:
http://allenbrowne.com/Access2007.html#Mixed
(Please remember that database have been designed for decades without the use of this "MS Access olny" feature.)
And that they mask and or limit your abilty to design versitile database designs.
(just be carefull)

JeffCoachman
0
GRayLCommented:
! - something you made
. - something M$ made.
0
Jeffrey CoachmanMIS LiasonCommented:
Thanks Ray, I had forgotten about your little poem.

;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.