LinkChildFields and LinkMasterFields being automatically set when changing recordsource

3
votes

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 :)

3
ms-accessvbams-access-2010
Have you tried searching for any dead code lurking in your application which could set LinkChild/Master to "CaseID"? It might be a place to start. – John Bingham
Yeah have checked, no luck. Thanks for reply – Simon
stackoverflow.com/questions/2654667/… Does this help? – Mark C.
Its not really helpful but I could only suggest deleting the subform control (not the subform it contains) and creating a new one with a different name. I myself would probably also set the recordsource of the subform programmatically rather than putting it in the form itself, but I can't really say that this should or shouldnt help. – John Bingham
Thanks John, and thanks Overmind for finding that, had missed it: but I'm 95% sure I've tried all those suggestions. I will continue to trial-and-error suggestions like that though. For one I'm curious if it does the same thing on an entirely new master form... One would assume it's a bug, given there's no "automatically update child/master links despite user setting" but nevermind :) – Simon

3 Answers

2
votes

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.)

0
votes

What I done in the past is simply REMOVE the link child/master settings.

Then in code I SET the child critera. So in the following, the link master/child SHOULD filter by tour_id, but I simply INCLUDED the criteria in the SQL and then HAMMER OUT the settings.

  Dim strSql        As String

  ' load sub-form to this tour....
  strSql = "select * from qryGuidesForTour2 where Tour_id = " & Me!ID


  Me.subGuides.Form.RecordSource = strSql
  Me.subGuides.LinkChildFields = ""
  Me.subGuides.LinkMasterFields = ""

The above was a fix and fixed any performance issues. I suppose it really depends on "how" the main form record is set, but if you are setting up the "main" form record via code, then as above simply stuff in the child forms recordset directly with the critera you need. And while in above I did blank out the master/child settings, if you ARE going to allow adding of reocrds in the sub form, then likely in above you should set the master/child fields as opposed blanking them out.

0
votes

Years passed... as even on Microsoft Access 2019, changing subform .RecordSource always alters original .LinkChildFields and .LinkMasterFields, reassigning good fields will cost us 5s time for each property, we have no way to avoid this.

As a workarround, we can use always the same query, for example, qrySelTmp, as .RecordSource for the subform, at the design time

Mainform.subform.Form.RecordSource = "qrySelTmp"

When we must change .RecordSource content, we change only the definition of the Query, like this

CurrentDb.QueryDefs("qrySelTmp").SQL = "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 & ")"


If(Mainform.subform.Form.RecordSource = "qrySelTmp") then
  Mainform.subform.Form.Requery
else
  Mainform.subform.Form.RecordSource = "qrySelTmp"
End if

As shown in the code, we do only a subform .Requery instead of assigning a new .RecordSource.

As Subform.Form.Requery does not alter subform properties .LinkChildFields and .LinkMasterFields, we gain about 8s for each real change of .RecordSource.

In my case with 1 Customer - Many Orders diagram, I've a gain of time at the proportion of 2s over 20s.