Hi Jeanette,
Thank you for the suggestions. I was forcing a requery because I was
wondering if Access was (for some reason) not returning the correct number of
records. Here is an abbreviated scenario:
1. I have a Guests table (GuestID, GuestName, AddressID):
1, John Doe, 1
2, Jane Doe, 1
3, Bob Smith, 2
4, Betty Smith, 2
2. I have an Address table (AddressID, Address):
1, 123 Any Street
2, 456 Easy Street
3. I have a "Basket" table (BasketID, BasketName, GuestID):
1, Basket1, 1
2, Basket2, 1
3, Basket3, 2
4, Basket4, 3
I'm writing the database for a charity event. By default, the database
prompts for a primary bidder and will anticipate that this winning bidder
will also pay for the baskets won by his/her spouse. For example, Guest1
(John Doe) will also pay for Jane Doe's basket. The query that I am using to
perform this is similar to:
SELECT tblBaskets.GuestID, tblGuests.AddressID
FROM tblBaskets LEFT JOIN tblGuests ON tblBaskets.GuestID = tblGuests.GuestID
WHERE (((tblBaskets.GuestID) In (SELECT tblGuests2.GuestID FROM tblGuests
LEFT JOIN tblGuests AS tblGuests2 ON tblGuests.AddressID=tblGuests2.AddressID
WHERE tblGuests.GuestID=Forms!frmFindBidderCheckout!BidderID GROUP BY
tblGuests2.GuestID));
Am I over-thinking this? FWIW, my other query is simply:
SELECT tblBaskets.GuestID, tblGuests.AddressID
FROM tblBaskets LEFT JOIN tblGuests ON tblBaskets.GuestID = tblGuests.GuestID
WHERE (((tblBaskets.GuestID)=[Forms]![frmFindBidderCheckout]![BidderID]));
When I open the underlying query and subform by themselves, they return the
correct number of records (3 in my example data above). However, when I open
the form/subform combo, my subform only returns two records (the records
associated with the main bidder).
I looked through the controls for the form in Acc 2007 and didn't see
anything that lept out at me. Also, I commented out the Requery statement and
that didn't have any effect as you surmised. Again, this all works correct in
Acc 2002.
I'll keep checking... thank you for your insights and any others that come
to mind.
dbqph
"Jeanette Cunningham" wrote:
> Not sure that I've ever noticed a subform with incorrect number of records
> in A2007.
> One thing with your code, after you set the subform's record source, you
> shouldn't do a requery.
> The line
> Me.[frmBidderCheckOut].Form.RecordSource = "qryBasketsByBidderID"
> sets the record source for you and the requery is completely unnecessary.
> I don't expect this would make any difference to your results.
>
> In A2007 there are additional properties for forms, not found in A2002.
> Examples - Filter On Load, Order By On Load - you could check if any of
> these make any difference.
>
> Perhaps some other code on your main form or subform is interferring with
> the record source in the subform.
>
> Another way to do what you want is to use 2 different subforms - one for the
> people and the other for an individual.
> You can set the subform control's correct source object (one of the
> subforms) depending on the value of LocateBasketsBy instead of changing the
> record source of the subform.
>
>
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>
<snip>
|
Similar Threads
