Fill Multiple Columns Listbox Control MSAccess VBA

Question

How can you fill a Listbox Control in MSAccess having multiple columns with VBA? I've searched StackOverFlow to find limited examples Here, but nothing that would help a beginning level tinkerer draw from Fields of another Table thru an SQL Statement

Answer 1

Here is an example using an SQL Statement that selects from a single Table having 3 Columns

Dim db As DAO.Database
Dim cnxn As Object
Dim rst As DAO.Recordset
Dim strsql As String

Set db = CurrentDb()
Set cnxn = Application.CurrentProject.Connection

strsql = "SELECT [Tbl A].[Fld 1], [Tbl A].[Fld 2],[Tbl A].[Fld 3]" _
  & "FROM [Tbl A] INNER JOIN [Tbl A]" _
  & "ON [Tbl B].[Fld 1] = [Tbl A].[Fld 1];"

Set rst = db.OpenRecordset(strsql, dbOpenDynaset)

Me.Lstbox.ColumnCount = 3
Me.Lstbox.ColumnWidths = "0.6 in;0.9 in;1 in"

With rst
  .MoveFirst

Do Until .EOF
'Fields are listed in ordinal position with (0) as being the 1st
Me.Lstbox.AddItem rst.Fields(0).Value & ";" & rst.Fields(1) & ";" & rst.Fields(2)

rst.MoveNext
Loop
End With

rst.Close
LstVsn.Requery
Set cnxn = nothing
Set db = nothing
End Sub