I have a Listbox (go figure), and this listbox is not tied to a RecordSource and is dynamically built based on other control choices, by the end-user. In the past I haven't had much of a problem with this but with this current dynamic query situation, the columns are not definitively constant. As the RecordSet being used is a CrossTab query, one quarter the CrossTab query may have 5 columns, the next 8 columns, and the next 3 columns.
Most of the ListBox's I have implemented have had a static amount of columns I can predict, but in this situation I am unable to predict the number of columns consistently.
I have set the ColumnHeads
property to Yes
, so thats not an issue and I even reset the ColumnHeads
property against in VBA before the AddItem
operation I have setup.
Logic I am using to operate in/around the Listbox in question (lstCategoryPG):
If lstCatType.ListIndex >= 0 Then
'Application.Echo False 'Turn off Screen Updating
Dim crt As String: crt = "crt_CategoryPG" 'Cross-Tab Query
Dim cttbl As String: cttbl = CreateCTTable(crt) 'Create Table to store the Cross-Tab information
Dim sql As String: sql = SQLSelect(cttbl)
Dim flds As DAO.Recordset: Set flds = CurrentDb.OpenRecordset(sql)
Dim fldwd As String 'Store the Field Width pattern
fldwd = "0"";0"";2""" 'Handles `tid` and `cid` columns in the ListBox
'Assign the number of columns based on the number of fields in CTtable
lstCategoryPG.ColumnCount = flds.Fields.Count
Dim fld As Long
For fld = 3 To (flds.Fields.Count - 1)
fldwd = fldwd & ";.75"""
Next
flds.Close: Set flds = Nothing
lstCategoryPG.ColumnWidths = fldwd
sql = SQLSelect(cttbl, , ("tid = " & lstCatType.Value))
lstCategoryPG.Enabled = True
lstCategoryPG.ColumnHeads = True
RefreshControl CurrentDb, lstCategoryPG, sql, , False
'Application.Echo True 'Turn Screen Updating back on
End If
Tentative break-fix solution that worked for me until i can get into the guts and see why the Column Headers are not being pulled from the query fields.
If lstCatType.ListIndex >= 0 Then
'Application.Echo False 'Turn off Screen Updating
Dim crt As String: crt = "crt_CategoryPG" 'Cross-Tab Query
Dim cttbl As String: cttbl = CreateCTTable(crt) 'Create Table to store the Cross-Tab information
Dim sql As String: sql = SQLSelect(cttbl)
Dim flds As DAO.Recordset: Set flds = CurrentDb.OpenRecordset(sql)
Dim fldwd As String, fldhd As String 'Store the Field Width pattern and Field Header Row
--> fldhd = "-1;-1;Category"
fldwd = "0"";0"";2.5""" 'Handles `tid` and `cid` columns in the ListBox
'Assign the number of columns based on the number of fields in CTtable
lstCategoryPG.ColumnCount = flds.Fields.Count
Dim fld As Long
For fld = 3 To (flds.Fields.Count - 1)
fldwd = fldwd & ";.75"""
--> fldhd = fldhd & ";" & flds.Fields(fld).Name
Next
flds.Close: Set flds = Nothing
lstCategoryPG.ColumnHeads = True
lstCategoryPG.ColumnWidths = fldwd
sql = SQLSelect(cttbl, , ("tid = " & lstCatType.Value))
lstCategoryPG.Enabled = True
RefreshControl CurrentDb, lstCategoryPG, sql, , False
--> lstCategoryPG.AddItem fldhd, 0
'Application.Echo True 'Turn Screen Updating back on
End If