locked
Form Filter asking for field that does not exist and is not asked for in code? RRS feed

  • Question

  • I have a parent form 'Form1' which has two subforms, a continous form "Demographic_Dashboard" and a single data form "Demographics". The Demgraphics subform has 5 subforms nested within it, one of which, Non_Compliance, is causing the problem.

    When triggering the dbl_click event on the ID text box of a record from "Demographic_Dashboard", the Demgraphics form is updated. However there is problem when it asks for a parameter, "Non_Compliance.ULI" that does not exist and is not in any part of the code or any SQL statement.

    I have tried three ways, starting with opening the form using do.cmd openform, applying a the form.filter method, and changing the form.recordsource. Only the docmd.openform works, but I do not want to open a sepearate form, I want to update the subform "Demographics".

    Private Sub ULI_DblClick(Cancel As Integer)
    Dim frm As Form
    Dim strSQL As String

    strSQL = "SELECT * FROM Demographic WHERE ULI = " & ULI
    Set frm = Forms!Form1!Demographic.Form
    frm.RecordSource = strSQL
    'frm.Filter = "ULI = " & ULI
    'frm.FilterOn = True
    'DoCmd.OpenForm "Demographic", acNormal, , "ULI = " & ULI
    End Sub

    Private Sub Form_Current()
    Dim i As Integer
    Dim arr() As Variant
    Dim lngLastParentCTO, lngLastCTO As Long
    Dim strFilter As String
    Dim frm As Form

    Set frm = Forms!Form1!Demographic.Form
    'Set frm = Me
    lngLastParentCTO = DMax("Parent_CTO_Id", "Issuance", "ULI = " & ULI)
    'Debug.Print lngLastParentCTO
    lngLastCTO = DMax("CTO_Id", "Issuance", "ULI = " & ULI)
    'Debug.Print lngLastCTO

    '============== Filter subform CTOs =============================
    frm!CTOs.Form.Filter = "Issuance_Serial = 0"
    'Forms!Demographic!CTOs.Form.FilterOn = True
    frm!CTOs.Form.FilterOn = True
    '============== Filter subform Renewals =========================
    'Compose strFilter based on option group Frame61
    If Frame61 = 1 Then 'Show Renewals of the last CTO
        strFilter = "Issuance_Serial > 0 AND Parent_CTO_Id = " & lngLastParentCTO
    ElseIf Frame61 = 2 Then 'Show All Renewals
        strFilter = "Issuance_Serial > 0 AND ULI = " & ULI
    End If
    'Apply the filter
    frm!Renewals.Form.Filter = strFilter
    frm!Renewals.Form.FilterOn = True


    '============= Compose strFilter for Review Panel, Amendments and Non-Compliance. _
    'These three do not have ULI stored in their tables

    If Frame61 = 1 Then
        strFilter = "CTO_Id = " & lngLastCTO
    ElseIf Frame61 = 2 Then
        'get array of all CTOs belonging to this ULI from table Issuance
        arr = fDLookUpArray("CTO_Id", "Issuance", "ULI = " & ULI)
        'compose filter string by assiging the first CTO
        strFilter = "CTO_Id = " & arr(0)
        'Edit the filter string if ULI has more than one CTO
        If UBound(arr) > 0 Then
            For i = LBound(arr) + 1 To UBound(arr)
                strFilter = strFilter & " OR CTO_Id = " & arr(i)
            Next i
        End If
    End If

    '============ Filter subform Review Panel ======================
        'Apply the filter
        frm!Review_Panel.Form.Filter = strFilter
        frm!Review_Panel.Form.FilterOn = True

    '============ Filter subform Amendment ======================
        'Apply the filter
        frm!Amendment.Form.Filter = strFilter
        frm!Amendment.Form.FilterOn = True
       
    '============ Filter subform Non-Compliance ======================
        'Apply the filter
        frm!Non_Compliance.Form.Filter = strFilter
        frm!Non_Compliance.Form.FilterOn = True
    End Sub

    Friday, December 23, 2011 6:55 PM

Answers

  • I recreated the nested subform that was creating the problem, "Non_Compliance". It solved the problem. I may have copy and pasted one of the other subforms when desinging the form Demographic and the Parent - Child relationship based on the field ULI may have some how lingered on.
    • Marked as answer by Imran J Khan Friday, December 23, 2011 8:43 PM
    Friday, December 23, 2011 8:43 PM

All replies

  • you might want to check the filter property of the forms
    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.
    • Edited by -suzyQ Friday, December 23, 2011 7:40 PM
    Friday, December 23, 2011 7:40 PM
  • you might want to check the filter property of the forms
    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    I checked the filter property of all the forms. All are empty except for Demographic, which the event code of the Demographic_Dashboard sets. I removed that filter as well but the same issue persists.
    • Marked as answer by Imran J Khan Friday, December 23, 2011 8:43 PM
    • Unmarked as answer by Imran J Khan Friday, December 23, 2011 8:43 PM
    Friday, December 23, 2011 7:50 PM
  • I recreated the nested subform that was creating the problem, "Non_Compliance". It solved the problem. I may have copy and pasted one of the other subforms when desinging the form Demographic and the Parent - Child relationship based on the field ULI may have some how lingered on.
    • Marked as answer by Imran J Khan Friday, December 23, 2011 8:43 PM
    Friday, December 23, 2011 8:43 PM