LinkChildFields and LinkMasterFields being automatically set when changing recordsource

Question

In Access Properties for a subform, I have "LinkChildFields = '' " and ""LinkMasterFields = '' " (i.e. blank for both, this is what I want: no link)

When I change the Recordsource of the subform, and requery the subform, (in VBA) they are both automatically being set to a field, in my case "CaseID" (resulting in no records being displayed)

This is my recordsource:

stSQL = "SELECT qryCasesAndCards1.CaseID, qryCasesAndCards1.CaseStatusID, qryCasesAndCards1.Status, qryCasesAndCards1.CompanyName, qryCasesAndCards1.NumberOfCards, qryCasesAndCards1.FullName, qryCasesAndCards1.CaseCreatedDate, qryCasesAndCards1.CaseClosedDate, qryCasesAndCards1.CreatedBy" & _
            " FROM qryCasesAndCards1 where not caseID = " & Me.CaseID & " and cardnumber in (select qryCasesAndCards1.cardnumber from qrycasesandcards1 where qryCasesAndCards1.caseID = " & Me.CaseID & ")"

I've tried substituting a simpler query "select * from qryCases1" and the problem still occurs.

In VBA, right after the requery, I am debug.print'ing ".linkchildfields" and ".linkmasterfields" and this is how I can see they are both being automatically set to "CaseID"

I have tried changing both values in Access form properties to nonsense value, resaving, changing again, resaving etc, and still no joy.

I can workaround problem by setting both those values right after the recordsource runs, but there is an unacceptable delay when doing this (about 5 seconds for each value)

One thing I'm just wondering now, is wether my form filter is being propagated to the subform? (am opening it via "docmd.open ... caseid = x" )

CaseID might have at ONE stage some long time ago been entered in to those link fields... but it's definitely not now. It's like they're locked in an Access vault somewhere and it's thinking "golly gee i'm pretty sure he wants CaseID in there gee whiz I'll get right on that!"

I've found MSAccess: Change in subform recordsource causes loss of LinkChildFields binding and I've found Linking SubReports Without LinkChild/LinkMaster but I can't get any help from them.

Thanks for any help offered: I'm tearing my hair out :)

Answer 1

To keep Access from automatically updating the LinkChildFields and LinkMasterFields on a subform...

1. Ensure that the RecordSource queries do not have matching key fieldnames.
2. Ensure that the Link Child Fields and Link Master Fields are blank on the SubForm object's Property Sheet.
3. DO NOT set the LinkChildFields and LinkMasterFields properties in code (i.e. remove any previous code like MySubForm.LinkChildFields = ""), otherwise the negative side effects may not be resolved.

If the form and/or subform are bound directly to tables, simply update one of them to query the table and add an alias for one of the key fields. For existing queries, it is possible to add an alias for one of the key field names without negative consequences in probably most cases. In my case, I only had to add "AS Acct" to a single field in the SELECT part of the subform's RecordSource SQL--no other change was necessary in the FROM, WHERE or ORDER BY clauses even though the original [Account] fieldname is referenced in each of those clauses.

SELECT Actions.[Account] As Acct, ...

Of course, this required an update to the ControlSource property for the relevant control on the form.

These changes eliminated the extra few-second lag when changing both the parent record and when switching records on the subform! Likewise, it eliminated redundant events during record navigation!

I found no other setting that keeps Access from automatically re-binding the subform via the LinkChildFields and LinkMasterFields properties. As already pointed out in the comments, it also doesn't help by resetting the link fields (e.g. LinkChildFields = "") after setting RecordSource, primarily because Access takes time to reset the fields, much of that time spent in events fired both upon the automatic assignment of the link fields and again when they are reset to blank. (e.g., Form_Current is called at least twice for each new record navigation.)