MSAccess: Change in subform recordsource causes loss of LinkChildFields binding

Question

I have a problem that Im completely stumped by. Or rather, I have a work-around that is messy and I don't understand why what I want to do doesn't work:

I have a mainform / subform nest. The subform is linked by master/child fields (neither form is editable- so Im using the recordsource field directly rather than a separate control). The subform is a continuous form; so the subform displays the records related to the master in the mainform when the current record changes. The subform is based on a query- there are no parameters or filters being applied- the query presents the recordset exactly as needed. This all works perfectly (in that when the record of the mainform changes, so to do the related subform records).

Problem arises when I want the subform to be based on a different a different query (ie based on different tables)- but with the same unique-key (the 'ID') and fields as the original query. The queries all work fine, and displaying them in the form works fine.

The issue arises when I change the recordsource of the subform (so from QueryA to QueryB). The records the subform are correct (ie the query results are displayed as expected)- but the LinkChildFields is no longer functioning: the subform no longer shows the related records from the query- it displays them all. In other words, the form displays the entire recordset as if I had no LinkChild / LinkMaster set.

So, the code in the AfterUpdate event of the toggle control in the MainForm is ;

Private Sub optActRep_AfterUpdate()

Select Case Me.optActRep
    Case 1
        MainFormSubFormControl.Form.RecordSource = "QueryA"
    Case 2
        MainFormSubFormControl.Form.RecordSource = "QueryB"
End Select

Ive debugged and the property 'linkchldfields' is still set correctly ('ID'). Ive tried setting these to vbNullString before the recordsource update and resetting after the recordsource update (same issue). The odd thing is if I update the linkchildfield property in the mainform (in the Current Event)- then it works and seems to fire. So,

Debug.print Me!Subform.LinkChildFields

correctly returns 'ID'. The only way I can make it fire is by coding;

Me!Subform.LinkChildFields = "ID"

into the Current event of the Mainform (ie so that every time the MainForm record changes, the correct QueryB record is displayed)... even though Im only setting the property to what my debug is telling me it is already set to. Its as if it no longer fires.

Very confused.

Have you tried just re-setting the value of LinkChildFields immediately after updating the Form.RecordSource in your VBA code? — Gord Thompson
Yes. Ive even tried adding an additional different field to each query (say IDa and IDb) and using those as the Linked in part of the Case (to rule out it holding onto something from the original query). It just stops firing. — Surferosa

Surferosa Surferosa · Accepted Answer · 2013-05-20T11:39:20

Access continues to do the daftest things.

Nothing to do with the form setup at all. The underlying query (QueryB) was based on a Crosstab query. Whilst the query worked fine independently of the form (including when directly using the query as the forms record source), it obviously didnt like using the product of one of the fields as the Unique key (even though it was the same format type and Grouped / Row rather than being a cross-tab field).

I changed the crosstab part of the query to a Grouped query (using IIF & Sum to manually create the Crosstab-equivalent field values) and the form now works fine.

My lesson learnt- don't use Crosstab queries! Apologies for the red-herring.

MSAccess: Change in subform recordsource causes loss of LinkChildFields binding

2 Answers