Correction:

I've never seen a subform filter records unless the Master:Child links WERE established.

Out of curiosity: Are there any relationships, global or other, between any of the fields in the master form and fields in the subform that Access might be inferring from? Even an ad hoc relationship established through a query in a combo box?

Just wondering if maybe there is a cascading inheritance at work such as a global relationship that might get overridden by an ad hoc relationship which might also get overridden by Master/Child relationship property. Working backwards, the last or highest level of established relationship might dominate until the master form becomes unbound with the subform filtering on that higher relationship?

Or, depending on the version of Access you're working with, if it's an Insider build, if there is a cascading combo box that might be establishing some sort of relationship/filter.

Grasping at straws, just some thoughts that ran through my head at first look.

That's an odd one. Access should not automatically apply a form/subform filter just because the underlying tables have a relationship. Table relationships enforce referential integrity and help with joins, but they don't normally act like Link Master Fields and Link Child Fields on a subform control.

I'd check a few things before blaming the bound parent form itself:

1. Make sure you're looking at the properties of the subform control on the parent form, not just the form object loaded inside it. The Link Master Fields and Link Child Fields properties belong to the subform control.

2. Check the subform's RecordSource. If it is a query, make sure it doesn't reference a control on the parent form, such as Forms!ParentF!SomeField, or a parameter that Access is resolving from the parent form.

3. Check the subform's Filter and FilterOn properties, plus any Form_Load, Form_Current, or other event procedures that might be applying a filter.

4. See whether the parent form is opening with a WhereCondition, or whether its RecordSource query has criteria that indirectly affect what you are seeing.

A quick test would be to open the subform by itself as a normal form. If it shows all records independently, but filters only when placed on the bound parent form, then something about the subform control, its RecordSource, or VBA is involved.

Relationships by themselves should not create a hidden master/child link. If you can reproduce it in a stripped-down copy of the database with just the two forms and tables, I'd definitely be interested in seeing the exact setup.

Donald

No.  I do not use any global relationships.

I had created those relationships on the parent/subform previously, then realized it wouldn't work the way I wanted, so I removed them, but it kept acting like they were still linked.

This was an Access database that I built starting in 2020.  

Once I removed the bound Table from the Parent Form... it started working the way I wanted, even with no change in values on the parent Form.  It's as if it still wanted the link there even after I removed it.  Weird.

-Raymond

Richard

You're bringing up all the things I looked at (which is good)

When I would open the subForm by itself, it would load all the records.  

When I had a Table bound to the parent Form, I did have a Filter on the parent Form, but I wouldn't expect that to apply to the SubForm when there was no Parent:Child Link.  I could be wrong about that.

I've just always had SubForms show all values if the Parent:Child Links were not established.  This one really confused me.

-Raymond

That still sounds like something Access was holding onto at the subform control level, especially since the subform opens independently and shows all records. A Filter on the parent form should not, by itself, filter an unrelated subform when Link Master Fields and Link Child Fields are blank.

One thing I might try is deleting the subform control from the parent form completely and then adding it back in as a new subform control. Not just clearing the links - actually delete and recreate the control. Access occasionally gets weird design-time baggage like that, particularly in databases that have been through a lot of revisions over several years.

You could also try a Compact and Repair before rebuilding it. But if removing the parent form's RecordSource immediately fixes it, and recreating the subform control fixes it too, I'd chalk it up to an Access quirk rather than intended behavior.

I wonder if saving as text would give any insight.

Matt

I'm not sure what you mean?  Save what as text?

-Raymond

Saving the form as text, like in this video.

Could also try clearing the office/access cache folder make sure that's not what's holding.

When the Master/Child properties are left blank, Access attempts to automatically use any defined relationship that exists between the tables in the Relationships window. In other words, a blank entry in the property sheet doesn't truly mean no link at all, behind the scenes, if Access finds a matching named relationship between the child tables, it can silently utilize it.
Are you sure that in the Relationships window no table level relationship exists? If the relationship at the table level is intact, this is actually expected, albeit lesser known, Access behavior.
You mentioned that you had established a relationship on the form/subform but removed it and this points toward an orphaned internal object cache, as Donald also suggested. Opening the database with msaccess.exe "yourdb" /decompile and then running a Compact & Repair to force a clean recompile of all objects should be your first step. If the issue persists, you should consider Richard 's suggestion to recreate the subform control.

Matt

Some how I missed this video.  Watching this now.  Thank you.

-Raymond

A

What you're describing sounds exactly like what I'm observing.  I've just not seen it do this with other Forms before.

I have opened my Relationships Window, selected "Show All Relationships", and there are none.  I don't normally use this feature in Access.

Opening the database with msaccess.exe "yourdb" /decompile and then running a Compact & Repair to force a clean recompile of all objects should be your first step.  -- I have not done this, but will do it now.  

I had tried removing the Subform and recreating it, but was still getting the same results with the Bound Parent Form.

I have changed the Form and SubForm and have it working... I just don't have the Parent Form Bound to a Table any more.

-Raymond

Hi Raymond

I had a few problems with access, this might not be your problem but.
One of the problems I had was adding existing records to the form, I did what Richard was doing in the Tech help video's,
adding only the records I wanted, when I open the form there where records missing, like 16 out 33.

The fix for this problem was to add all off the records to the form and remove the ones I didn't want.
So now that's what I always do when making a form.

Haven't had that problem ever since that time, and have not been able to recreate it either lol oww Access you red headed step child you.

I will talk about this in the next Quick Queries video: https://599cd.com/QQ