Data Filtering in Continuous Form!

[cap.zadi]

I have a continuous form data records and it is filtered via selecting some criteria in some fields at form header: This method was copied from web source (Dont remembered).

Its working fine but had few issues:

a. There are some other text fields at form header which are not a part of filtering criteria. So at each click for clearance of criteria, showing error (Run Time Error "3314" and showing name of fields) of those fields but still clear the criteria.
b. After Clearing the criteria fields, the data is not going back to original position as non filtered.

Private Sub cmdReset_Click()    Dim ctl As Control
    
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
       Me.FilterOn = False
End Sub

The Code for Criteria Filtering:

Private Sub cmdFilter_Click()'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.CboMaterial) Then
        strWhere = strWhere & "([Material] = """ & Me.CboMaterial & """) AND "
    End If
    
    If Not IsNull(Me.CboMachine) Then
        strWhere = strWhere & "([MachineNo] = """ & Me.CboMachine & """) AND "
        
    End If
    If Not IsNull(Me.CboSSize) Then
        strWhere = strWhere & "([SieveSize] = " & Me.CboSSize & ") AND "
        
    End If
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.TxtStartDate) Then
        strWhere = strWhere & "([SampleDate] >= " & Format(Me.TxtStartDate, conJetDate) & ") AND "
    End If
    
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.TxtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([SampleDate] < " & Format(Me.TxtEndDate + 1, conJetDate) & ") 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.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    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
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

[cap.zadi]

I use another way out to clear the criteria fields after selection:

Private Sub CmdClear_Click()

[CboComposition].Value = ""
[TxtStartDate].Value = ""
[TxtEndDate].Value = ""
[CboSampleLocation].Value = ""
End Sub

But got the issue of Run Time Error "13" Type Match, if to select another field of criteria one after one to filter further records and if debug it then it take to the below code:

strWhere = strWhere & "([SampleDate] < " & Format(Me.TxtEndDate + 1, conJetDate) & ") AND "

[June7]

If you want to restore all records, turn filter off:

Me.FilterOn = False

Don't set controls to empty string, use Null.

[cap.zadi]

Hi June.

Thanks for your usual Valuable feedback.

I used Null instead of empty strings which helps to kickoff both Run Time Errors.


[CboName].Value = Null
Etc.

I did not get the idea for Me.FilterOn = False

Since after clearance selection criteria there is no filter.

[June7]

Your original code in post 1 shows turning the filter off. The code in post 2 does not show that.

Just clearing the controls does not remove the filter. Filter is still applied.

Code building the filter criteria checks controls for Null, not empty string. An empty string is not same as Null. So clearing the controls by setting to empty string causes error if the cmdFilter button is clicked and no entry is made to replace the empty string.

[cap.zadi]

Yeah you are right.

Private Sub cmdReset_Click() Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

but i replaced this clearing all fields at form header with simple code for all fields.

[CboName].Value = Null

and its taken away all the error messages and filtering the data properly but the issue after selecting certain criteria and clearing those, the results are not going back to original data.

the code used for the filtering is the same given above in post 1.

So what do you suggest in this case now?

[June7]

Did you include the Me.FilterOn = False in your revised code?

Code works for me. If you want to provide db for analysis, follow instructions at bottom of my post.

[cap.zadi]

Hi

This is code below to clear the selection criteria

Private Sub CmdClear_Click()


[CboField1].Value = Null
[TxtStartDate].Value = Null
[TxtEndDate].Value = Null
[CboField2].Value = Null


End Sub

Where to apply the Me.FilterOn criteria and how?

[June7]

In the same place as the code in post 6 - just before End Sub.

[cap.zadi]

Hi

Thanks. Its working fantastic.