I am creating a reporting tool that will show two listbox in a form. Row and Column lists, user will select multiple ROW and Single COLUMN based on that a cross tab query should populate.
I googled and took some really helping code but I am not able to achieve what I need and I know I am near to it, But I am not able to complete it.
Please could you help me out on this.
Row list box name is - lstRowData
column list box name is - lstColData
I want to show the data in a subform as datasheet view, name of the subform is - QryDatasheet
Expand|Select|Wrap|Line Numbers
- Dim tst, clm As Variant
- clm = Me.lstColData.Value
- tst = "TRANSFORM Count(QryConsolidatedData.[Employee ID]) AS [Count]" _
- & " SELECT " & RowDatas & ", Count(QryConsolidatedData.[Employee ID]) AS [Total Of Employee ID]" _
- & " FROM QryConsolidatedData" _
- & " GROUP BY " & RowDatas & "" _
- & " PIVOT QryConsolidatedData.[" & clm & "]"
- Function RowDatas()
- Dim varItem As Variant 'Selected items
- Dim strWhere As String 'String to use as WhereCondition
- Dim lngLen As Long 'Length of string
- Dim strDelim As String 'Delimiter for this field type.
- Dim strDescrip As String 'Description of WhereCondition
- strDelim = """" 'Delimiter appropriate to field type. See note 1.
- 'Loop through the ItemsSelected in the list box.
- With Form_Form1.lstRowDtls
- For Each varItem In .ItemsSelected
- If Not IsNull(varItem) Then
- 'Build up the filter from the bound column (hidden).
- 'strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
- strWhere = strWhere & "[" & .ItemData(varItem) & "]" & ", "
- 'Build up the description from the text in the visible column. See note 2.
- strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
- End If
- Next
- End With
- 'Remove trailing comma. Add field name, IN operator, and brackets.
- lngLen = Len(strWhere) - 2
- If lngLen > 0 Then
- 'strWhere = "IN (" & left$(strWhere, lngLen) & ")"
- strWhere = left$(strWhere, lngLen)
- lngLen = Len(strDescrip) - 2
- If lngLen > 0 Then
- strDescrip = "Details: " & left$(strDescrip, lngLen)
- End If
- End If
- RowDatas = strWhere
- End Function