查询通过Lookup获取，但是具有相同SQL的RowSource却没有 [英] Query fetches via Lookup, but RowSource with same SQL does not

问题描述

在我的项目中，我有一个获取正确值的查询，但是当我将表用作列表框的rowsource时，它不会显示相同的值.

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.

有两个表:Characters，Scenes和一个表格.

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

Characters表具有以下列:ID和Title.它充满了《星球大战》角色的名字.

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

Scenes表具有以下列:ID，Title和Appearances. Appearances是多选列，并针对Characters.ID字段执行查找.这是该列的查找字符串:

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]; 

这里是棘手的地方.尽管Appearances字段绑定到Characters.ID，但它会显示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 to Characters.ID as the *second* column - *AND* - it is that column which is bound. This keeps the table bound to the ID column properly, but since the *first* column is the Title then it is the 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.

场景.外观的查阅列

当查看Scenes表时，我在外观"列中看到字符的名称，用逗号分隔-完美！

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

显示标题而不是ID的场景表

但是，当我将此表用作列表框的rowsource时，我希望获得相同的效果，但是列表框仅显示ID(我认为是因为这是绑定列).

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?

显示场景表的列表框

我一直在查看这个问题 ，这很相似，但无法弄清楚.

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

我认为我已经对所有内容进行了很好的描述，但这是我的示例数据库； 在我的Google驱动器中. 这是对在Scenes表中选择字段.

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.

推荐答案

您正在使用查找字段-一种智能"功能，该功能在查询(或表)本身中很有用，但除了麻烦之外，什么都不会引起麻烦-如您所见现在.

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.

您看到的值是与 Bob，Harry和Justin 相关的 ID .将它们移到一个单独的表中，将该表加入查询中，然后检索表单的名称.

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.

要收集和连接名称，请首先创建一个简单的查询，例如:

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

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

将其另存为 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;

函数:

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表字段:

这篇关于查询通过Lookup获取，但是具有相同SQL的RowSource却没有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！