Connie-
You're not providing the "name" of a filter, you're providing a WhereCondition:
DoCmd.OpenForm "Listings", _
WhereCondition:=strF
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Lahaina, HI)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, January 25, 2012 12:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Opening a form with a specific recordsource
John,
I am struggling with this. You are right-- I do not need ContactID and
WithdrawnDate in the Select statement.
This code is behind a button on another form which is supposed to open the
Listing form filtered to the appropriate record/s. I was using the old code as
the Recordsource but have decided it's better to filter the records.
The Listing form is opening unfiltered. I've used a breakpoint along with the
Immediate window to check that a filter is being made and it looks okay to me.
Here's the string for strF from the Immediate Window: ListID IN(SELECT
Withdrawn.ListID FROM ListingContacts INNER JOIN Withdrawn ON
ListingContacts.ListID = Withdrawn.ListID WHERE ListingContacts.ContactID = 24
AND DatePart("yyyy", [Withdrawn].[WithdrawnDate]) = 2011)
I also used the filter and created a query which worked.
I'll put my code below along with the SQL for the query that is working based on
the above.
I am soooooo grateful for your help!
Connie
ElseIf Me.Status = "Past Seller" Then
Dim strF As String
strF = "ListID IN(SELECT Withdrawn.ListID FROM " & _
"ListingContacts INNER JOIN Withdrawn " & _
"ON ListingContacts.ListID = Withdrawn.ListID " & _
"WHERE ListingContacts.ContactID = " & Me.ContactID & _
" AND DatePart(""yyyy"", [WithdrawnDate]) = " & Me.Yr & ")"
DoCmd.OpenForm "Listings", _
filtername:=strF
If Me.FilterOn = False Then Me.FilterOn = True
Forms!Listings.SBorder.Visible = True
Query SQL:
SELECT Listings.ListID, Listings.PropertyName
FROM Listings
WHERE ListID IN
(SELECT Withdrawn.ListID
FROM ListingContacts INNER JOIN Withdrawn ON ListingContacts.ListID =
Withdrawn.ListID
WHERE ListingContacts.ContactID = 24 AND DatePart("yyyy",
[Withdrawn].[WithdrawnDate]) = 2011);
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Connie-
>
> No. You could get all the applicable listing data, using an IN clause, but
you
> wouldn't be able to get ContactID and WithdrawnDate. I'm having a hard time
> picturing how you could have the same form bound to a simple query on Listings
> and also this query. Are ContactID and WithdrawnDate used on the form or in
> code? If you aren't actually using those fields in the target form, you
*could*
> build this filter:
>
> "Listings.ListingID IN (SELECT Withdrawn.ListID FROM " & _
> "ListingContacts INNER JOIN Withdrawn " & _
> "ON ListingContacts.ListID = Withdrawn.ListID " & _
> "WHERE ListingContacts.ContactID = " & Me.ContactID & _
> " AND DatePart(""yyyy"", [WithdrawnDate]) = " & Me.Yr & ")"
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Lahaina, HI)
>
>
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Tuesday, January 24, 2012 6:58 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Opening a form with a specific recordsource
>
> Thanks a bunch John! That is a far easier solution that I was doing. I'm
> redoing several things. Another question--is there a way to use the original
> recordsource (Listings*) and create a filter to get the appropriate records
> instead of the following?
>
> "SELECT Listings.*, ListingContacts.ContactID, Withdrawn.WithdrawnDate " _
> & "FROM Listings INNER JOIN (ListingContacts INNER JOIN Withdrawn ON
> ListingContacts.ListID = Withdrawn.ListID) ON Listings.ListID =
Withdrawn.ListID
> " _
> & "WHERE ListingContacts.ContactID = " & Me.ContactID & " AND
> DatePart(""yyyy"", [WithdrawnDate]) = " & Me.Yr & ";"
>
> Thanks!
> Connie
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Connie-
> >
> > You don't need the INNER JOIN. The filter you need is:
> >
> > "ListID IN (SELECT ListID FROM Withdrawn)"
> >
> > You also don't need DISTINCTROW if you do that.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Lahaina, HI)
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > Sent: Monday, January 23, 2012 11:46 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Opening a form with a specific recordsource
> >
> > John,
> >
> > "All Listings" recordsource is the Listings table. The Sold and Withdrawn
> have
> > an inner join with the Listings table and their respective table (Sold,
> > Withdrawn). An Example of the recordsource for Withdrawn would be
> > strListingsrecordsource = "SELECT DISTINCTROW Listings.* FROM Listings " & _
> > "INNER JOIN Withdrawn ON " & _
> > "Listings.ListID = Withdrawn.ListID;"
> >
> > So I'm guessing that it's necessary to open the Listings form and then set
the
> > recordsource, captions and row sources. I'm thinking of having the Listings
> > form not have a recordsource or row sources until it's assigned when the
> button
> > is clicked. Will that work? I have a filter combobox in the header which
> > applies the filter. And have been having the reset button redo the
> > recordsource. But I'm realizing through your comment that I could just have
> it
> > remove the filter.
> >
> > This form takes a long time to load and I'm trying to figure out how to
speed
> it
> > up. Has 6 subforms and 13 comboboxes.
> >
> > Thanks!
> > Connie
> >
> >
> >
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Connie-
> > >
> > > What does your code look like for Sold or Current? Are you simply
applying
> a
> > > filter such as Status = "Sold" ?? If so, you can use the WhereCondition
> > > parameter of OpenForm to apply the filter as the form opens. You will
still
> > > need to set a custom caption and might perhaps need to set the combo box
Row
> > > Sources.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Lahaina, HI)
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > Sent: Monday, January 23, 2012 8:07 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Opening a form with a specific recordsource
> > >
> > > Hi all! I have a form Listings that is opened via buttons for different
> > > recordsources (Sold, Current, etc). So each button has code applying the
> > > appropriate recordsource. I'm guessing there's a much more efficient way
to
> > do
> > > it. I am currently opening the form and then setting the recordsource. Is
> > there
> > > a way to set the recordsource as part of the opening the form process?
(I'm
> > > also setting titles and RowSources after the form is opened and I'm
guessing
> I
> > > can apply the same method that you'll give to to those.)
> > >
> > > Thanks,
> > > Connie
> > >
> > > Dim strSql2 As String
> > >
> > > 'Close Listings form if open
> > > If IsLoaded("Listings") Then
> > > DoCmd.Close acForm, "Listings", acSaveNo
> > > End If
> > > 'create query for all recordsource with all listings
> > > strListingsrecordsource = "SELECT Listings.* FROM Listings "
> > >
> > >
> > > DoCmd.OpenForm "Listings"
> > >
> > > 'Set Form title/caption to "All Listings"
> > > Forms!Listings.FormTitle.Caption = "All Listings"
> > > 'Set Listings RecordSource
> > > Forms!Listings.RecordSource = strListingsrecordsource
> > >
> > > 'Create rowsource for cboSearch box
> > > strSql2 = "SELECT DISTINCT qListingContacts.ContactID,
> > > qListingContacts.ConcatName, qListingContacts.SpouseFN " & _
> > > "FROM qListingContacts INNER JOIN Listings ON qListingContacts.ListID
=
> > > Listings.ListID " & _
> > > "ORDER BY qListingContacts.ConcatName;"
> > >
> > > 'Set RowSource for cboSearch box
> > > Forms!Listings.cboSearch.RowSource = strSql2
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar