Bug in new Access? Unavoidable automatic Master- Subform linking (Linkmasterfields, Linkchieldfield)

Question

Hallo Developers

with the new Access I got a new feature that destroyes the proper working of my frontend.

(Dont know when this happens because I have Office 365)

When I put a subform on a master form the field Linkmasterfields, Linkchieldfield of the subform will be filled automatical when are matchings availabe. I dont know if this is new - nevertheless this is not the problem. I can simple delete the 2 entrieys.

The Problem is, the deleting has no effect. Via VBA-Code I show the fields in a message box during runtime -> they are filled and the behavior of the program shows the effect too. For delimiting the problem I have disabled all VBA-code. Then it worked at first opening. The cause was only Me.Recourdsource= "qryTest" (here a simplified version only for testing).

This should change nothing because qryTest is the startup up Recordsource in the Propertyexplorer. Especially this should not change the linking of the subform !!!

Its is strange. When I test my previous developed forms all works perfectly. But when I delete the subform and put it again in it  works no more. Therefore a new arrangement (compiling?) destroys my form logic.

That happens with ALL form with such a structure. Can it be that Access has a bug or a new setting for disabling the new funktion?

Answer 1

Access has always done this – for a long time that I can remember.

So, this is not a new feature/change – it been around for a good many years (2000, or 2003). So it not a change in regards to say 2016 or 2013.

You can certainly save the form with the link master/child fields as blank.

However, when you drag the sub form – link master/child will be set.

However, if you blank link settings, then save them.

When you set the recordsource of the sub form in VBA code, if the primary key, and foreign key can be determined by access, it will AUTO set this for you.

How to fix?

#1

The simple solution is thus to simply blank out the master/child settings – and they will thus remain blank.

So, you code can say work like this:

   strSQL = "select * from tblChild where main_id = " & Me!ID

   Me.Child12.Form.RecordSource = strSQL  

   Me.Child12.LinkMasterFields = ""

   Me.Child12.LinkChildFields = ""

Also, no re query is required – access will re query update for you.

So just empty out the link master/child right after you set the data source.

#2

Leave out the “child” FK column OUT of the sql data source, then again access can’t figure out the settings, and will NOT set the master/child for you.

Eg:

   Dim strSQL     As String

  

   strSQL = "select id, InvoiceNumber, InvoiceDate from tblChild where main_id = " & Me!ID

   Me.Child12.Form.RecordSource = strSQL

Note in above, the FK “main_ID” is the FK column, but I do NOT include it in the data source (columns). So you can still place the FK in the “where” clause, but NOT in the column list.

Thus NO automatic setting will occur.

However, the INSTANT you include the FK, like this:

strSQL = "select * from tblChild where main_id = " & me!id

Or:

strSQL = "select ID, Main_ID, InvoiceNumber from tblChild where main_id = " & me!id

then:

   Me.Child12.Form.RecordSource = strSQL

So the “trigger” or WHEN this occurs is if you include the FK value (column) in the field list.

If you include the FK column, then access goes – hey hey hey! – That is the FK column, and thus I will setup relations for you!

So either blank out the two columns right after the RecordSource setting, or if possible, do NOT include the FK column in the data source.

Either approach will fix this, but this auto setting is a VERY long time “feature” and “favor” that access does for you (and a action that some over the years don’t like).

Of course, now that access has worked this way for so long, then changing how this works would break a lot of applications that depended on this favor/feature.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 2

Hello Albert,

thank for you detailed answer.

What you describe is contradict to my experiences – very strange. I swear I never ever in my life wrote such code like

Me.Child12.LinkMasterFields = ""

Me.Child12.LinkChildFields = ""

and it worked yet !

More strange is, in my current application I have many Form/SubForm Relations that work without that code. Reintegrating the SubForm again will destroy the for me “normal” behaviour of Access and produces ever a linking. How I manged it some weeks ago ???

I will check it Monday on a customer Computer with an old Access version.

Your solution #1 works – I tested it. But it’s a hack for me. From the view of software design, it breaks my good modular GUI concept: reusing/modul responsibility. A subform should have its own job, an should not manipulated then by side effect from parent objects.

 

Solution #2 works too.

Disadvantages:

List the lot fields in a query instead of a simple * is much work, especialy in the case of later refactoring.

Creation of a new query with only renaming of the Key should reduce the refactoring, but a query more for only one special task.

Simpler it would be I get the old behaviour of Access.

Answer 3

To my knowledge, this is NOT change in behaviour. Quite sure even 18 year old versions of Access did this.

Note that there is an “additional” way to stop this from occurring.

Simply ensure that the main form is not based on the base table

In other words, if you setup related tables (relationships), and THEN base the MAIN form on the base table name, then Access will do this “automatic” setting for you.

So, if your main form data source is:

tblCustomers

Then access will do the automatic translation for you for any child table (and sub form).

However, if you simply change (or set) the reocrdsource of the main form from a base table  to that of a sql query, then access does not do this translation for you. (this is a code free solution here).

So change the above from

tblCustomers

to

select * from tblCustomers

Now, you not see this auto translation (setting of master/child) occurring for you.

So it possible that those “other” examples did not use a base table name for the main form.

So this would explain why you seeing this now, but not in other cases, and ALSO explain that you NEVER in the past had to change or leave out the FK as I suggested. And why in the past you never had to blank out the two settings (linkmaster/child).

As noted, my suggesting of leaving out the FK seems to work (even if the main form is set to a base table).

However, I even seen the FK trick I mention fail and access will match on PK to PK (main and child table – and that is WRONG!!!).

So a better solution in your case is to simply change what the main form data source is based on (ie: from a “base table” to that of a query that does the same thing).

I am not aware of this being a recent change – even 18 year old versions of Access did this to my knowledge.

So fingers crossed that your “only” difference here is that your main form is based on a base table – not a query.

A simple change to a query for the main form should thus fix this, and thus you don't need to go on some big code hunt. The other examples work likely because the main form is not based on a "base" table as opposed to a query. If indeed your main form is based on a table name, then this would explain everything here.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 4

Hallo Albert

I have tested it on my customer computer with Access 16.0.9129.2295 32-bit. I have deleted the subform, dropped it again in the parentform and deleted the connections in the property expolorer.   ->   all works perfectly without the hacks.

In Version 16.0.10827.20118  32-bit on my computer I get the automatic linking.

What is going on here ?

Exists a different global setting that controls the feature maybe ?

Why I get the feature changings on my computer from one day to another (don’t know exactly when, but I had an office update in that time range) ?

(I had a lot of work for this unclear feature. Can I get money for it from Microsoft – 4 hours a 90€ = 360€ ?  😊)

 

Nevertheless your  “select *” trick works good. You are my hero!

 

Here my structure more precise:

Subform as Tableview, parentform as single record view, both base on the same DBTable.

I make per VBA a Subform -> Parentform connection resq. datsource updateting.

Subform shows all records in a list. Change current row there triggers the showing of that record in the parentform in details.

Answer 5

Keep in mind that we also “often” see the strange behaviour of:

Me.SomeField

The above will work, and then sometimes “fail”. The reason for above is when you “type in” and set the forms recordsource, access THEN auto generates a set of properties that allow you to use

Me.SomeField

And the above works without a control on the form. However, if you use code to change the forms reocrdsource, then all of a sudden, you can find the above stops working. You can even get a compile error. This is because at runtime, access does NOT re-gen the above collection when you change the forms reocrdset via code. If the columns match up, then fine, but if columns used don’t match, then your application can break.

You “often” see this in some forms and you see a “pile” of controls shoved up in some corner – all set to “invisible”. The developer did this because they “out of the blue” started getting errors.

What the developer should have done was use me!SomeField. This does not rely on the forms auto-generated collection, and ALSO does not require a control to be placed on the form.

My “spider” sense suggests that your testing worked, because you changed (or in fact did not change) the forms record source in design mode. (in other words, once you changed this, then all bets for testing are now off the table).

So Access will “re-gen” a set of properties that allows the columns to be referenced via me.SomeField ONLY WHEN you change the forms record source during design mode (by you - not VBA code).

I would get a “broken” copy on your machine, and then run it on the other machine – but do NOT touch anything design wise – I am betting it will still break!!!

Regardless, simply using a query in place of a base table for the main form will fix this issue, and you well note this.

As I noted, this is NOT a recent change in Access – I had this issue with 15+ year old versions.

My spider sense suggests that the error WILL carry over. My position is this is the result of design edits and changes that forces the re-gen of the property collection that allows Access to “reference” the underlying data source is the "real" issue here.

I am not doubting your experience here, but am suggesting that the editing and testing process may well have not been 100% equal.

A “manual” change of the forms reocrdsource setting is a HUGE trigger event in design mode – access re-gens the field list when you do this – but does not if you use VBA code at runtime. The “instant” your testing makes this change, then all bets are off between the two machines. And yes, a slight version change can cause access to re-compile some VBA.

So take a “broken” copy, and just run it on the other machine – it likely will remain broken. (and a working copy can even survive some edits – and will continue to work!!!). Once that auto re-gen occurs, then the errors you noted will start to occur. (so it can work, but edits can cause this to start breaking.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 6

Hi Andreas,

Albert had asked you about the Relationship View. Which is important to track down the error and can help a lot if you can provide the details of how it was link Tables to Tables.

Answer 7

Thank for your interest on the problem. I have not terminated the thread yet, but I have in the moment in stress and have no much time and I am happy that I can continue my work for my customers with the "trick" of albert.

I will come back soon. Some idear what is going on.

The order of the control have often an effect. And I hat set indeed in my code something like "select * where ..." from the very beginning, therefor it worked - but it seem no in the right moment. Event order .....

I will test something and will report when I have time.

T