Problems with listbox and Crosstab queries

[Poobear1929]

Hi,

I am trying to populate a listbox on open event based on a crosstab query. Here is my code. My question is, can I can I make the number of column dynamic based on the results of my crosstab? Second, how can I get my columns to display correctly. Any help would be great!

Private Sub Form_Open(Cancel As Integer)
Dim strRowSource As String
Dim strTaskID As String
Dim cnnCurr As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String


Set cnnCurr = Me.Application.CurrentProject.Connection
Set rs = New ADODB.Recordset


strTaskID = Me.cmb_task_type

sql = "TRANSFORM First(qry_Condition_Mission_CTB.Condition_Value) AS FirstOfCondition_Value " & _
"SELECT qry_Condition_Mission_CTB.mission_task_ID, qry_Condition_Mission_CTB.Task_type_ID, qry_Condition_Mission_CTB.Mission_task_name " & _
"FROM qry_Condition_Mission_CTB " & _
"WHERE (((qry_Condition_Mission_CTB.Type_Condition) Is Not Null) And " & _
"((qry_Condition_Mission_CTB.Task_type_ID)=" & strTaskID & "))" & _
"GROUP BY qry_Condition_Mission_CTB.mission_task_ID, " & _
"qry_Condition_Mission_CTB.Task_type_ID, qry_Condition_Mission_CTB.Mission_task_name " & _
"PIVOT qry_Condition_Mission_CTB.Type_Condition;"

Debug.Print sql

rs.Open sql, cnnCurr, adOpenKeyset

Me.List_task_to_condition.ColumnCount = 5(this I would like to be dynamic)
Me.List_task_to_condition.ColumnHeads = True
Me.List_task_to_condition = 1
Me.List_task_to_condition =
Me.List_task_to_condition = "Table/Query"

Set Me.List_task_to_condition.Recordset = rs

rs.Close: Set rs = Nothing


End Sub

*******************************

 

[SkipVought]

Hi,

Count the fields in rs

rs.Open sql, cnnCurr, adOpenKeyset
iCount = rs.fields.count

subtract off the CONSTANT field count.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Poobear1929]

Thanks for the quick response. The icount worked great and I was able to make the columncount dynamic. Now the only problem is the columna are being displayed in I believe alphabetical order. However, since my column count is dynamic I can't be sure which column my primary key will turn up and I don't want it displayed. Any ideas?

 

[SkipVought]

dim fld as adodb.field
rs.Open sql, cnnCurr, adOpenKeyset
iCount = rs.fields.count
for each fld in rs.fields
  with fld
    Select case .name 
      case "MyKeyFld","OtherFieldNameIDont'WantToAdd"
      case else
        'additem the sucker!!!

    end select
  end with
next

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Poobear1929]

Hi Skip

Thanks very much for the fast response you rock! The only problem now is with me, I am just a little confused where in my code would I put your code? Could look at my first post and tell me where to drop it in?

Thank You so much!

 

[SkipVought]

You never did post an AddItem for your listbox. Here's an example...

    For i = 1 To NumRows
        lstMyListBox.AddItem “Row” & i & “Column1?
        For j = 1 To NumColumns - 1
            lstMyListBox.List(i - 1, j) = “Row” & i & “Column” & j
        Next j
    Next i

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Poobear1929]

Thanks Skip,

I guess I am still confused. I didn't think I needed to use additem, since I am not asking my user to update the listbox. I just want to display a crosstab query in the listbox, I may have three columns or 5, or 6 is all just depends on the data. I just didn't know if there was a way to change the order the columns get displayed and if I could some how hid the column that displays the id.

Thank you so much for your help.

 

[SkipVought]

Then why bother with a listbox CONTROL???

Stick it in a Label, format with a Fixed Width font.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Poobear1929]

Thanks Skip, I will try that.