Copy data from lookup column with multiple values to new record Access 2007

Question

I am copying a record from one table to another in Access 2007. I iterate through each field in the current record and copy that value to the new table. It works fine until I get to my lookup column field that allows multiple values. The name of the lookup column is "Favorite Sports" and the user can select multiple values from a dropdown list.

I believe the values of a multivalued field are stored in an array but I cannot access the values in VBA code! I've tried myRecordset.Fields("myFieldName").Value(index) but it didn't work. I don't understand how Access stores multiple values in one field.

I saw something about ItemsSelected on another forum but I don't know what Object is associated with that method.

Thanks for any help!

Answer 1

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:

1. 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.

2. 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.

Answer 2

For a quick and dirty way of getting the values out of a multivalued ('complex data') column, you can use an ADO Connection with the Jet OLEDB:Support Complex Data connection property set to False e.g. the connection string should look something like this:

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\dbs\TestANSI92.accdb;
Jet OLEDB:Engine Type=6;
Jet OLEDB:Support Complex Data=False

The multivaled type column will now be of type MEMO (adLongVarWChar) with each value separated by a semicolon ; character.

But that's only half the problem. How to get data into a multivalued column?

The Access Team seem to have neglected to enhance the Access Database Engine SQL syntax to accommodate multivalued types. The 'semicolon delimiter' trick doesn't work in reverse e.g.

INSERT INTO TestComplexData (ID, weekday_names_multivalued) 
   VALUES (5, 'Tue;Thu;Sat');

fails with the error, "Cannot perform this operation", ditto when trying to update via ADO recordset :(