Filter Form Based on Subform Combo Field Problem
in [Form]
|
Prev: creating Time Book Need help
Next: Auto Fill Fields
From: Wayne on 21 Apr 2010 14:24 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 22 Apr 2010 15:31 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
|
Pages: 1 Prev: creating Time Book Need help Next: Auto Fill Fields |