I would recommend against using multivalue fields for precisely the reason you re running into, because it s extremely complex to refer to the data stored in this simple-to-use UI element (and it s for UI that it s made available, even though it s created in the table design).
From your mention of "ItemsSelected," you seem to be assuming that you access the data in a multivalue field the same way you would in a multiselect listbox on a form. This is not correct. Instead, you have to work with it via a DAO recordset. The documentation for working with multivalue fiels explains how to do it in code, something like this:
Dim rsMyField As DAO.Recordset
Set rsMyField = Me.Recordset("MyField").Value
rsChild.MoveFirst
Do Until rsChild.EOF
Debug.Print rsChild!Value.Value
rsChild.MoveNext
Loop
rsChild.Close
Set rsChild = Nothing
Now, given that you can usually access the properties of a recordset object through its default collections, you d expect that Me.Recordset("MyField").Value would be returning a recordset object that is navigable through the default collection of a recordset, which is the fields collection. You d think you could do this:
Me.Recordset("MyField").Value!Value.Value
This should work because the recordset returned is a one-column recordset with the column name "Value" and you d be asking for the value of that column.
There are two problems with this:
it doesn t actually work. This means that Me.Recordset("MyField").Value is not reallly a full-fledged recordset object the way, say, CurrentDB.OpenRecordset("MyTable") would be. This is demonstrable by trying to return the Recordcount of this recordset:
Me.Recordset("MyField").Value.Recordcount
That causes an error, so that means that what s being returned is not really a standard recordset object.
even if it did work, you d have no way to navigate the collection of records -- all you d ever be able to get would be the data from the first selected value in your multivalued field. This is because there is no way in this shortcut one-line form to navigate to a particular record in any recordset that you re referring to in that fashion. A recordset is not like a listbox where you can access both rows and columns, with .ItemData(0).Column(1), which would return the 2nd column of the first row of the listbox.
So, the only way to do this is via navigating the child DAO recordset, as in the code sample above (modelled on that in the cited MSDN article).
Now, you could easily write a wrapper function to deal with this. Something like this seems to work:
Public Function ReturnMVByIndex(ctl As Control, intIndex As Integer) As Variant
Dim rsValues As DAO.Recordset
Dim lngCount As Long
Dim intRecord As Integer
Set rsValues = ctl.Parent.Recordset(ctl.ControlSource).Value
rsValues.MoveLast
lngCount = rsValues.RecordCount
If intIndex > lngCount - 1 Then
MsgBox "The requested index exceeds the number of selected values."
GoTo exitRoutine
End If
rsValues.MoveFirst
Do Until rsValues.EOF
If intRecord = intIndex Then
ReturnMVByIndex = rsValues(0).Value
Exit Do
End If
intRecord = intRecord + 1
rsValues.MoveNext
Loop
exitRoutine:
rsValues.Close
Set rsValues = Nothing
Exit Function
End Function
Using that model, you could also write code to concatenate the values into a list, or return the count of values (so you could call that first in order to avoid the error message when your index exceeded the number of values).
As cool as all of this is, and as nice as the UI that s presented happens to be (it would be really nice if they d added selection checkboxes as a type for a multiselect listbox), I d still recommend against using it precisely because it s so much trouble to work with. This just takes the problem of the standard lookup field (see The Evils of Lookup Fields in Tables) and makes things even worse. Requiring DAO code to get values out of these fields is a pretty severe hurdle to overcome with a UI element that is supposed to make things easier for power users, seems to me.
