Apply multiple filters to subform

[Elwood07]

Hi,


I have a subform called "sbfrmEditRecords" with multiple unbound text boxes for a user to enter criteria to search and then edit criteria in a subform datasheet.

I've gotten the code to work when I want to apply a filter based on just one of the text boxes, but I can't figure out how to combine. I'm also going need to allow to return all data in a field if it's left blank, and for text fields, allow for "like" argument, but don't know how to modify. I tried linking with AND but doesn't work.

These are the working independent codes I have for the date and for the server name.

Private Sub cmdsrchDate_Click()


Me.sbfrmEditRecords.Form.Filter = "[TransDate]= #" & DateValue(Me.srchDate) & "#"
Me.sbfrmEditRecords.Form.FilterOn = True


End Sub

Private Sub cmdSrchOutlet_Click()


Me.sbfrmEditRecords.Form.Filter = "[ServerName]= '" & Me.srchServerName & "'"
Me.sbfrmEditRecords.Form.FilterOn = True


End Sub

Your help is appreciated!

[June7]

Review http://www.allenbrowne.com/ser-62code.html

[Elwood07]

Thanks for the link. I'll take a look through and see if I can possibly understand it.

[Elwood07]

Does this exact code work for searching in a subform?
Using the date filter for example, do I just change

'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 & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If

to

'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.sbfrmname.txtStartDate) Then
        strWhere = strWhere & "([EnteredOn] >= " & Format(Me.sbfrmname.txtStartDate, conJetDate) & ") AND "
    End If

?

I have a lot of difficulty referring to subforms.
Thanks.

[Elwood07]

So I took the code from the link and tried to apply it just to the date field.

the "srchDate" box is in the main form and [TransDate] is the control in the subform.
When I click the button, nothing happens. No error, no nothing, using the code below. I'm sure it has something to do with the subform reference, but I don't know how to correct. I'm also tried Forms.sbfrmEditRecords.[TransDate], but also didn't work.
'Thanks.

Private Sub cmdsrchDate_Click()

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.


 'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.srchDate) Then
        strWhere = strWhere & "([Forms]![sbfrmEditRecords]![TransDate] = " & Format(Me.srchDate, 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

[June7]

No, will not work as is for subform.

Is the main form bound to data? If not, perhaps could use a single form in Continuous View and put the search controls in form header section.

Did you download the example database from the tutorial?

[Elwood07]

I did and it works fine for main form.

The form is not bound - do you mean put the controls in the subform header? The same syntax would work if all was contained within the subform?

[Elwood07]

Ok Great that works! Which solves the problem for one of my forms, but the other one is a search I'm trying to execute on a split form, so it needs to be able to search by both info on the parent and info on on subform, so if the syntax can be adjusted, it would be ideal.

Thanks again for the suggestion!

[June7]

It should work as is on a split form (not a form/subform arrangement).

So what do you mean by 'split' form? Main form bound to one dataset and subform bound to another?

[Elwood07]

I used confusing language there.
My main form is the split form as it has a datasheet on the bottom. The main form also has a subform. The subform is a continuous form. The datasheet will expand to show subform details.
I'm trying to search items both on the main form and the subform.
The form is going to be used by our payroll department to make edits, so I can't base the form off a query, which would make this so much easier. They are not comptuer savvy at all, so i do not want them opening various tables to make the changes. And I can't have them scrolling through all records in the subforms, as there is too much data. Basically it's a form for service charge distribution in our outlets. The main form houses the info on the date, outlet, lead server and total check gratuity. the Subform is a list of all the team member that were paid out gratuity off the check. depending on the info payroll is given, they need to be able to query off the lead server info and the payee info.

[June7]

Why can't the form have a query as RecordSource?

I don't use the split form often and never have seen it with a subform. Regardless, referencing a subform from the main requires referencing through the subform container control. I always name the control different from the object it holds, like ctrDetails. So code behind main form to apply criteria to subform Filter property like:

Me.ctrDetails.Form.Filter = strCriteria
Me.ctrDetails.Form.FilterOn = True

Why do you need to filter the subform? If it is linked to the main form it will display only related records.