Annoying automatic filling of link master/child fields at runtime

[Josef P.]

Because I overlooked the automatism once again and wondered about the displayed data:

Does anyone know a way to prevent the LinkMasterFields and LinkChildFields subform properties from auto-populating when changing the subform data source at runtime?
Access sets this property automatically when it finds data fields with the same name in the data source of the main form (PK) and the subform.
When inserting in design view, it doesn't bother me. At runtime, I find it very annoying. Why does Access change the application developer settings?

Note: renaming the respective affected data fields using an alias helps, but I regularly forget to do that

Example see attached file.

 

[CJ_London]

Access sets this property automatically when it finds data fields with the same name in the data source of the main form (PK) and the subform.

I had thought this only happened when relationships are defined but appears you are right.

My naming convention always suffixes the primary key with PK and the foreign key with FK and with the exception of the foreign key name all field names are unique to the application, not just the table so I've never come across this issue although I guess I might of done when I was a true newbie.

Regret I don't know of a way to prevent auto populating the link properties

 

[Josef P.]

My naming convention always suffixes the primary key with PK and the foreign key with FK and with the exception of the foreign key name all field names are unique to the application, not just the table so I've never come across this issue although I guess I might of done when I was a true newbie.

That is also the case with me, but unfortunately it is not always possible.

My last example with parent records:
Data fields:
- idWorkPackage (PK)
- fiParentWP (FK to idWorkPackage, same table)
- ....

Main form with one Record + subform with childs.
Since I couldn't just link the child entries by MainForm.idWorkPackage to SubForm.fiParentWP, but needed a slightly more complex filter expression, I made the subform unlinked to the main form and set the filters using the subform's data source.
Result: no data was displayed because Access supplemented the link via MainForm.idWorkPackage to SubForm.idWorkPackage.
Note: DBMS is SQL-Server.

Regret I don't know of a way to prevent auto populating the link properties

I consider this a bug and not a feature if it happens at runtime and not in design mode.
Or have you ever used the feature at runtime?

 

[sonic8]

Why do you not want the master/child links set?

It is definitely a feature and not a bug.

Access is just taking a guess what would be the correct master/child relations between a parent form and the sub form. It simply cannot know what the intended relations are. @Josef P. already described a concrete scenario in the previous post where Access set the links incorrectly.

 

[Josef P.]

Access is just taking a guess what would be the correct master/child relations between a parent form and the sub form.

Exactly!
This guess is perfectly fine as a suggestion in design mode, but just not at runtime when the form is used in form view.

Renaming the using an alias for the data field is currently the easiest way I know to work around the problem.
Unfortunately, this means that you lose a uniform naming of the identical data fields.

 

[ebs17]

Overzealous wizards can be at least as harmful as lazy wizards.

 

[MarkK]

If you programmatically load a subform with no RecordSource, the main form will not populate the LinkMasterFields and LinkChildFields properties of the subform control. So set the RecordSource of the subform after it is loaded in the control. One possibility on the subform...

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval = 50
End Sub

Private Sub Form_Timer()
    Me.TimerInterval = 0
    Me.RecordSource = "qMyQuery"
End Sub

 

[ebs17]

Please post a link

Look at sample database in #1.

There are two tables that have nothing to do with each other, so they have no relationship.
But since they both have a field id, they are automatically linked in the form, which is not supposed to be. Due to the unwanted linking, the records in the subform are additionally filtered, which is an error here.

 

[MarkK]

This is not a bug, it is a feature.

I can see why people might like this behaviour, and why they might not. I don't see this issue as having a right or wrong answer.

 

[ebs17]

sure that Access will get the master/child links right

It is not easy to understand a question.

It was not about connecting right or wrong. It was about not connecting at all, that is, switching off this overzealous automatism and finding a way for it.

 

[arnelgp]

The feature works correctly as long as the relationships are correctly defined

the Intent of the programmer is that the two tables are Not Related, but Access keeps on Relating them and that for me is a bug since I did not define where and how they are related.

 

[MarkK]

If you don't bother with relationships because you are too good for them,

Snide:
adjective
1. derogatory or mocking in an indirect way.
2. (of a person) devious and underhanded.
noun
informal
1. an unpleasant or underhanded person or remark.

 

[Minty]

It's definitely a bug in my opinion.

If I don't want a relationship between a subform I won't specify one. I certainly don't want Access to then guess I DID want a relationship after the event out side of my control.

Can you imagine if access relinked tables in a query automatically after you had specifically unlinked them or changed it from a left join to a full join? You'd be hopping mad, and have a broken app.