Mystery! Multi Select List Box not working- very strange behavior!

[ma t]

Hello all,


I have a report with group footers that I have some multi select list boxes in.
I have some code that builds a query based on the list box items.
It works perfectly if the command button (with the event that runs the code) is in the group footer that the list boxes are in, however, it doesn't work (the list box returns null value) if the command button is in the report header, where I would like it to be. The other strange thing is that the code for the list boxes in some of the group footers works with the command button being in the header, and some doesn't. Maybe I need to refer to each group footer somehow in the code?

Thank you,

'************************** Õ¿Õ- **************************
'*** Coded by Martin Green ******* martin@fontstuff.com ***
'******* Office Tips Web Site - www.fontstuff.com *********
'**********************************************************
' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub AgencySubmit_Open()
    Dim blnQueryExists As Boolean
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
    Dim varItem As Variant
    Dim strAgencyType As String
    Dim strCaseManager As String
    Dim strCaseStatus As String
    Dim strCaseManagerCondition As String
    Dim strCaseStatusCondition As String
    Dim strAgencySQL As String
    Dim strDeleteSQL As String
    
    DoCmd.SetWarnings False
    strDeleteSQL = "DELETE * From tblSelectedAgencyType;"
    DoCmd.RunSQL strDeleteSQL
    DoCmd.SetWarnings True

    'date filter- code supplied by Allen Browne
    Dim strDateFilter As String
    Dim strDateField As String
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.

' Check for the existence of the stored query
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    For Each qry In cat.Views
        If qry.Name = "qryAgencySelectQuery" Then
            blnQueryExists = True
            Exit For
       End If
    Next qry

' Create the query if it does not already exist

    If blnQueryExists = False Then
        cmd.CommandText = "SELECT * FROM qryAgencyInvolvTypeQuery"
        cat.Views.Append "qryAgencySelectQuery", cmd

    End If
    Application.RefreshDatabaseWindow

'
' Close the query if it is already open
    If SysCmd(acSysCmdGetObjectState, acQuery, "qryAgencySelectQuery") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryAgencySelectQuery"
    End If

'Build criteria string for the date filter.

    strDateField = "qryAgencyInvolvTypeQuery.[Start Date]"

    If IsDate(Me.txtAgnBegin) Then
        strDateFilter = "(" & strDateField & " >= " & Format(Me.txtAgnBegin, strcJetDate) & ")"
    End If
    If IsDate(Me.txtAgnEnd) Then
        If strDateFilter <> vbNullString Then
            strDateFilter = strDateFilter & " AND "
        End If
        strDateFilter = strDateFilter & "(" & strDateField & " <= " & Format(Me.txtAgnEnd, strcJetDate) & ")"
    End If

' Build criteria string for AgencyType
    For Each varItem In Me.lstAgencyType.ItemsSelected
        strAgencyType = strAgencyType & "," & lstAgencyType.ItemData(varItem) & ""
    Next varItem
    If Len(strAgencyType) = 0 Then
        strAgencyType = "Like '*'"
    Else
        strAgencyType = Right(strAgencyType, Len(strAgencyType) - 1)
        strAgencyType = "IN(" & strAgencyType & ")"
    End If

' Build criteria string for CaseManager
    For Each varItem In Me.lstCaseManager.ItemsSelected
        strCaseManager = strCaseManager & "," & Me.lstCaseManager.ItemData(varItem) & ""
    Next varItem
    If Len(strCaseManager) = 0 Then
        strCaseManager = "Like '*'"
    Else
        strCaseManager = Right(strCaseManager, Len(strCaseManager) - 1)
        strCaseManager = "IN(" & strCaseManager & ")"
    End If

' Build criteria string for Status
    For Each varItem In Me.lstCaseStatus.ItemsSelected
        strCaseStatus = strCaseStatus & ",'" & Me.lstCaseStatus.ItemData(varItem) & "'"
    Next varItem
    If Len(strCaseStatus) = 0 Then
        strCaseStatus = "Like '*'"
    Else
        strCaseStatus = Right(strCaseStatus, Len(strCaseStatus) - 1)
        strCaseStatus = "IN(" & strCaseStatus & ")"
    End If

' Get CaseManager condition
    If Me.optAndCaseManager.Value = True Then
        strCaseManagerCondition = " AND "
    Else
        strCaseManagerCondition = " OR "
    End If

' Get Status condition
    If Me.optAndCaseStatus.Value = True Then
        strCaseStatusCondition = " AND "
    Else
        strCaseStatusCondition = " OR "
    End If


If strDateFilter <> "" Then
    strDateFilter = " AND " & strDateFilter & " "
End If

' Build SQL statement
    strAgencySQL = "SELECT qryAgencyInvolvTypeQuery.* FROM qryAgencyInvolvTypeQuery " & _
             "WHERE qryAgencyInvolvTypeQuery.[Agency Type] " & strAgencyType & _
             strDateFilter & _
             strCaseManagerCondition & "qryAgencyInvolvTypeQuery.[CaseManager] " & strCaseManager & _
             strCaseStatusCondition & "qryAgencyInvolvTypeQuery.[Status] " & strCaseStatus & ";"


' Apply the SQL statement to the stored query
    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("qryAgencySelectQuery").Command
    cmd.CommandText = strAgencySQL
    Set cat.Views("qryAgencySelectQuery").Command = cmd
    Set cat = Nothing

'append to local table.
' Open the Query
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "ApndAgencySelected"
    DoCmd.SetWarnings True
 


End Sub

[June7]

Reports are not intended to be interactive and won't be in PrintPreview. First time I've encountered anyone doing this with report.

Have you step debugged?

[ma t]

I understand. I am using the report as a container for a report building dashboard. The reason I chose a report instead of a form is because I wanted to be able to easily hide sections of controls by placing them in group footers and setting the visible property with a checkbox. As I said, when the command button that runs the code is in the same group footer it works perfectly. When its in the report header, it doesn't work at all (the criteria in the select query is all "*" even though there are items selected.) Any help is greatly appreciated. Is there a way to refer to a group footer in the code? Thank you!

[June7]

I think so, with Section(x) reference. Maybe this will help http://ms-access.itags.org/q_ms-acce...ase_42434.html

I use form with a tab control and code to hide pages of tab control to not make sets of controls available.