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 am 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 did not see
anything that lept out at me. Also, I commented out the Requery statement and
that did not have any effect as you surmised. Again, this all works correct in
Acc 2002.
I will keep checking... thank you for your insights and any others that come
to mind.
dbqph