Remove From My Forums

Database application crashing on 'no data'

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

0

Sign in to vote

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

Asked by:

Database application crashing on 'no data'

Question

All replies