如何在Access VBA列表框中显示真实字符串（用户名）

我用以下VBA代码设置列表框listBoxTrial的RowSource，从带自动编号ID的tbl2026Test表取数据：

Dim statInProg, statPClarif As String
statInProg = "In Progress"
statPClarif = "For Clarification"

listBoxTrial.RowSource = "SELECT [Developer], [TicketID], [Status], [Deployment Date], [Modified], [Modified By] FROM tbl2026Test WHERE Supervisor = '" & cmbTeams & _
"' AND Status = '" & statInProg & "' OR Supervisor = '" & cmbTeams & "' AND Status = '" & statPClarif & "'"

下拉框cmbTeams的数据源是：

SELECT DISTINCT [Team] FROM tbl_Teams ORDER BY [Team];

当下拉框内容变化时，列表框能显示数据，但Developer和Modified By字段显示的是UserInfo表中的ID（不是对应的用户名）。UserInfo表以自动编号为ID，比如ID=256对应用户名Test, FirstName A.。

我尝试过几个查询都没解决：

• 列表框仍显示ID：

  listBoxTrial.RowSource = "SELECT DISTINCT tbl2026Test.[Developer] FROM tbl2026Test LEFT JOIN UserInfo ON tbl2026Test.[Developer] = UserInfo.ID"
  

• 列表框为空：

  listBoxTrial.RowSource = "SELECT DISTINCT tbl2026Test.[Developer] FROM tbl2026Test LEFT JOIN UserInfo ON tbl2026Test.[Developer] = UserInfo.Name"
  

• 能显示用户名，但无法包含其他所需字段，也没筛选状态：

  listDevInprogess.RowSource = "SELECT distinct UserInfo.[Name] from UserInfo inner JOIN tbl2026Test ON tbl2026Test.[Developer] = UserInfo.[ID]"
  

• Modified By字段仍显示ID：

  listDevInprogess.RowSource = "SELECT distinct UserInfo.[Name], " & _
      "tbl2026Test.[ID], tbl2026Test.[Status], tbl2026Test.[Deployment Date], tbl2026Test.[Modified], tbl2026Test.[Modified By] " & _
      "from UserInfo inner JOIN tbl2026Test " & _
      "ON tbl2026Test.[Developer] = UserInfo.[ID] " & _
      "ON tbl2026Test.[Modified By] = UserInfo.[ID] "
  

需求：列表框同时显示Developer和Modified By对应的用户名、tbl2026Test的其他目标字段（TicketID、Status、Deployment Date、Modified），并筛选状态为“In Progress”和“For Clarification”的数据。

核心是两次关联UserInfo表并起别名，分别对应开发者和修改者字段，同时保留原筛选逻辑。以下是修正后的VBA代码：

Dim statInProg, statPClarif As String
statInProg = "In Progress"
statPClarif = "For Clarification"

listBoxTrial.RowSource = "SELECT " & _
    "devInfo.[Name] AS [Developer], " & _
    "tbl2026Test.[TicketID], " & _
    "tbl2026Test.[Status], " & _
    "tbl2026Test.[Deployment Date], " & _
    "tbl2026Test.[Modified], " & _
    "modInfo.[Name] AS [Modified By] " & _
    "FROM (tbl2026Test " & _
    "LEFT JOIN UserInfo AS devInfo ON tbl2026Test.[Developer] = devInfo.[ID]) " & _
    "LEFT JOIN UserInfo AS modInfo ON tbl2026Test.[Modified By] = modInfo.[ID] " & _
    "WHERE tbl2026Test.Supervisor = '" & cmbTeams & "' " & _
    "AND tbl2026Test.Status IN ('" & statInProg & "', '" & statPClarif & "')"

关键说明： #

• 给UserInfo表分别起别名devInfo和modInfo，区分开发者和修改者的关联查询，避免字段冲突。
• 使用AS给关联后的用户名字段重命名为原字段名，保证列表框列标题与需求一致。
• 用IN简化状态筛选条件，替代重复的OR逻辑，更简洁易读。
• 使用LEFT JOIN确保即使Developer或Modified By字段为空（无对应ID），列表框仍能显示其他有效数据。

内容的提问来源于stack exchange，提问作者Shiela