Filter a Subform

[MonkeyChico]

I have some great VBA code that I used in the past to filter a form. Now I'm trying to apply this code to filter a subform but I have no idea how to modify it (I know pretty much nothing about VBA, I just relpicate what I can find on here and Google). My unbound filter fields/combo boxes are on the main form.

Here's what I have:
- Main form uses tbl_Quote Book List
- Subform uses tbl_Quoting Data

Here's the code:

Private Sub cmdFilter_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.


If Not IsNull(Me.qcustomername) Then
strWhere = strWhere & "([Customer name] Like ""*" & Me.qcustomername & "*"") AND "
End If

If Not IsNull(Me.qquotemonth) Then
strWhere = strWhere & "([Quote Month] = """ & Me.qquotemonth & """) AND "
End If

If Not IsNull(Me.qLOB) Then
strWhere = strWhere & "([Line of Business] = """ & Me.qLOB & """) AND "
End If

If Not IsNull(Me.qquotestatus) Then
strWhere = strWhere & "([Quote Status] = """ & Me.qquotestatus & """) AND "
End If

If Me.QQaflag = -1 Then
strWhere = strWhere & "([QA Flag] = True) AND "
ElseIf Me.QQaflag = 0 Then
strWhere = strWhere & "([QA Flag] = False) AND "
End If


'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)

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True

End If

End Sub

 

[boblarson]

Everything is the same up until this point:

Me.Filter = strWhere
Me.FilterOn = True

So you would need to know what the subform control name is (it is the control on the main form that houses the subform) as you need to use its name and not the subform name (unless they are exactly the same).

So, something like this:

Me.YourSubformControlNameHere.Form.Filter = strWhere
Me.YourSubformControlNameHere.Form.FilterOn = True

And you leave the .Form. part exactly as shown (as it tells Access you want something on the subform itself and not the subform control).

 

[MonkeyChico]

Money as always, thank you Bob!

One quick follow-up question. I can't figure out how to apply what you told me to the clear filter code. When I click the clear filter button I get the following error:


"Run time error '3333'.

Records in table 'tbl_Quoting Data' would have no record on the 'one' side."


Here's the code for the clear filter button:

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

 

[boblarson]

You need to change the Me.FilterOn to:

Me.YourSubformControlNameHere.Form.Filter = ""
Me.YourSubformControlNameHere.Form.FilterOn = False

 

[MonkeyChico]

Thanks Bob. I cut down the code to just

Me.YourSubformControlNameHere.Form.Filter = ""
Me.YourSubformControlNameHere.Form.FilterOn = False

It now clears the filter. I think I can't get the clear all controls code to work because I have non-filter text boxes in the header the identify the main form record. Could you give me a one line example as to how I would change the following code to direct it to clear out specific controls that I'd name? I.e. clear out text box qquotemonth. Then clear out text box...

Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = Null
End Select
Next

 

[boblarson]

Just put the word clear in the control's TAG property of the ones you want cleared. Then you can use:

For Each ctl In Me.Section(acHeader).Controls
[COLOR="red"]If ctl.Tag = "clear" Then[/COLOR]
   Select Case ctl.ControlType
      Case acTextBox, acComboBox
          ctl.Value = Null
      Case acCheckBox
          ctl.Value = Null
   End Select
[COLOR="Red"]End If[/COLOR]
Next

 

[MonkeyChico]

Brilliant! Thank you Bob.