none
Database application crashing on 'no data' RRS feed

  • Question

  • Hello all-
    This is complex (at least, to me it is). 

    WHAT I'M TRYING TO DO:
    I've got a form (frmSelector) where my users make a selection from one of multiple drop down boxes, and click a button to generate a report.
    The report has a button at the top which when clicked, opens a form (frmFilter) that allows the user to apply multiple filters.  A button on this 'filter' form is clicked which applies a filter based on the selections made on frmFilter to the underlying query of the report.  Mostly, this works. 

    WHAT HAPPENS:
    When the user filters and returns a set of records, all is fine.  If the user then (after a successful filter) opens frmFilter again, and (during a single instance of frmFilter) removes the successful filter and applies a filter which will return a 'no data' situation, things work fine as well (ie., a pop up is generated indicating no records found, and the report returns with a 'blank' report.

    However, if the users opens frmFilter and simply makes a selection that will result in a 'no data' situation, the above referenced pop up is generated.  Once the 'OK' button is clicked, access then crashes, and gives a "Microsoft Access has stopped working.  Windows can try to recover your information and restart the program. 'RESTART THE PROGRAM' 'DEBUG THE PROGRAM'.

    Here is my code (please, do not rag me for the poor code - I'm still learning!):

     

    frmSelector:

    Option Compare Database
    
    Private Sub btnClear_Click()
        Me.AttyFullName = Null
        Me.cmboFirm = Null
        Me.cmboDivision = Null
        Me.cmboProduct = Null
        Me.cboState = Null
        
    End Sub
    
    Private Sub btnClose_Click()
        DoCmd.Close acForm, "frmSelector", acSaveNo
        DoCmd.OpenForm "frmStartup", acNormal, "", "", , acNormal
    End Sub
    
    
    
    Private Sub btnDivProd_Click()
     If (IsNull(List18)) Then
            Beep
            MsgBox "Please select a Division from the drop down menu", vbOKOnly, "Make Selection"
            Me.cmboDivision.SetFocus
            Exit Sub
        ElseIf (IsNull(cmboProduct)) Then
            Beep
            MsgBox "Please select a Product from the drop down menu", vbOKOnly, "Make Selection"
            Me.cmboProduct.SetFocus
            Exit Sub
        End If
        DoCmd.openReport "rptProdDivRecordSearch", acViewReport, "", "", acWindowNormal
        
    End Sub
    
    Private Sub btnFirmState_Click()
    If (IsNull(cmboFirm)) Then
            Beep
            MsgBox "Please select a law firm from the drop down menu", vbOKOnly, "Make Selection"
            Me.cmboFirm.SetFocus
            Exit Sub
        ElseIf (IsNull(cboState)) Then
            Beep
            MsgBox "Please select a state from the drop down menu", vbOKOnly, "Make Selection"
            Me.cboState.SetFocus
            Exit Sub
        End If
        DoCmd.openReport "rptStateFirmRecordSearch", acViewReport, "", "", acWindowNormal
        
    End Sub
    
    Private Sub btnProdState_Click()
        If (IsNull(cboState)) Then
            Beep
            MsgBox "Please select a state from the drop down menu", vbOKOnly, "Make Selection"
            Me.cboState.SetFocus
            Exit Sub
        ElseIf (IsNull(cmboProduct)) Then
            Beep
            MsgBox "Please select a Product from the drop down menu", vbOKOnly, "Make Selection"
            Me.cmboProduct.SetFocus
            Exit Sub
        End If
        DoCmd.openReport "rptStateProdRecordSearch", acViewReport, "", "", acWindowNormal
        
    End Sub
    
    Private Sub Command65_Click()
        DoCmd.SetWarnings False
        
        If IsNull(cmboDemo) Then
        Beep
        MsgBox "You have not selected a law firm", vbOKOnly, "Make Selection"
        Exit Sub
        Else
        'create a new table for attorneys with selected firm tha have reviews
        DoCmd.OpenQuery "mtkblReviewedAttorneys", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendReviewedAttorneys", acViewNormal, acEdit
        
        'create a new table for divisions with reviews for selected firm
        DoCmd.OpenQuery "mktblQryDivisionsForFilter", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendDivisionsForFilter", acViewNormal, acEdit
        
        'create a new table for products with reviews for selected firm
        DoCmd.OpenQuery "mktblQryProductsForFilter", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendProductsForFilter", acViewNormal, acEdit
        
        'create a new table for products with reviews for selected firm
        DoCmd.OpenQuery "mktblFirmRecordSearchUNFILTERED_FIRM_DEMO"
        
        
        'open the report
        DoCmd.openReport "rptFirmRecordSearch", acViewReport, "", "", acNormal
        End If
        DoCmd.SetWarnings True
    End Sub
    
    Private Sub Command82_Click()
      ' set variable to allow form to close
        blnAllowClose = True
      DoCmd.Quit acSave
    End Sub
    
    
    
    Private Sub Form_Load()
     Frame54.Value = 1
     AttyFullName.SetFocus
    End Sub
    
    Private Sub Frame54_AfterUpdate()
    Forms!frmSelector!AttyFullName.Value = Null
    Forms!frmSelector!AttyFullName.Requery
    Forms!frmSelector!cmboFirm.Value = Null
    Forms!frmSelector!cmboFirm.Requery
    End Sub
    
    '------------------------------------------------------------
    ' Open_rptAttyRecordSearch_Click
    '
    '------------------------------------------------------------
    Private Sub Open_rptAttyRecordSearch_Click()
        DoCmd.SetWarnings False
        If IsNull(AttyFullName) Then
        Beep
        MsgBox "You have not selected an attorney", vbOKOnly, "Make Selection"
        Exit Sub
        Else
        TempVars.Remove "TempAppliedFilters"
        
        'create a new table for divisions with reviews for selected atty
        DoCmd.OpenQuery "mktblQryDivisionsForFilter_Atty", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendDivisionsForFilter_Atty", acViewNormal, acEdit
        
        'create a new table for states with reviews for selected atty
        DoCmd.OpenQuery "mktblQry_States_For_Filter_Atty", acViewNormal, acEdit
        DoCmd.OpenQuery "APPEND_Qry_States_For_Filter_Atty", acViewNormal, acEdit
        
        'create a new table for products with reviews for selected atty
        DoCmd.OpenQuery "mktblQryProductsForFilter_Atty", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendProductsForFilter_Atty", acViewNormal, acEdit
        
        'create a new table for products with reviews for selected atty
        DoCmd.OpenQuery "mktblAttyRecordSearchUNFILTERED_ATTY"
        
        'Set a temporary variable - 'tempCallingForm' to be equal to this form
        TempVars.Add "tempCallingForm", Me.Name
        
        'Hide the selector form
        Me.Visible = False
        
        'open the report
            DoCmd.openReport "rptAttyRecordSearch", acViewReport, "", "", acNormal
                
        End If
        DoCmd.SetWarnings True
    End Sub
    
    
    Private Sub Open_State_Report_Click()
        DoCmd.SetWarnings False
        If IsNull(cboState) Then
        Beep
        MsgBox "You have not selected a state", vbOKOnly, "Make Selection"
        Exit Sub
        Else
        TempVars.Remove "TempAppliedFilters"
        
            'create a new table for attorneys with reviews for selected state
            DoCmd.OpenQuery "mtkbl_ReviewedAttorneys_STATES", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendReviewedAttorneys_STATES", acViewNormal, acEdit
            
            'create a new table for firms with revivews for selected state
            DoCmd.OpenQuery "mtkbl_Reviewed_Firms_for_STATES", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendReviewed_FIRMSfor_STATES", acViewNormal, acEdit
            
            'create a new table for DIVISIONS with reviews for selected state
            DoCmd.OpenQuery "MakeTableQry_FindDivisionsForSelected_STATE", acViewNormal, acEdit
            DoCmd.OpenQuery "appendDivisionsForFilter_STATES", acViewNormal, acEdit
            
            'create a new table for PRODUCTS with reviews for the selected state
            DoCmd.OpenQuery "mktblQryProductsForFilter_STATES", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendProductsForFilter_STATES", acViewNormal, acEdit
            
            'create a new table for unfiltered states with reviews
            DoCmd.OpenQuery "mktbl_UNFILTERED_STATES"
        
        'Set a temporary variable - 'tempCallingForm' to be equal to this form
        TempVars.Add "tempCallingForm", Me.Name
        
        'Hide the selector form
        Me.Visible = False
        
                DoCmd.openReport "rptsTATERecordSearch", acViewReport, "", "", acNormal
        End If
        DoCmd.SetWarnings True
    End Sub
    
    '------------------------------------------------------------
    ' openRptFirmRecordSearch_Click
    '
    '------------------------------------------------------------
    Private Sub openRptFirmRecordSearch_Click()
    
    DoCmd.SetWarnings False
        If IsNull(cmboFirm) Then
        Beep
        MsgBox "You have not selected a law firm", vbOKOnly, "Make Selection"
        Exit Sub
        Else
        TempVars.Remove "TempAppliedFilters"
        
        'create a new table for attorneys with reviews for selected firm
        DoCmd.OpenQuery "mtkblReviewedAttorneys", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendReviewedAttorneys", acViewNormal, acEdit
        
        'create a new table for divisions with reviews for selected firm
        DoCmd.OpenQuery "mktblQryDivisionsForFilter", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendDivisionsForFilter", acViewNormal, acEdit
        
        'create a new table for products with reviews for selected firm
        DoCmd.OpenQuery "mktblQryProductsForFilter", acViewNormal, acEdit
        DoCmd.OpenQuery "AppendProductsForFilter", acViewNormal, acEdit
        
        'create a new table for STATES with reviews for the selected FIRM
        DoCmd.OpenQuery "mktblQry_States_For_Filter_FIRM", acViewNormal, acEdit
        DoCmd.OpenQuery "APPEND_Qry_States_For_Filter_FIRM", acViewNormal, acEdit
        
        'create a new table for unfiltered FIRM
        DoCmd.OpenQuery "mktblFirmRecordSearchUNFILTERED_FIRM"
        
        'Set a temporary variable - 'tempCallingForm' to be equal to this form
        TempVars.Add "tempCallingForm", Me.Name
        
        'Hide the selector form
        Me.Visible = False
        
            
        
        'open the report
        DoCmd.openReport "rptFirmRecordSearch", acViewReport, "", "", acNormal
        End If
        DoCmd.SetWarnings True
    
    End Sub
    
    
    '------------------------------------------------------------
    ' openRptDivisionSearch_Click
    '
    '------------------------------------------------------------
    Private Sub openRptDivisionSearch_Click()
        DoCmd.SetWarnings False
        
        If IsNull(List18) Then
            Beep
            MsgBox "You have not selected a division", vbOKOnly, "Make Selection"
            Exit Sub
        Else
            TempVars.Remove "TempAppliedFilters"
            
            'create a new table for ATTORNEYS with reviews for selected DIVISION
            DoCmd.OpenQuery "mtkblReviewedAttorneys_DIVISION", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendReviewedAttorneys_DIVISION", acViewNormal, acEdit
            
            'create a new table for FIRMS with reviews for selected DIVISION
            DoCmd.OpenQuery "mtkbl_Reviewed_Firms_for_DIVISIONS", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendReviewed_FIRMSfor_divisions", acViewNormal, acEdit
            
            'create a new table for STATES with reviews for selected DIVISION
            DoCmd.OpenQuery "mtkbl_Reviewed_States_for_DIVISIONS", acViewNormal, acEdit
            DoCmd.OpenQuery "append_Reviewed_States_for_DIVISIONS", acViewNormal, acEdit
            
            
            'create a new table for PRODUCTS with reviews for selected DIVISION
            DoCmd.OpenQuery "mktblQryProductsForFilter_DIVISIONS", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendProductsForFilter_DIVISIONS", acViewNormal, acEdit
            
            'create a new table for unfiltered DIVISION
            DoCmd.OpenQuery "mktblDivisionRecordSearchUNFILTERED_DIVISIONS"
        
        'Set a temporary variable - 'tempCallingForm' to be equal to this form
        TempVars.Add "tempCallingForm", Me.Name
        
        'Hide the selector form
        Me.Visible = False
        
                
            DoCmd.openReport "rptDivisionRecordSearch", acViewReport, "", "", acNormal
        End If
    
    End Sub
    
    
    '------------------------------------------------------------
    ' openRptSearchProduct_Click
    '
    '------------------------------------------------------------
    Private Sub openRptSearchProduct_Click()
        DoCmd.SetWarnings False
        If IsNull(cmboProduct) Then
            Beep
            MsgBox "You have not selected a product", vbOKOnly, "Make Selection"
            Exit Sub
        Else
        TempVars.Remove "TempAppliedFilters"
            'create a new table for ATTORNEYS with reviews for selected PRODUCT
            DoCmd.OpenQuery "mtkblReviewedAttorneys_PRODUCTS", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendReviewedAttorneys_PRODUCTS", acViewNormal, acEdit
            
            'create a new table for FIRMS with reviews for selected PRODUCT
            DoCmd.OpenQuery "mtkbl_Reviewed_Firms_for_Products", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendReviewed_FIRMSfor_PRODUCTS", acViewNormal, acEdit
            
            'create a new table for DIVISIONS with reviews for selected PRODUCT
            DoCmd.OpenQuery "MakeTableQry_FindDivisionsForSelected_PRODUCT", acViewNormal, acEdit
            DoCmd.OpenQuery "AppendDivisionsForFilter_PRODUCTS", acViewNormal, acEdit
            
            'create a new table for STATES with reviews for selected PRODUCT
            DoCmd.OpenQuery "mtkbl_Reviewed_States_for_Products", acViewNormal, acEdit
            DoCmd.OpenQuery "APPEND_Reviewed_States_for_Products", acViewNormal, acEdit
            
            
            'create a new table unfiltered PRODUCTS with reviews
            DoCmd.OpenQuery "mktblProductsRecordSearchUNFILTERED_PRODUCTS"
        
        'Set a temporary variable - 'tempCallingForm' to be equal to this form
        TempVars.Add "tempCallingForm", Me.Name
        
        'Hide the selector form
        Me.Visible = False
        
                
            DoCmd.openReport "rptProductRecordSearch", acViewReport, "", "", acNormal
        
        End If
    
    End Sub
    
    
    '------------------------------------------------------------
    ' Command29_Click
    '
    '------------------------------------------------------------
    Private Sub Command29_Click()
    
       
      
    End Sub
    
    
    

    REPORT

    Option Compare Database
    
    Private Sub Command724_Click()
        DoCmd.Close acReport, "rptAttyRecordSearch", acSaveNo
        TempVars.Remove "tempSelections"
        TempVars.Remove "tempFirmReportFilter"
        TempVars.Remove "tempStateSelection"
        TempVars.Remove "tempDivisionSelection"
        TempVars.Remove "tempAttySelection"
        TempVars.Remove "tempProductSelection"
        TempVars.Remove "tempAppliedFilters"
        TempVars.Remove "tempUseAgain"
        TempVars.Remove "tempCurrentLItTypeSelections"
        TempVars.Remove "alreadyRun"
        Forms!frmSelector.Visible = True
    End Sub
    
    Private Sub Command82_Click()
        DoCmd.Close acReport, "rptAttyRecordSearch", acSaveNo
        TempVars.Remove "tempSelections"
        TempVars.Remove "tempFirmReportFilter"
        TempVars.Remove "tempStateSelection"
        TempVars.Remove "tempDivisionSelection"
        TempVars.Remove "tempAttySelection"
        TempVars.Remove "tempProductSelection"
        TempVars.Remove "tempAppliedFilters"
        TempVars.Remove "tempUseAgain"
        TempVars.Remove "tempCurrentLItTypeSelections"
        TempVars.Remove "alreadyRun"
        Forms!frmSelector.Visible = True
    End Sub
    
    Private Sub Report_Current()
    
        TempVars.Add "tempCallingReport", "rptAttyRecordSearch"
        If Me.Text721 > 0 Then
            Me.lblNoRecords.ControlSource = "= '" + DLookup("[Attorneys]![FullName]", "Attorneys", " Forms![frmSelector]![AttyFullName] = [Attorneys]![Attorney_ID] ") + "'"
        End If
        
        If (testTempVars) Then
            Me.Label724.Visible = False
            Me.Label1155.Visible = False
            Me.Text721.Visible = False
            Me.Text1154.Visible = False
        Else
            Me.Label724.Visible = True
            Me.Label1155.Visible = True
            Me.Text721.Visible = True
            Me.Text1154.Visible = True
        End If
    End Sub
    
    Private Sub Report_Load()
        
        TempVars.Add "alreadyRun", "False"
        If Me.Text721 > 0 Then
            Me.lblNoRecords.ControlSource = "= '" + DLookup("[Attorneys]![FullName]", "Attorneys", " Forms![frmSelector]![AttyFullName] = [Attorneys]![Attorney_ID] ") + "'"
        End If
        
        If TempVars!tempFirmReportFilter = "" Then
           Me.Filter = ""
        End If
        If (testTempVars) Then
            Me.Label724.Visible = False
            Me.Label1155.Visible = False
            Me.Text721.Visible = False
            Me.Text1154.Visible = False
        Else
            Me.Label724.Visible = True
            Me.Label1155.Visible = True
            Me.Text721.Visible = True
            Me.Text1154.Visible = True
        End If
        
    End Sub
    
    Private Sub Report_NoData(Cancel As Integer)
    
        If TempVars!tempCallingForm = "frmSelector" Then
            strMessage = "There are no records for the selected attorney.  Please make another selection."
            alert = MsgBox(strMessage, vbInformation, "No Records Found")
            DoCmd.Close acReport, "rptAttyRecordSearch", acSaveNo
            Forms!frmSelector.Visible = True
            Exit Sub
        Else
            If TempVars!alreadyRun = "True" Then
            Else
                strMessage = DLookup("[Attorneys]![FullName]", "Attorneys", " Forms![frmSelector]![AttyFullname] = [Attorneys]![Attorney_ID] ") + "  has no records with the selected filter combination'"
                alert = MsgBox(strMessage, vbInformation, "No Records Found")
                Me.lblNoRecords.ControlSource = "= '" + DLookup("[Attorneys]![FullName]", "Attorneys", " Forms![frmSelector]![AttyFullname] = [Attorneys]![Attorney_ID] ") + "  has no records with the selected filter combination'"
                TempVars.Add "alreadyRun", "True"
            End If
        End If
    
        Me.Command82.SetFocus
    End Sub
    
    Private Sub Report_Open(Cancel As Integer)
        TempVars.Add "tempCallingReport", "rptAttyRecordSearch"
    End Sub
    
    Private Sub Toggle756_Click()
        DoCmd.OpenForm "frmAttyReportFilter", acNormal, "", "", , acDialog
        If TempVars!alreadyRun = "False" Then
            Me.Requery
        End If
        Me.Command82.SetFocus
        
    
    End Sub
    
    Private Function testTempVars()
        If (Me.Text770.Value = "No Filters Applied") And (Me.Text798.Value = "No Filters Applied") Then
            testTempVars = True
        Else
            testTempVars = False
        End If
    
    End Function
    

    frmFilter

    Option Compare Database
    
    Private Sub btnApplyFilter_Click()
        
        Dim myFilter As String
        Dim litTypeFilter As String
        Dim strLitType As String
        ReDim litTypeOptionsArray(5) As String
        Dim LitTypeArraySize As Integer
        ReDim selectedLitTypes(5) As String
        Dim litTypeSelections As String
        
        'CREATE LITIGATIONTYPE FILTER
        'create an array of the radio buttons
        litTypeOptionsArray(0) = "littype_5_Appeal"
        litTypeOptionsArray(1) = "littype_1_Coverage"
        litTypeOptionsArray(2) = "littype_2_Defense"
        litTypeOptionsArray(3) = "littype_6_EC"
        litTypeOptionsArray(4) = "littype_3_Monitoring"
        litTypeOptionsArray(5) = "littype_4_Subrogation"
        
    
    '   determine number of selected lit types by cycling through all controls on form
    '   and running a test if the control is a check box (ie. controltype 106)
        For Each ctl In Me.Controls
            If (ctl.ControlType = 106) Then
            
            'if it is a check box, then check to see if the check box matches any element of the litTypeOptionsArray
                    For i = 0 To 5
                        If ctl.Name = litTypeOptionsArray(i) Then
                            'if the check box matches the litTypeOptionsArray, then check to see if any of those controls are selected
                            If (ctl.Value) = -1 Then
                                'if the check box is selected, then check to see if litTpyeFilter already contains data
                                If litTypeFilter > "" Then
                                    'if litTypeFilter already has data, then add ' OR ' to it, in order to extend the string to multiple values
                                    litTypeFilter = litTypeFilter + " OR "
                                End If
                                'set the selection within the selectedLitType array to yes
                                selectedLitTypes(i) = "Yes "
                                'acquire the litType value (which is the 9th character of the button's name)
                                'and add that to the litTyepFilter string
                                litTypeFilter = litTypeFilter + "LitigationType = '" + Mid(ctl.Name, 9, 1) + "'"
                            Else
                                'set the selection within the selectedLitType array to no
                                selectedLitTypes(i) = "No "
                            End If
                        End If
                    Next i
            End If
        Next
    
       
        For i = 0 To 5
            litTypeSelections = litTypeSelections + (selectedLitTypes(i))
        Next i
    
        'if any selections were made for lit type, populate the myFilter string with the litTypeFilter
        If litTypeFilter <> "" Then
            'Check to see if myFilter already has data in it
            If myFilter > "" Then
                'if it already has data, insert the word "AND", followed by litTypeFilter
                myFilter = myFilter + " AND (" + litTypeFilter + ")"
            Else
                'if it does not have data in it, then set it up with litTypeFilter
                myFilter = "(" + litTypeFilter + ")"
            End If
        End If
        
        
        'CREATE STATE FILTER
        'Check to see if a state has been selected
        
        If Me.cmboState.Value > 0 Then
            'if a selection was made, check if it was "All" or a state
            'create a new variable and populate it with the value of the selection made
            Dim myState As String
            If Me.cmboState.Value = "All" Then
                myState = ""
            Else
            myState = Me.cmboState.Value
            Dim myStateName As String
            Me.cmboState.SetFocus
            myStateName = Me.cmboState.Text
            'if the selection is not "All" then continue with defining filter
            'Check to see whether myFilter has already been populated with data
                If myFilter > "" Then
                    'if it already has data, insert the word "AND", followed by myState
                    myFilter = myFilter + " AND State = '" + myState + "'"
                Else: myFilter = "State = '" + myState + "'" 'if myFilter does not already have data, populate it simply with myState
                End If
            End If
        End If
        
        
        'CREATE DIVISION FILTER
        'Check to see if a division has been selected
        
        If Me.cmboDivision.Value <> "" Then
            'if a selection was made, check if it was "All" or a Division
            'create a new variable and populate it with the value of the selection made
            Dim myDivision As String
            If Me.cmboDivision.Value = "All" Then
                myDivision = ""
            Else
            myDivision = (Me.cmboDivision.Value)
            'if the selection is not "All" then continue with defining filter
            'Check to see whether myFilter has already been populated with data
                If myFilter > "" Then
                    'if it already has data, insert the word "AND", followed by myDivision
                    myFilter = myFilter + " AND Division = '" + myDivision + "'"
                Else: myFilter = "Division = '" + myDivision + "'" 'if myFilter does not already have data, populate it simply with myDivision"
                End If
            End If
        End If
        
          
    
        
          
        'CREATE PRODUCT FILTER
        'Check to see if a product has been selected
        
        If Me.cmboProduct.Value > 0 Then
            'if a selection was made, check if it was "All" or a Product
            'create a new variable and populate it with the value of the selection made
            Dim myProduct As String
            If Me.cmboProduct.Value = "All" Then
                myProduct = ""
            Else
            myProduct = (Me.cmboProduct.Value)
            'if the selection is not "All" then continue with defining filter
            'Check to see whether myFilter has already been populated with data
                If myFilter > "" Then
                    'if it already has data, insert the word "AND", followed by myProduct
                    myFilter = myFilter + " AND Product = '" + myProduct + "'"
                Else: myFilter = "Product = '" + myProduct + "'" 'if myFilter does not already have data, populate it simply with myProduct
                End If
            End If
        End If
        
        
        'CREATE USE AGAIN FILTER
        'Check to see if use again was selected
        
        If Me.cmboUseAgain.Value > 0 Then
            'if a selection was made, check if it was "All" or a Product
            'create a new variable and populate it with the value of the selection made
            Dim myUseAgain As String
            If Me.cmboUseAgain.Value = "All" Then
                myUseAgain = ""
            Else
            myUseAgain = (Me.cmboUseAgain.Value)
            'if the selection is not "All" then continue with defining filter
            'Check to see whether myFilter has already been populated with data
                If myFilter > "" Then
                    'if it already has data, insert the word "AND", followed by myProduct
                    myFilter = myFilter + " AND UseAgain = '" + myUseAgain + "'"
                Else: myFilter = "UseAgain = '" + myUseAgain + "'"  'if myFilter does not already have data, populate it simply with myProduct
                End If
            End If
        End If
            
            
        'Reset the report filter
        Reports("rptAttyRecordSearch").Filter = ""
      
            
        'create a temporary variable to hold myFilter
        TempVars.Add "tempFirmReportFilter", myFilter
        'create temporary variables to hold all selection status (so that selections can be remembered if filter is opened during this report's session)
        TempVars.Add "tempSelections", litTypeSelections
        TempVars.Add "tempStateSelection", myState
        TempVars.Add "tempDivisionSelection", myDivision
        TempVars.Add "tempAttySelection", myAttorney
        TempVars.Add "tempProductSelection", myProduct
        TempVars.Add "tempUseAgain", myUseAgain
        TempVars.Add "tempCurrentLitTypeSelections", appliedLitTypeFilters(selectedLitTypes())
    
    
        'CREATE A TEMPVAR TO HOLD ALL SELECTIONS, FOR DISPLAY ON MAIN REPORT
        
        
        Dim appliedFilters As String
        appliedFilters = ""
        If TempVars!tempStateSelection <> "" Then
            appliedFilters = "STATE: " + myStateName + Chr(13) & Chr(10)
        End If
        
        If TempVars!tempDivisionSelection <> "" Then
            If appliedFilters = "" Then
                appliedFilters = "DIVISION: " + TempVars!tempDivisionSelection + Chr(13) & Chr(10)
            Else
                appliedFilters = appliedFilters + "DIVISION: " + TempVars!tempDivisionSelection + Chr(13) & Chr(10)
            End If
        End If
        
        If TempVars!tempProductSelection <> "" Then
            If appliedFilters = "" Then
                appliedFilters = "PRODUCT: " + TempVars!tempProductSelection + Chr(13) & Chr(10)
            Else
                appliedFilters = appliedFilters + "PRODUCT: " + TempVars!tempProductSelection + Chr(13) & Chr(10)
            End If
        End If
        
        If TempVars!tempUseAgain <> "" Then
            If appliedFilters = "" Then
                appliedFilters = "USE AGAIN: " + TempVars!tempUseAgain + Chr(13) & Chr(10)
            Else
                appliedFilters = appliedFilters + "USE AGAIN: " + TempVars!tempUseAgain + Chr(13) & Chr(10)
            End If
        End If
        
        TempVars.Add "tempCallingForm", Me.Name
        
        TempVars.Add "tempAppliedFilters", appliedFilters
        DoCmd.Close acForm, "frmAttyReportFilter"
        DoCmd.openReport "rptAttyRecordSearch", acViewReport, "", TempVars!tempFirmReportFilter, acNormal
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        TempVars.Add "alreadyRun", "False"
        DoCmd.SetWarnings True
        ReDim litTypeOptionsArray(5) As String
        'create an array of the radio buttons
        litTypeOptionsArray(0) = "littype_5_Appeal"
        litTypeOptionsArray(1) = "littype_1_Coverage"
        litTypeOptionsArray(2) = "littype_2_Defense"
        litTypeOptionsArray(3) = "littype_6_EC"
        litTypeOptionsArray(4) = "littype_3_Monitoring"
        litTypeOptionsArray(5) = "littype_4_Subrogation"
       
        If TempVars!tempSelections <> "" Then
            TempVars.Add "tempSelections", RTrim(TempVars!tempSelections)
            Dim mySelections() As String
            mySelections = Split(TempVars!tempSelections, " ")
            
            For Each ctl In Me.Controls
                If (ctl.ControlType = 106) Then
                'if it is an option button, then check to see if the option button matches any element of the litTypeOptionsArray
                        For i = 0 To 5
                            If ctl.Name = litTypeOptionsArray(i) Then
                                'if the option button matches the litTypeOptionsArray, then check to see if any of those controls are selected
                                If (mySelections(i)) = "Yes" Then
                                    'if the option button is selected, then check to see if litTpyeFilter already contains data
                                    ctl.Value = -1
                                    'set the selection within the selectedLitType array to yes
                                Else
                                    ctl.Value = 0
                                End If
                            End If
                        Next i
                End If
            Next
    
        Else
            Me.littype_1_Coverage.Value = 0
            Me.littype_2_Defense.Value = 0
            Me.littype_3_Monitoring.Value = 0
            Me.littype_4_Subrogation.Value = 0
            Me.littype_5_Appeal.Value = 0
            Me.littype_6_EC.Value = 0
        End If
        If TempVars!tempStateSelection <> "" Then
            Me.cmboState.Value = TempVars!tempStateSelection
        Else
            Me.cmboState.Value = "All"
        End If
        If TempVars!tempDivisionSelection <> "" Then
            Me.cmboDivision.Value = TempVars!tempDivisionSelection
        Else
            Me.cmboDivision.Value = "All"
        End If
        'If TempVars!tempAttySelection <> "" Then
        '    Me.cmboAtty.Value = TempVars!tempAttySelection
        'Else
        '    Me.cmboAtty.Value = "All"
        'End If
        If TempVars!tempProductSelection <> "" Then
            Me.cmboProduct.Value = TempVars!tempProductSelection
        Else
            Me.cmboProduct.Value = "All"
        End If
        If TempVars!tempUseAgain <> "" Then
            Me.cmboUseAgain.Value = TempVars!tempUseAgain
        Else
            Me.cmboUseAgain.Value = "All"
        End If
    
    End Sub
    
    Public Function appliedLitTypeFilters(litTypes() As String)
       'create an array of litigation types
        ReDim litTypeOptionsArray(5) As String
        litTypeOptionsArray(0) = "Appeal"
        litTypeOptionsArray(1) = "Coverage"
        litTypeOptionsArray(2) = "Defense"
        litTypeOptionsArray(3) = "Extra-contractual"
        litTypeOptionsArray(4) = "Monitoring"
        litTypeOptionsArray(5) = "Subrogation"
        Dim currentLitTypeSelections  As String
        For i = 0 To 5
            If (RTrim(litTypes(i)) = "Yes") Then
                currentLitTypeSelections = currentLitTypeSelections + litTypeOptionsArray(i) + Chr(13) & Chr(10)
            End If
        Next
        appliedLitTypeFilters = currentLitTypeSelections
    End Function
    

    Monday, February 29, 2016 5:25 PM

All replies

  • Hi Casey_M,

    Since other four threads which you post are the same issue, and your posts are much long, I delete other four threads. Thanks for your understanding, and I suggest you keep on following this thread.

    With this much complex issue, I suggest you create a simple Access file which could reproduce your issue, then share it through OneDrive, and share us details steps to reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 1, 2016 2:31 AM