FilterOn = False not allowing Main Form to ReQuery (1 Viewer)

C

cage4000

Registered User.
Local time
Yesterday, 20:10
Joined
Oct 14, 2015
Messages
49
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:
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:
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:
 
J

James Deckert

Continuing to Learn
Local time
Yesterday, 22:10
Joined
Oct 6, 2005
Messages
189
>>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

spikepl

Eledittingent Beliped
Local time
Today, 05:10
Joined
Nov 3, 2010
Messages
6,142
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"
 
C

cage4000

Registered User.
Local time
Yesterday, 20:10
Joined
Oct 14, 2015
Messages
49
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.
 
J

James Deckert

Continuing to Learn
Local time
Yesterday, 22:10
Joined
Oct 6, 2005
Messages
189
You have this line in two places, did you change them both?
 
C

cage4000

Registered User.
Local time
Yesterday, 20:10
Joined
Oct 14, 2015
Messages
49
Yes i did. I tried:

Code: 
Me.Filter = ""
        Me.FilterOn = False

then i tried

Code: 
Me.Filter = ""
        Me.FilterOn = True

then

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

then

Code: 
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.
 
C

cage4000

Registered User.
Local time
Yesterday, 20:10
Joined
Oct 14, 2015
Messages
49
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:

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
 
You must log in or register to reply here.

Similar threads

smtazulislam
Solved Unbound Combo box need multiple value
2
Replies
20
Views
1,764
smtazulislam
smtazulislam
C
Solved getting filtered records
Replies
11
Views
708
ClaraBarton
C
C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
263
CraigDouglas
C
T
Filter Form From ComboBox and date field
Replies
4
Views
342
Josef P.
J
B
Solved Filtering Subform based off carried over data
Replies
5
Views
328
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom