How to show true string in Access VBA Listbox

Question

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

Answer 1

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.

• Shiela: Hi this one works. May I ask of how to also show names under Modified By. I used yours and added & _ "ON tbl2026Test.[Modified By] = UserInfo.[ID] " after your UserInfo.[ID]" but listbox is blank. Also, may I ask how to add where conditions if I need to show data which have Status of In Progress and For Clarification. TY
• Gustav: Yes, see extended answer, please.
• Shiela: will check on this

Answer 2

ListBoxes and ComboBoxes have a property called ColumnWidths. If it contains e.g., 0 or 0;3cm, then it will not display the first column. I.e., in the query SELECT [Developer], [ID], ... the Developer column will have a width of 0 and the first visible column will be ID.

I would turn the columns around in the query: SELECT ID, Developer, .... Then you can have a BoundColumn of 1, which binds to ID, but because of the 0 entry in ColumnWidths, ID will be hidden and instead the name (and the following columns, depending on ColumnCount) will be displayed.

ColumnCount must include the hidden ID column.

• Shiela: i tested again through this and it is showing me names now: listDevInprogess.RowSource = "SELECT distinct UserInfo.[Name] from UserInfo inner JOIN tbl2026Test ON tbl2026Test.[Developer] = UserInfo.[ID]" but how can i turn my query to include the other columns from tbl2026Test
• Olivier Jacot-Descombes: Select these columns and set the listbox's ColumnCount accordingly. You can then specify the desired width in ColumnWidths.