Annoying automatic filling of link master/child fields at runtime (1 Viewer)

[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?

 

[Pat Hartman]

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

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?

It is definitely a feature and not a bug.

 

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

 

[Pat Hartman]

There is only ONE master/child relationship between two tables.

Are you saying that you want NO relationship defined? That would be unusual but you could remove the relationship. Then YOU would be responsible for populating the FK in your code, preferably using the BeforeInsert event. If you think the relationship should be something non-standard, then you would always be defining it yourself regardless.

So, the only time Access' wrong guess would be an annoyance is when you want NO relationship defined.

I never saw @Josef P. 's example showing the wrong guess. Please post a link. Thanks.

 

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

 

[Pat Hartman]

Well Of Course the assumption is incorrect. There is no relationship defined and so Access is using name matching to guess. If you simply define the actual relationship, I'm pretty sure that Access will get the master/child links right. Or, you could stop confusing Access and use unique names for unique objects This is not a bug, it is a feature. The feature assumes you have designed the schema correctly. You can't expect it to figure out your own mistakes.

When I load subforms on the fly, I always set the master/child links manually. Probably because at some point in ancient history, Access didn't used to set them and since it is two lines of code, it never occured to me to investigate whether or not I still needed to do this. Looks like I don't.

 

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

 

[Pat Hartman]

Access should work consistently regardless of how a "feature" is invoked. Either it is OK to automatically set the master/child links in the GUI or it is not. If it makes sense for the GUI, it makes sense when adding the form via VBA. The feature works correctly as long as the relationships are correctly defined. If you don't bother with relationships because you are too good for them, than you get what you get.

 

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

 

[Pat Hartman]

@Minty As I said, if you don't want the subform to be linked to the main form, that is your business. I'm sure you are quite capable of managing the populating of FKs without the help of Access. You can remove the master/child links as needed. Access does a lot of stuff behind the scenes that you take advantage of which is the entire point of using a RAD tool. This particular "help" you don't like so you call it a bug. OK. If you want to do everything yourself, use some variant of C. You will have absolute control but no help. If you use a RAD tool, you get "help" whether you want it or not

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.

That is quite a different situation and yes it would be a bug.

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.

Again, Access will NOT attempt to match by names if you don't reuse names. If you call every thing "Larry", then Access will assume you made a valid choice and the two identical names will be assumed to be the PK and FK. If you suffix your PK and FK with _PK and _FK, then Access will only help you if you have actually defined a relationship. There won't be any duplicate names to use

I would be fine with Access not being quite so helpful and not attempting to match by name because I don't ever need that particular kind of help given that I always define relationships between parent and child tables. However, what I am arguing about is your (all 4 of you) desire to have it both ways. I think Access should be consistent. You are arguing that it should be inconsistent. When Access is inconsistent (as it is occasionally), THAT is a bug.

If it is a bug to match by name at runtime, it is a bug to match by name at design time.