[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
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (10)

Recent Activity:

• New Members 6
• New Links 2
• New Files 4

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___