Re: How to change the RowSource in a SubForm in VBA code

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.

<CDMAPoster@xxxxxxxxxxxxxxxx> wrote in message
news:1187804456.303254.149010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Aug 19, 10:46 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
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
CDMAPoster@xxxxxxxxxxxxxxxx

.

Relevant Pages

  • Re: Is there a way to open a report in preview or report mode and have it be invisible?
    ... ascertain the recordsource of the query so I can give my users a list ... of the fields in the report from which they can choose a sort order. ... I'm sure opening in design view is a bad way to do this... ... Tony Toews, Microsoft Access MVP ...
    (comp.databases.ms-access)
  • Re: inconsistent report problems
    ... could also set the RecordSource to a full sql string in the Report_Open ... This use the fact that contrary to forms, the record source for a report is ... Sylvain Lafontaine, ing. ... @Counties VARCHAR to something smaller. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Is it possible to show both "Yes" and "No" answers from a "yes/no"
    ... the report will not show the subreports. ... > subreport; delete that subform and the report will show that subreport. ... If you open this query in design view, ... >>> using QryJobs as its recordsource. ...
    (microsoft.public.access.gettingstarted)
  • Re: Weird Report Problem - Access 2003
    ... Sorting Records in a Report at runtime ... The SQL statement combines 2 tables. ... >> a) You saved the report with some other RecordSource. ... The user can choose up to 3 sorts ...
    (comp.databases.ms-access)
  • RE: Filter without grouping
    ... to change the SQL property of a saved query. ... Part of the problem is that the criteria are optional: ... and passed it to the report as OpenArgs. ... creates the RecordSource, split it up into pieces to build the appropriate ...
    (microsoft.public.access.reports)