davysouthernboy
asked on
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?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks Ray, I had forgotten about your little poem.
;-)
;-)
ASKER
Dim CodeCount As Integer
For CodeCount = 0 To Code.ItemsSelected.Count - 1
MsgBox ("Item: " & Code.ItemsSelected.Item(Co
MsgBox ("Value: " & Code.ItemData(Code.ItemsSe
Next CodeCount
The Code.ItemSelected.Item(Cod
However, the line Code.ItemData(Code.ItemsSe
What am I doing wrong?