Query fetches via Lookup, but RowSource with same SQL does not

Question

In my project, I have a query that fetches the right values, but when I use the table as the rowsource for a listbox, it doesn't display the same values.

For my example, I am using Star Wars Scenes.

There are two Tables: Characters, Scenes and one Form.

The Characters Table has these columns: ID and Title. It is filled with names of Star Wars characters.

The Scenes Table has these columns: ID, Title and Appearances. Appearances is a multi-select column and performs a lookup against the Characters.ID field. Here is the lookup string for that column:

SELECT Characters.[Title], Characters.ID FROM Characters ORDER BY Characters.[Title]; 

Here is where the trickiness happens. Although the Appearances field is bound to Characters.ID, it displays Characters.Title Instead of displaying the IDs in the table, I want it to display the names for human readability. Notice how the sql string is set toCharacters.IDas the *second* column - *AND* - it is that column which is bound. This keeps the table bound to theIDcolumn properly, but since the *first* column is theTitlethen it is theTitle` that displays in the table when humans look at it.

Lookup Column for Scenes.Appearances

When I view the Scenes table, I see the names of the characters in the Appearances column, separated with commas - perfect!

Scenes Table Displaying Titles Instead of IDs

However, when I use this table as the rowsource for a listbox, I want the same effect but the listbox only displays the ID instead (I assume it is because that is the bound column).

How do I make the listbox display like the Table does? I am hoping there is a simple trick to get the listbox to display the same way as the query does. The table displays the names from the non-bound column, but the listbox does not, even though the listbox is set to display the same table. Maybe there's a better way to go about this? Or is this a bug?

Listbox Displaying Scenes Table

I have been looking at this question, which is similar, but can't figure this out.

I think I've described everything well enough, but here is my sample database; in my google drive. Here is a good explanation of the multi-select field in the Scenes table.

Answer 1

You are using lookup fields - a "smart" feature that can be useful in the query (or table) itself but otherwise cause nothing but trouble - as you see now.

The values you see are the IDs related to Bob, Harry, Justin. Move those to a separate table, join this table in the query, and retrieve the names for your form.

Edit:

To collect and concatenate the names, first create a simple query like:

SELECT 
    tblChild.FK, 
    tblChild.Name
FROM 
    tblChild
WHERE 
    tblChild.FK = [Key];

Save this as, say, QueryConcat.

Then modify your original query to include the function below using this query and specifying the separator, like:

SELECT 
    tblParent.ID, 
    tblParent.SomeField, 
    ConcatenateRecords("QueryConcat", [ID], "Name", " ,") AS Names
FROM 
    tblParent;

And the function:

Public Function ConcatenateRecords( _
  ByVal strSource As String, _
  ByVal lngKey As Long, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from one field (strField) from all
' records in query strSource using parameter Value lngKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 1999-10-12. Cactus Data ApS, CPH

  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field

  Dim booPluralis As Boolean
  Dim strFields   As String

  On Error GoTo Err_ConcatenateRecords

  Set dbs = CurrentDb()

  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = lngKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)

    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.Value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With

    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If

  Set dbs = Nothing

  ConcatenateRecords = strFields

Exit_ConcatenateRecords:
  Exit Function

Err_ConcatenateRecords:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecords

End Function

MV table field: