form/subform parent/child link doesn't work as expected.

[Seamus59]

I have Parent/Child tables with one set for each year, in a linked (archive) database. They are related on ID, ParentID. I want the user to select a year and be able to browse the data in datasheet view, with the plus sign to see the child table. Since the tables are in a linked database, the only way I have been able to achieve that is with a form/subform with Datasheet as the default view.

Perhaps there is a simpler method, but here is my difficulty.

On the click event that selects the year I open the parent form and set it's recordsource to the correct parent table. Then I build a query that selects the correct child table. The child table's record source is already set to the child query. The parent opens with the plus signs which display the fields of the child table but the child (+) shows only field headers, no child records nor record navigation items.



If I click open the child form, it's record set is the query whick is itself correctly defined and brings up the correct table.

There is a relationship defined, relating the parent to the child in a one-to-many relationship. I have tried a requery on the child form load event and that doesn't help.

Does anyone have any suggestions?

[June7]

Are the subform container control Master/Child links properties set?

Does the subform query include WHERE clause? Maybe don't use a query as the subform RecordSource, just reference tablename.

[Seamus59]

Thanks for the suggestions. No, I'm not using a WHERE clause. I used a query because I couldn't figure out how to set the subform's RecordSource without it being open. I tried the code below to put the table name as RecordSource of the subform. [Archive-Detector Specifications] is the parent form and [Archive-Detector Testing History] is the subform.

DoCmd.OpenForm "Archive-Detector Specifications", acFormDS, , , acFormReadOnly, acWindowNormal, lstYear.Column(0) 'opens correctly with (+) signs
Forms![Archive-Detector Specifications].RecordSource = lstYear.Column(0) & "-Detector Specifications" 'Correctly set recordsource of parent
Forms![Archive-Detector Testing History].RecordSource = lstYear.Column(0) & "-Detector Testing History" 'returns error 2450, "Can't find the "Archive-Detector Testing History" ...

Apparently the subform isn't opened and it's RecordSource can't be redefined until the user clicks the (+) sign. That's why I set it's RecordSource to a query and just redefine the query depending on which year has been selected.

I may be "over thinking" the issue and may need to take a different tack.

I do have a one-to-many relationship defined for each parent and child, is that what you meant by "subform container control Master/Child links properties set?"

[June7]

I've never set up a form to use the + expansion. Do you want to provide db for analysis? Follow instructions at bottom of my post. Import the linked tables.

[Seamus59]

I got this from the Microsoft support website that states:

The plus sign (+) may not appear when you try to click the plus sign (+) to drill down into the related records of a child table in Office Access 2003

In a Microsoft Office Access 2003 database, when you try to click the plus sign (+) to drill down into the related records of a child table, the plus sign (+) may not appear. You experience this problem when the following conditions are true:

• You use Office Access 2003 to link to tables that are in another database.
• The tables are in a parent-child relationship.

To work around this problem, follow these steps:

1. Establish a one-to-many relationship between the parent and child tables. To do this, click Relationships on the Tools menu in Office Access 2003.
2. Create one form for the parent table.
3. Create another form for the child table.
4. Set the Default View property of both forms to Datasheet.
5. Add the child form to the parent form.
   

That is how I got the (+) sign to appear, although I am using Access 2007, it has the same problem.
I've created the one-to-many.

[Seamus59]

GDPM-Test.zip

The plus link works when you open the back-end, but not when the table is linked. Once you see what I'm trying to do, there may be an awesomely easier way to do it.

Thanks,
Sean

[Seamus59]

Ok, I solved it...well "June7" solved it. I'm dumb, I double checked the Link Master Fields and Link Child Fields and the were incorrect! Gosh, talk about making the easy things look difficult!

Sorry for the uproar, but sometimes it just takes another pair of eyes. I couldn't see the forest for the trees!

Thanks.