Can you filter two subforms at the same time?

[haggis999]

I have an unbound Access 2003 form that contains two subforms (SF1 and SF2). The main form contains a ReqLastName textbox for the user to enter a surname. The subforms are bound to a couple of stored queries that each have [ReqLastName] as the criterion in their respective LastName fields. If the user types 'Smith' in the ReqLastName textbox then the subforms duly display the relevant info for all the Smiths in the database. So far, so good.

Because surnames like Smith are quite common, I also have three additional textboxes to specify optional filters for FirstName, City and Email. After entering the required filter values, they are applied by clicking on an ApplyFilter button. I also have a RemoveFilter button. Everything works as expected with SF1 but my problem is that the filter is not applied to SF2 until I click the ApplyFilter button for a second time and the RemoveFilter button never removes the filter from SF2. My first assumption was that there was a bug in my code but no such bug was apparent.

As an experiment, I then tried copying all my ApplyFilter button VBA code to a second button called ApplyFilter2. Everything related to SF2 was commented out of the code associated with the ApplyFilter button and everything related to SF1 was commented out of the code associated with the ApplyFilter2 button. The Remove Filter button code remained unchanged (it still set the FilterOn property to False for both SF1 and SF2). Everything now worked perfectly!

Can anyone explain why trying to filter both subforms with a single button click event does not work properly, while the identical code works fine when split between separate buttons for each subform? Is there some obscure reason why it is not possible to filter two subforms at the same time?

David

[June7]

Should be able to filter mutiple subforms from same procedure. Would have to review code. Post code or attach db for review.

[haggis999]

Here is the relevant VBA code. You will see that I slightly simplified some things in my OP. For example, my reference to SF1 and SF2 was a simplification of EntryHistorySF1 and EntryHistorySF2. This shows the original version of the btnApplyFilter_Click code, i.e. the lines referring to EntryHistorySF2 are not commented out.

David

================================================== ====
Option Compare Database
Option Explicit

Private Sub btnApplyFilter_Click()
On Error GoTo Err_btnApplyFilter_Click
Dim X As Boolean
X = False
Dim A As String
Dim B As String
Dim C As String
Dim ABC As String

If Trim(Nz(Me![ReqFirstName], "")) = "" Then
A = "N"
Else
A = "Y"
End If

If Trim(Nz(Me![ReqCity], "")) = "" Then
B = "N"
Else
B = "Y"
End If

If Trim(Nz(Me![ReqEmail1], "")) = "" Then
C = "N"
Else
C = "Y"
End If
ABC = A & B & C
Select Case ABC
Case "YYY"
Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "YYN"
Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "'"
X = True
Case "YNN"
Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "'"
X = True
Case "YNY"
Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "NYN"
Me.[EntryHistorySF1].Form.Filter = "[City]='" & Me![ReqCity] & "'"
Me.[EntryHistorySF2].Form.Filter = "[City]='" & Me![ReqCity] & "'"
X = True
Case "NYY"
Me.[EntryHistorySF1].Form.Filter = "[City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "NNY"
Me.[EntryHistorySF1].Form.Filter = "[Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "NNN"
X = False
End Select
If X = True Then
Me.[EntryHistorySF1].Form.FilterOn = True
Me.[EntryHistorySF2].Form.FilterOn = True
Else
Me.[EntryHistorySF1].Form.FilterOn = False
Me.[EntryHistorySF2].Form.FilterOn = False
End If


Exit_btnApplyFilter_Click:
Exit Sub
Err_btnApplyFilter_Click:
MsgBox Err.Description
Resume Exit_btnApplyFilter_Click

End Sub

Private Sub btnRemoveFilter_Click()
On Error GoTo Err_btnRemoveFilter_Click
Me.[EntryHistorySF1].Form.FilterOn = False
Me.[EntryHistorySF2].Form.FilterOn = False


Exit_btnRemoveFilter_Click:
Exit Sub
Err_btnRemoveFilter_Click:
MsgBox Err.Description
Resume Exit_btnRemoveFilter_Click

End Sub

Private Sub btnApplyFilter2_Click()
On Error GoTo Err_btnApplyFilter2_Click
Dim X As Boolean
X = False
Dim A As String
Dim B As String
Dim C As String
Dim ABC As String

If Trim(Nz(Me![ReqFirstName], "")) = "" Then
A = "N"
Else
A = "Y"
End If

If Trim(Nz(Me![ReqCity], "")) = "" Then
B = "N"
Else
B = "Y"
End If

If Trim(Nz(Me![ReqEmail1], "")) = "" Then
C = "N"
Else
C = "Y"
End If
ABC = A & B & C
Select Case ABC
Case "YYY"
'Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "YYN"
'Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [City]='" & Me![ReqCity] & "'"
X = True
Case "YNN"
'Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "'"
X = True
Case "YNY"
'Me.[EntryHistorySF1].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[FirstName]='" & Me![ReqFirstName] & "' And [Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "NYN"
'Me.[EntryHistorySF1].Form.Filter = "[City]='" & Me![ReqCity] & "'"
Me.[EntryHistorySF2].Form.Filter = "[City]='" & Me![ReqCity] & "'"
X = True
Case "NYY"
'Me.[EntryHistorySF1].Form.Filter = "[City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[City]='" & Me![ReqCity] & "' And [Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "NNY"
'Me.[EntryHistorySF1].Form.Filter = "[Email1]='" & Me![ReqEmail1] & "'"
Me.[EntryHistorySF2].Form.Filter = "[Email1]='" & Me![ReqEmail1] & "'"
X = True
Case "NNN"
X = False
End Select
If X = True Then
'Me.[EntryHistorySF1].Form.FilterOn = True
Me.[EntryHistorySF2].Form.FilterOn = True
Else
'Me.[EntryHistorySF1].Form.FilterOn = False
Me.[EntryHistorySF2].Form.FilterOn = False
End If

Exit_btnApplyFilter2_Click:
Exit Sub
Err_btnApplyFilter2_Click:
MsgBox Err.Description
Resume Exit_btnApplyFilter2_Click

End Sub

[haggis999]

Apologies for the lack of indentation in my code sample. This was automatically removed when making my post. Is there a better way to attach code?

David

[June7]

Can't see anything wrong with the original code. Seems it should work. So guess next step if you want to pursue is to provide db (instructions at bottom of my post) so I can step debug code. Have you tried step debug? Do both Filter actions run?

Place code with code tags and indentation will be preserved. You can type the tags: [ code] code here [/code] or use the buttons on the Advanced Editor.

[haggis999]

It would be quite a struggle to create a reduced copy of my DB to get under the 2MB limit. I will first have another go at step debugging my code, as perhaps I missed something first time round. Thanks for the help so far.

David

[June7]

Could extract just the objects related to issue into another db.

Or upload to a fileshare site and post link to file.

Regardless, please run Compact and Repair and zip file before upload.

Consider this slimmed down version of code:

Private Sub btnApplyFilter_Click()
On Error GoTo Err_btnApplyFilter_Click

Dim A As String
Dim B As String
Dim C As String
Dim strFilter As String

A = Trim(Nz(Me![ReqFirstName], ""))
B = Trim(Nz(Me![ReqCity], ""))
C = Trim(Nz(Me![ReqEmail1], ""))

strFilter = IIf(A = "", "", "[FirstName]='" & A & "' And ")
strFilter = strFilter & IIf(B = "", "", "[City]='" & B & "' And ")
strFilter = strFilter & IIf(C = "", "", "[Email1]='" & C & "' And ")
strFilter = Left(strFilter, Len(strFilter) - 5)

Me.[EntryHistorySF1].Form.Filter = strFilter
Me.[EntryHistorySF2].Form.Filter = strFilter

Me.[EntryHistorySF1].Form.FilterOn = strFilter <> ""
Me.[EntryHistorySF2].Form.FilterOn = strFilter <> ""

Exit_btnApplyFilter_Click:
    Exit Sub
Err_btnApplyFilter_Click:
    MsgBox Err.Description
Resume Exit_btnApplyFilter_Click
End Sub

[haggis999]

Just after my last post I realised it would be quite easy to copy the relevant objects into a new DB and create a small edited version of the tables used by the queries. I have now attached this test DB, which is only 348kb, so hardly worth zipping.


David

[haggis999]

I forgot to mention that the only surname in the test version of my Entrants table is Smith, so use that in the 'Entry history for specified entrant' form.

David

[haggis999]

I've just given your slimmed down version of my code a check. As you probably expected, it has the same problems as before but is certainly much more succinct. My coding skills could do with some polishing, so many thanks for taking the time to suggest that improvement .

David

[June7]

I hope you got the version after I did all my edits on the post.

Actually, I am not having any issue with the original code. Both subforms filter. And both subforms unfilter. And my code also works. Maybe I need some different last names to test with. I will work on that.

EDIT: Even with more last names, still works. Can't replicate the issue on the db you provided.


An issue I do observe is that once any of the supplemental filters have been applied, can't change the LastName criteria. I really, really don't like parameterized queries but if you really want to use that method for filtering form, review http://datapigtechnologies.com/flash...tomfilter.html.


Why don't you use comboboxes?

[haggis999]

I have just rechecked my test DB and it exhibits just the problem I described in my OP. As exactly the same filter should be applied to both subforms, I just want to use one Apply Filter button (so just ignore the second Apply Filter button further down the page). If you enter Smith in the ReqLastName box, specify Peter as the FirstName filter and hit the top Apply Filter button then both subforms should shrink in size. However, for me, only the top subform shrinks until I click on the top Apply Filter button for a second time. Are you saying that both subforms shrink simultaneously for you?

I chose not to use comboboxes for the name and filters as my real tables are very large and the comboboxes would therefore need a lot of scrolling. I reckoned it was faster just to type into a text box.

David

[June7]

I was ignoring the bottom Apply Filter button.

Both subforms filter when I click the top Apply Filter button only once.

Don't have to scroll combobox list. The AutoExpand property will show matching value as user types in the box.

[haggis999]

Well, that is very strange. Can you think of any reason why we should have such a different experience with exactly the same DB? Are you running under Access 2003, as I am?

I take your point about the comboboxes and will give that a try.

David

[June7]

The version should not make any difference but that is the only remaining explanation. I am using Access 2010 today.