MS Access Crosstab query based on Listbox selection

[johny6685]

Hi,

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

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

[johny6685]

I did researched more in this site and corrected my code and it is working for one time and when I change the selections and refresh the query, the query doesn't show the updated data, instead it shows the old data only.

Please could you help me out how to refresh the data?

I even tried to create a new form and put the query on that, so that I can call the subform whenever the user want to refresh with new selection, but I am not getting the fresh data, instead the old data appearing.

Private Sub LbSubmit_Click()
Dim clm As Variant

Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim rst As Recordset
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry_index")
    
Me.Form2.Visible = False
   
    
clm = Me.lstColData.Value

strSQL = "TRANSFORM Count(QryConsolidatedData.[Employee ID]) AS [Counts]" _
& " SELECT " & RowDatas & ", Count(QryConsolidatedData.[Employee ID]) AS [Total Of Employee ID]" _
& " FROM QryConsolidatedData" _
& " GROUP BY " & RowDatas & "" _
& " PIVOT QryConsolidatedData.[" & clm & "]"


qdf.SQL = strSQL
    'DoCmd.OpenQuery "qry_index"
    
       
    Set qdf = Nothing
    Set db = Nothing
Me.Form2.Visible = True

End Sub

[jforbes]

It looks like you are updating the SQL of the Query Definition and I don't think you need to go that far.

I would attempt to update the RecordSource of your SubForm. Maybe try the following around line 22 of your Second Post.:

Me.QryDatasheet.Form.RecordSource = strSQL

[johny6685]

Thanks for your response jforbes.

It worked the way you advised, but the rows and columns are not refreshed actually. The Previous rows still remains there and it says #Name? for the fields I haven't selected the next time.

But I have found another working around in this link which helped me to come out of this issue and it is working perfectly.