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
-
I thought I'd post this 'cause I searched the sight - scoured the web actually - and only found several sparse examples that didn't quite work so well.John Shaw– John Shaw2016-01-29 15:58:09 +00:00Commented Jan 29, 2016 at 15:58
Add a comment
|
1 Answer
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