Answered by:
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 StringstrSQL = "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 SubPrivate Sub Form_Current()
Dim i As Integer
Dim arr() As Variant
Dim lngLastParentCTO, lngLastCTO As Long
Dim strFilter As String
Dim frm As FormSet 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 tablesIf 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 SubFriday, 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