From: Wayne on
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
From: Wayne on
On Apr 21, 11:24 am, Wayne <handyman1...(a)gmail.com> wrote:
> 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 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