FilterOn = False not allowing Main Form to ReQuery

[cage4000]

First let me say this is my first post at this form so please bear with me. I have always found the answers I needed here without posting so I want to do a big shout out to this community and how knowledgeable all of you are. Thank you for the years of support!
Now to my question =)

I’m building a continues Form that has 2 text boxes that are dates that I use to lookup data on the from between the 2 dates. After inputting the 2 dates I can update the form after I click a requery button I made which works with no problems.

Requery Button Code:

Private Sub cmdReset_Click()

Me.cmbType.Value = "Type"
Me.cmbFndBy.Value = "Found By"
Me.cmbFndBy.BackColor = 11920639
Me.cmbFoundIn.Value = "Found In"
Me.cmbAprSup.Value = "Approving Sup"
Me.cmbAprSup.BackColor = 11920639
Me.frmAllorEmpt = 1

Me.Filter = ""
Me.FilterOn = False

Me.Requery

End Sub

I then put code in to filter my drop down fields on my form which works with no problem (see below).

Filter Code:

Private Sub FilterStatusForm()
    
    Dim strWhere As String

    'Make string
    If Me.cmbType <> "Type" Then
        strWhere = strWhere & "[Type] = '" & Me.cmbType & "' AND "
    End If

    If Me.frmAllorEmpt = 2 Then
        strWhere = strWhere & "Nz([TimeStamp], 0) = 0 AND "
    End If

    If Me.cmbFoundIn <> "Found In" Then
        strWhere = strWhere & "[ProbFoundIn]='" & Me.cmbFoundIn & "' AND "
    End If
    
    If Not IsNull(Me.cmbAprSup.Value) And Not Me.cmbAprSup.Value = "Approving Sup" Then
        strWhere = strWhere & "[ApprovingSup]='" & Me.cmbAprSup & "' AND "
    End If
    
    If Not IsNull(Me.cmbFndBy.Value) And Not Me.cmbFndBy.Value = "Found By" Then
        strWhere = strWhere & "[FndBy]='" & Me.cmbFndBy & "' AND "
    End If
    

    'Apply filter
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.Filter = strWhere
        Me.FilterOn = True
    Else
        Me.cmbType.Value = "Type"
        Me.cmbFndBy.Value = "Found By"
        Me.cmbFoundIn.Value = "Found In"
        Me.cmbAprSup.Value = "Approving Sup"
        Me.frmAllorEmpt = 1
        Me.Filter = ""
        Me.FilterOn = False
    End If

End Sub

The error I am finding is that after I use the filter code I’m able to requery the form, but if I clear all the fields or run my refresh button it will not requery the form. Any help is most appreciative.

:banghead:

 

[James Deckert]

>>it will not requery the form.
you're telling it not to filter
Me.FilterOn = False
You are clearing the fields, but if you want it to requery after clearing the fields
Me.FilterOn = True

 

[spikepl]

1. Define "it will not requery" - it punches you on the nose while it screams no no or what happens exactly when you do what?

2. Your combos have one column only, so you should reset them to Null. You can get the guiding text by inserting into the Format property of each combo:
@;"The text you wish to display"

 

[cage4000]

Hi James Deckert,

I have tried changing False to True as well but it still will not requry the active form. Great suggestion, thank you.


Hi spikepl,

Sorry for the lack of clarification. The Form will not requery after i use the FilterStatusForm() and then use the cmdReset_Click() or if i use the FilterStatusForm() and then change the values back to default for all records.

I tried what you suggested with resetting them to null and than using the (@;"The text you wish to display") inserting into the Format property of each combo, but it would not leave the selection i picked in the combo box.

Also, here is the strange part of this issue. If i update the 2 Date text boxes the form will not requery but if i open up the query that feeds data to this form it will update its data. So in essence the issue does not lie in the data behind this form only in the form itself.

 

[James Deckert]

You have this line in two places, did you change them both?

 

[cage4000]

Yes i did. I tried:

Me.Filter = ""
        Me.FilterOn = False

then i tried

Me.Filter = ""
        Me.FilterOn = True

then

Me.Filter = ""
        Me.FilterOn = False
        Me.FilterOn = True

then

Me.Filter = ""
        Me.FilterOn = True
        Me.FilterOn = False

but no mater the combo i use the true/false it still will not allow me to requery the form.

 

[cage4000]

I found the answer. i had to take out the where statement from the query and build it into the filter to make it work.

Here is the code:

Private Sub FilterStatusForm()
    
    Dim strWhere As String

    'Make string
    If Me.cmbType <> "Type" Then
        strWhere = strWhere & "[Type] = '" & Me.cmbType & "' AND "
    End If

    If Me.frmAllorEmpt = 2 Then
        strWhere = strWhere & "Nz([TimeStamp], 0) = 0 AND "
    End If

    If Me.cmbFoundIn <> "Found In" Then
        strWhere = strWhere & "[ProbFoundIn]='" & Me.cmbFoundIn & "' AND "
    End If
    
    If Not IsNull(Me.cmbAprSup.Value) And Not Me.cmbAprSup.Value = "Approving Sup" Then
        strWhere = strWhere & "[ApprovingSup]='" & Me.cmbAprSup & "' AND "
    End If
    
    If Not IsNull(Me.cmbFndBy.Value) And Not Me.cmbFndBy.Value = "Found By" Then
        strWhere = strWhere & "[FndBy]='" & Me.cmbFndBy & "' AND "
    End If
    
     If Nz(Me.txtDT, "") <> "" And Nz(Me.txtDTE, "") <> "" Then
        strWhere = strWhere & "[DnTProbIdentified]>=" & Format(Me.txtDT, "\#yyyy-mm-dd\#") & " AND "
  
        strWhere = strWhere & "[DnTProbIdentified]<=" & Format(Me.txtDTE, "\#yyyy-mm-dd\#") & " AND "
    End If

    'Apply filter
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.Filter = strWhere
        Me.FilterOn = True
    Else
        Me.cmbType.Value = "Type"
        Me.cmbFndBy.Value = "Found By"
        Me.cmbFoundIn.Value = "Found In"
        Me.cmbAprSup.Value = "Approving Sup"
        Me.frmAllorEmpt = 1
        Me.txtDT = ""
        Me.txtDTE = ""
        Me.Filter = ""
        Me.FilterOn = False
    End If

End Sub