Yes, that's a good description of what is it supposed to do.
Most times it gets it right if the conditions you met are right. But there
are complicating factors where Access may not be able to figure it out
correctly. For example, where the related table has more than one foreign
key of the right data type to choose from (regardless of whether the names
match or not), and you are using queries as the source.
It makes sense that the same problem situtions could arise when setting the
subform's RecordSource on the fly.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<CD********@FortuneJames.comwrote in message
news:11**********************@i13g2000prf.googlegr oups.com...
On Aug 19, 10:46 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Simon, just a follow up note.
When you do something like:
Me.[SubFormControlName].Form.RecordSource = "SELECT ...
Access will reset the LinkMasterFields and LinkChildFields property of the
subform control. If it gets it right, things will be fine. If it gets it
wrong, the subform may show all record (not just the related ones), no
records at all, or the wrong records.
That's an interesting observation.
>From the A97 help for LinkChildFields, LinkMasterFields Properties:
When you create a subform or subreport by dragging a form or report
from the Database window onto another form or report or by using the
Form Wizard, Microsoft Access automatically sets the LinkChildFields
and LinkMasterFields properties under the following conditions:
· Both the main form or report and the child object are based on
tables, and a relationship between those tables has been defined with
the Relationships command. Microsoft Access uses the fields that
relate the two tables as the linking fields.
· The main form or report is based on a table with a primary key, and
the subform or subreport is based on a table or query that contains a
field with the same name and the same or a compatible data type as the
primary key. Microsoft Access uses the primary key from the main
object's underlying table and the identically named field from the
child object's underlying table or query as the linking fields.
Although setting a subform's RecordSource dynamically isn't mentioned
specifically, I imagine that the same principles apply. I've never
had Access get this wrong, but thanks to your warning I'll be on the
lookout for it.
James A. Fortune
CD********@FortuneJames.com