Acc 2007 Subform Recordset vs Acc 2002 Subform Recordset

[dbqph]

I've created a form/subform combo in Access 2002 and have some code in the
OnCurrent section of the form that changes the recordsource of the subform
that will either:

1. Return records for a people at an address (default recordset), or,
2. Return records for an individual at an address

In a nutshell:

If Forms![frmFindBidderCheckout].[LocateBasketsBy] = 2 Then
' Guest wants bidders by address
Me.[frmBidderCheckOut].Form.RecordSource = "qryBasketsByBidderAddress"
Else
' Get just the bidder baskets
Me.[frmBidderCheckOut].Form.RecordSource = "qryBasketsByBidderID"
End If

Me.[frmBidderCheckOut].Form.Requery

intBasketCount = Nz(DCount("[GuestID]",
Me.[frmBidderCheckOut].Form.RecordSource), 0)
curAmountDue = Nz(DSum("[WinningBidAmount]",
Me.[frmBidderCheckOut].Form.RecordSource), 0)

In Access 2002, the correct number of records is returned for either
scenario. In Access 2007, the subform only displays the second case... almost
as if there is a inferred "Link Child/Link Master" setting on the subform
control (there is not).

However, in either version of Access, the values of intBasketCount and
curAmountDue are correct.

Is there a better/different way to properly refresh the content of my
subform in Access 2007 that will correctly display the contents of the
recordsource for the subform? I'm open for any/all suggestions.

TIA,

dbqph

 

[Jeanette Cunningham]

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

 

[dbqph]

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

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>