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):

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

Gustav · Accepted Answer · 2025-10-23 07:08:17Z

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.

edited Oct 23 at 7:08

answered Oct 20 at 6:56

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shiela Oct 22 at 23:19

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 Oct 23 at 7:09

Yes, see extended answer, please.

Shiela Oct 23 at 17:00

will check on this

Olivier Jacot-Descombes · Accepted Answer · 2025-10-19 14:15:50Z

1

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.

edited Oct 19 at 14:15

answered Oct 19 at 14:10

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

2 Comments

Shiela Oct 19 at 18:58

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 Oct 20 at 14:48

Select these columns and set the listbox's ColumnCount accordingly. You can then specify the desired width in ColumnWidths.

Collectives™ on Stack Overflow

How to show true string in Access VBA Listbox

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related