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

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

Answer 1

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.

Answer 2

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.

Answer 3

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.