Filter Form Based on Subform Combo Field Problem

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

I have three forms as follows:
tblContracts 1 to M tblIssues 1 to M tblIssueComments

I have a unbound combo box with three options: Open, Closed, All.

Based on what I select on this combo I change the recordsource as
follows:

Start Code ***************************************
Private Sub txtSelectIssues_AfterUpdate()

Dim bWasFilterOn As Boolean, MyFilterString As String

bWasFilterOn = Me.FilterOn

'MsgBox Me.Filter

If Me.txtSelectIssues.Value <> "All" Then
If Me.txtSelectIssues.Value = "Open" Then
' change recordsource with OPEN Issues
MySQL = "SELECT tblContracts.ID,
tblContracts.Contract_Number,tblContracts.Contract_Ext,
tblContracts.Contract_Name, tblContracts.Contract_Type,
tblContracts.Contract_Manager, tblIssues.Issue_Title,
tblIssues.Issue_Status, tblIssues.Open_Date, tblIssues.Close_Date,
tblIssues.Priority, tblIssueComments.Comments_Date,
tblIssueComments.Description"
MySQL = MySQL & " FROM (tblContracts INNER JOIN tblIssues ON
tblContracts.ID=tblIssues.IssueID) INNER JOIN tblIssueComments ON
tblIssues.ID=tblIssueComments.IssueCommentsID"
MySQL = MySQL & " WHERE
(((tblIssues.Issue_Status)=""Open""));"
Me.RecordSource = MySQL
Else
' change recordsource with CLOSED Issues
MySQL = "SELECT tblContracts.ID,
tblContracts.Contract_Number,tblContracts.Contract_Ext,
tblContracts.Contract_Name, tblContracts.Contract_Type,
tblContracts.Contract_Manager, tblIssues.Issue_Title,
tblIssues.Issue_Status, tblIssues.Open_Date, tblIssues.Close_Date,
tblIssues.Priority, tblIssueComments.Comments_Date,
tblIssueComments.Description"
MySQL = MySQL & " FROM (tblContracts INNER JOIN tblIssues ON
tblContracts.ID=tblIssues.IssueID) INNER JOIN tblIssueComments ON
tblIssues.ID=tblIssueComments.IssueCommentsID"
MySQL = MySQL & " WHERE
(((tblIssues.Issue_Status)=""Closed""));"
Me.RecordSource = MySQL
End If
If InStr(1, Me.Filter, "[tblContracts].[Contract_Number]") > 0
Then
Me.Filter = Replace(Me.Filter, "[tblContracts].
[Contract_Number]", "[frmContractStatus].[Contract_Number]")
End If
Else
If Me.RecordSource <> "tblContracts" Then
Me.RecordSource = "tblContracts"
End If
If InStr(1, Me.Filter, "[frmContractStatus].
[Contract_Number]") > 0 Then
Me.Filter = Replace(Me.Filter, "[frmContractStatus].
[Contract_Number]", "[tblContracts].[Contract_Number]")
End If
End If

'MsgBox Me.Filter

' turn filter back on if set
If bWasFilterOn And Not Me.FilterOn Then
Me.FilterOn = True
End If

End Sub
End Code ***************************************

When switching to the query recordsource I get the correct count of
"Open" issues for example
but when I step through the Main form records it doesn't display the
"Open" issues in the subform.
I get the same display for all records on the Main, Subform1 and
Subform2. When I run the query
separately it shows all the data I trying to show correctly.

What am I doing wrong? I tried using the same query for Subform1 but
never got that to work.

I'm trying to get Allen Browne's solution (http://allenbrowne.com/
ser-28.html) to work for me but
I messed up something.

Any help will be appreciated.

Wayne
 
I have three forms as follows:
tblContracts  1 to M  tblIssues  1 to M  tblIssueComments

I have a unbound combo box with three options: Open, Closed, All.

Based on what I select on this combo I change the recordsource as
follows:

Start Code ***************************************
Private Sub txtSelectIssues_AfterUpdate()

Dim bWasFilterOn As Boolean, MyFilterString As String

bWasFilterOn = Me.FilterOn

'MsgBox Me.Filter

If Me.txtSelectIssues.Value <> "All" Then
    If Me.txtSelectIssues.Value = "Open" Then
        ' change recordsource with OPEN Issues
        MySQL = "SELECT tblContracts.ID,
tblContracts.Contract_Number,tblContracts.Contract_Ext,
tblContracts.Contract_Name, tblContracts.Contract_Type,
tblContracts.Contract_Manager, tblIssues.Issue_Title,
tblIssues.Issue_Status, tblIssues.Open_Date, tblIssues.Close_Date,
tblIssues.Priority, tblIssueComments.Comments_Date,
tblIssueComments.Description"
        MySQL = MySQL & " FROM (tblContracts INNER JOIN tblIssues ON
tblContracts.ID=tblIssues.IssueID) INNER JOIN tblIssueComments ON
tblIssues.ID=tblIssueComments.IssueCommentsID"
        MySQL = MySQL & " WHERE
(((tblIssues.Issue_Status)=""Open""));"
        Me.RecordSource = MySQL
    Else
        ' change recordsource with CLOSED Issues
        MySQL = "SELECT tblContracts.ID,
tblContracts.Contract_Number,tblContracts.Contract_Ext,
tblContracts.Contract_Name, tblContracts.Contract_Type,
tblContracts.Contract_Manager, tblIssues.Issue_Title,
tblIssues.Issue_Status, tblIssues.Open_Date, tblIssues.Close_Date,
tblIssues.Priority, tblIssueComments.Comments_Date,
tblIssueComments.Description"
        MySQL = MySQL & " FROM (tblContracts INNER JOIN tblIssues ON
tblContracts.ID=tblIssues.IssueID) INNER JOIN tblIssueComments ON
tblIssues.ID=tblIssueComments.IssueCommentsID"
        MySQL = MySQL & " WHERE
(((tblIssues.Issue_Status)=""Closed""));"
        Me.RecordSource = MySQL
    End If
    If InStr(1, Me.Filter, "[tblContracts].[Contract_Number]") > 0
Then
        Me.Filter = Replace(Me.Filter, "[tblContracts].
[Contract_Number]", "[frmContractStatus].[Contract_Number]")
    End If
Else
        If Me.RecordSource <> "tblContracts" Then
            Me.RecordSource = "tblContracts"
        End If
        If InStr(1, Me.Filter, "[frmContractStatus].
[Contract_Number]") > 0 Then
            Me.Filter = Replace(Me.Filter, "[frmContractStatus].
[Contract_Number]", "[tblContracts].[Contract_Number]")
        End If
End If

'MsgBox Me.Filter

' turn filter back on if set
If bWasFilterOn And Not Me.FilterOn Then
    Me.FilterOn = True
End If

End Sub
End Code ***************************************

When switching to the query recordsource I get the correct count of
"Open" issues for example
but when I step through the Main form records it doesn't display the
"Open" issues in the subform.
I get the same display for all records on the Main, Subform1 and
Subform2. When I run the query
separately it shows all the  data I trying to show correctly.

What am I doing wrong? I tried using the same query for Subform1 but
never got that to work.

I'm trying to get Allen Browne's solution (http://allenbrowne.com/
ser-28.html) to work for me but
I messed up something.

Any help will be appreciated.

Wayne
Click to expand...

I was including subform information in the query which caused the
problem.

I have a unbound combo box on the Main form that has Open, Closed and
All as the options. When this is changed I change the recordsource
which includes the Status field on the subform (not displayed, not
checked) so I see only Open issues for example which ends up being the
equivalent of a filter.

Wayne
 
Back
Top