Combining Filters (1 Viewer)

[MilaK]

Hello,

I apply a complex filter to a continuous subform when the main form opens.

On that subform there is a combobox that user can use use to filter data further.

Is there a way to preserve the first filter and add addition filter (input from the combobox)?
Do I need to combine the two filters, or is there an easier option?

Also, is there advantage of using recordsource vs. filter for a Form in Access?

Thanks, Mila

 

[Ranman256]

The sub form filter has nothing to do with the master filter.
The sub data belongs to the master record.
To filter Sub data, you need a set of filters for the sub. Items on the main form would control master records and either ,
Filters on the sub form, or
a box on the master form to separate master filters from the sub filters.
Otherwise the user would be confused.

But yes, you can filter the sub data ,different from the master filters.

 

[MilaK]

I forgot to mention that the two forms are not linked, but the Current Form event of the subform filters the Main form.

I’m interesting in filtering the subform that is filtered independently from main form. Sorry, I'm not understanding how to combine the two filters that are designed to filter the same form.

The first filter code is in another form button click event that opens the Main form with the subform and filters the subform. The combobox on the subform contains distinct sample names and designed to add an additional filter to pre-filtered subform. Thanks

 

[sneuberg]

I don't know if this is the best way but you can take whatever is in the filter and concatenate to it. To test this in the attached database I have in the main form a command button with this code.

Private Sub FilterSub_Click()

Dim strCriteria As String
strCriteria = "[F1] = '" & Me.FilterValue & "'"
If Len(Me.subForm.Form.Filter & vbNullString) = 0 Then
    Me.subForm.Form.Filter = strCriteria
Else
    Me.subForm.Form.Filter = Me.subForm.Form.Filter & " And " & strCriteria

End If
Me.subForm.Form.FilterOn = True
 

End Sub

On the subform I have another command button with this code.

Private Sub Filter_Click()

Dim strCriteria As String
strCriteria = "[F2] = '" & Me.FilterValue & "'"
If Len(Me.Filter & vbNullString) = 0 Then
    Me.Filter = strCriteria
Else
    Me.Filter = Me.Filter & " And " & strCriteria
End If
Me.FilterOn = True


End Sub

Each of these check to see if anything is in the filter. If not is just puts in the criteria by itself. If there is something already in the filter it Ands the criteria with whatever is there.

 

[MilaK]

This approach will only works the first time the user selects a sample from a combobox. When the user selects another sample, both the first sample and the second samples will be combined in the search criteria and the filter will fail.

Perhaps, the best way to do this is to add a public variable to pass the search criteria from one form to another? Sorry, if I'm wrong... Mila

 

[sneuberg]

This approach will only works the first time the user selects a sample from a combobox. When the user selects another sample, both the first sample and the second samples will be combined in the search criteria and the filter will fail.

It doesn't fail. It just doesn't do what you want. If you run my test case and go back and forth between the subform and main form adding to the filter you might get something like:

[F1] = 'D' And [F2] = 'A' And [F1] = 'A' And [F2] = 'H' And [F1] = 'K'

That's a legitimate filter value. Obviously you want to clear the filter at some point and that would be up to you. How do you want this to behave? You could have the combo box just set the filter value (take out the else part) for example


I don't see how a global would help. The point of my test was to show that you could use the subform filter property as a storage location. You can read it, write to it and turn the filter on or off. I think the only thing you need to to figure out how you want this to function.

 

[MilaK]

I understand that I can combine filters, however, I want to clear the combobox filter each time the user selects an item from a combobox.

Let's say that the first filter (not dependent of the combobox selection) is a contant filter that I will always apply to the form and the combobox filter will change each time a user selects an item from a combobox.

Thanks, Mila

 

[sneuberg]

Ok sorry I didn't really read your post correctly. It does seem that a global would be helpful here. I'd try adding something like this to the main form load

TempVars.Add "SubFilterValue", Me.subForm.Form.Filter

Then in the combo afterupdate

Private Sub ComboBoxName_AfterUpdate()

Dim strCriteria As String
strCriteria = "[SomeFieldName] = '" & Me.ComboBoxName & "'"  'Assuming field is text
Me.subForm.Form.Filter = TempVars("SubFilterValue") & " And " & strCriteria
Me.subForm.Form.FilterOn = True
 
End Sub

What filtered this form when it loaded? Is there any way to get the filter value from it and avoid this TempVar?

 

[MilaK]

Great! There is a continuous form “Project” that has a button next to each record. The click of a button lunches the Main form with subform. The filter code is in the click event of that button. The “Project” form will remain open until the “Main” form is closed.