vbastringms-accesslistbox

How to show true string in Access VBA Listbox

I have here a listbox showing data from tbl2026Test table (with ID as its own autonumber) by a simple listbox rowsource code:

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 is a combobox with values populated through:

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

It is showing data triggered by combobox change but Developer (or even Modified By) shows ID number from another table named UserInfo but in the actual table of tbl2026Test table, it is not showing ID numbers, it is showing names (string):

developerID

UserInfo table (with its own autonumber as the Name's ID showing in listbox) has this data when I try to filter for ID 256:

ID  |Name               |
256 |Test, FirstName A. |

I tried this below and listBoxTrial showed me the same:

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

I also tried this below and listBoxTrial now showed me blank:

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

I only wanted listBoxTrial to show:

Developer
Test, FirstName A.

Answer here from this link mentions data sheet but would really appreciate if it is applicable on listboxes.

Update:

This below is showing names now. But how can I also include the other columns of tbl2026Test where tbl2026Test.[Satus] = "In Progress" and tbl2026Test.[Satus] = "For Clarification" as I need other tbl2026Test columns to show as well:

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

For Modified By column, it is just also showing me ID instead of names when I tried this below:

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] "
Solution

  • Include those other fields in the query and increase the ColumnCount property of the listbox accordingly:

    listDevInprogess.RowSource = _
    "SELECT distinct UserInfo.[Name], " & _
    "tbl2026Test.[OtherField1], tbl2026Test.[OtherField2] " & _
    "from UserInfo inner JOIN tbl2026Test " & _
    "ON tbl2026Test.[Developer] = UserInfo.[ID]"

    Extended filtering

    Inner Join could be extended, but that would block the query to be studied in Design View, so I will suggest the older syntax using Where which also is easier to read.

    Your filter on Status is simplest to apply using In:

    listDevInprogess.RowSource = _
    "SELECT distinct UserInfo.[Name], " & _
    "tbl2026Test.[OtherField1], tbl2026Test.[OtherField2] " & _
    "FROM UserInfo, tbl2026Test " & _
    "WHERE (tbl2026Test.[Developer] = UserInfo.[ID] Or tbl2026Test.[Modified By] = UserInfo.[ID]) " & _
    "And [Status] In ('In Progress', 'Clarification')"

    If you have very many records, this may be too slow. If so, you could join two subqueries, one for each filtered table.