Function to open school with error

[Ruegen]

I have a function which works fine for schools that are not filtered out of the list however some schools have a field that is not null and thus filtered out on the form



one particular form "schools found" is opened using an instance so the staff can open more than one school using the same form using the function below. My problem is sometimes I want to open a school that is filtered out of the form and because it gets filtered out on load the ID gets filtered out and the function fails - it can't find the object.

It's the line

If cycleFoundSchools(frm.Form!NewSchoolsID) Then        MsgBox "School " & IIf(IsNothing(frm.Form!SchoolName), "no name", frm.Form!SchoolName) & "  is already open, the opening form will close."

the cycleFoundSchools() function gets the id and compares it to any forms that are open with the same id - however if that is missing then that is where it will fail and I need to cover for that.

help!

Public Sub OpenThisSchool(Optional schoolid As Variant, Optional AreaID As Variant, Optional StateID As Variant, Optional EnrollmentAbove As Variant)
If intCountFrmSchool > 3 Then


MsgBox "You have opened too many school forms at one time, all other searched school forms now will close", vbExclamation


Call CloseAllClients




End If


If intCountFrmSchool < 0 Then
intCountFrmSchool = 0
End If
intCountFrmSchool = intCountFrmSchool + 1




Dim strWhere As String
Dim lnglen As Long


If Not IsMissing(schoolid) Then
strWhere = "[NewSchoolsID] = " & schoolid & " AND "
End If


If Not IsMissing(AreaID) Then
strWhere = strWhere & "[AreaID] = " & AreaID & " AND "
End If


If Not IsMissing(StateID) Then
strWhere = strWhere & "[StateID] = " & StateID & " AND "
End If


If Not IsMissing(EnrollmentAbove) Then
strWhere = strWhere & "[Enrollment] >= " & EnrollmentAbove & " AND "
End If






    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lnglen = Len(strWhere) - 5
    If lnglen <= 0 Then     'Nah: there was nothing in the string.
        
        'clears text boxes


        
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lnglen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        




  'Purpose:    Open an independent instance of form frmClient.
    Dim frm As Form
    
    'Open a new instance, show it, and set a caption.
    Set frm = New Form_frmSchoolsFound
    frm.Visible = True
    frm.Filter = strWhere
    frm.FilterOn = True
    frm.Caption = IIf(IsNothing(frm.Form!SchoolName), "no name", frm.Form!SchoolName) '"Schools Found" &
      Debug.Print "nothing " & frm.Form!NewSchoolsID
        If cycleFoundSchools(frm.Form!NewSchoolsID) Then
        MsgBox "School " & IIf(IsNothing(frm.Form!SchoolName), "no name", frm.Form!SchoolName) & "  is already open, the opening form will close."
        Else
        'Append it to our collection.
        clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)
        End If
      
    Set frm = Nothing
    End If
End Sub

[Ruegen]

eh nevermind I used this, not the best solution but all I could think of

Dim strNewSQL As String
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs(frm.RecordSource)
    Debug.Print "a ha " & qdf.SQL
    strNewSQL = qdf.SQL
    Set qdf = Nothing
    Debug.Print "WHERE " & Chr(40) & Chr(40) & Chr(40) & "tblSchools.Removed" & Chr(41) & " Is Null" & Chr(40) & Chr(40)
    strNewSQL = Replace(strNewSQL, "WHERE " & Chr(40) & Chr(40) & Chr(40) & "tblSchools.Removed" & Chr(41) & " Is Null" & Chr(41) & Chr(41), "")
    frm.RecordSource = strNewSQL
    frm.Filter = strWhere
    Debug.Print vbCrLf & "look " & vbCrLf & frm.RecordSource