FilterOnLoad=True works for some subdatasheets, not others: Why?

Question

I have a drilldown set of tables/queries setup to show in datasheet/multiple levels of subdatasheet. All of the subdatasheet tables/queries have FilterOnLoad set Yes and a Filter criteria specified. All of them, when opened individually, respect the FilterOnLoad and display as expected, with Toggle Filter enabled.

However, when viewed via the subdatsheet drilldown, some of the levels respect the FilterOnLoad and display accordingly and others don't. Why not?

This may be kinda hard to decipher, but it depicts the problem. All of the subdatsheets should show their FilterOnLoad condition, but only two of the four do. (I labeled the top level filter as disabled; I can enable one and one is not set in default for this table, so I shouldn't have labeled it. But I did the graphic in Paint 3D so the graphic was un-fix-able once I labeled it.):

Answer 1

Build forms with linked master/child relations and apply whatever filtering you wish.  You shouldn't be working directly from within tables.

As for your direct question, without seeing the database I fear it will be very difficult for anyone to diagnose.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

You shouldn't be working directly from within tables.

Aloha Daniel and thanks for responding.

<minirant>I read that pedant-ism here frequently. Do those who offer it really never use Access Datasheet view for Table or Query, skipping straight from working in Table and Query, Design or SQL view, off to building Forms to see if any of what they just designed was right? Permit me to doubt... FWIW, I also use Lookup combo boxes in table design. So I really am a renegade reprobate where Access "conventional wisdom" is concerned.</minirant>

(In the db in question, which is entirely for my own use, no "users", there are only one or two tables that I *ever* entered or edited data in manually, via form or otherwise, and none of that hand-entered data will likely ever change again--one table has nine rows, the other has a total of five rows that have been edited by hand, out of >840 rows in that table. Building and maintaining Forms to manipulate 14 records in two tables, for one time use, seems like overkill. The rest of the data in this db is created and updated from code via DAO or SQL-in-code (Database.Execute or DoCmd.RunSQL). So a bunch of fancy Form-based UI is really beside the point.)

As to Forms vs. the problem at hand, if I built a Datasheet-view Form for the top-level table, and set it up to drill right down the same set of subdatasheets, I'm struggling how that added any value. FWIW, I just built the one-click "Create Form" on my top-level table. The resultant form, again, one-click, I didn't tell Access to do it, Access decided it was appropriate all on its own, has a subform for the subdatasheet drilldown and that drilldown has the exact same problematic set of FilterOnLoad honored and not honored cases. So, creating the Form, per se, solved NOTHING.

Back to the question, not questioning the premise of the question, it's still just three tables and two queries. The 2nd and 3rd level tables have FilterOnLoad set and filter conditions that work when I open them directly. One of the two of them does not honor this FilterOnLoad when accessed as a subdatasheet. The last two are both queries. Both of them have FilterOnLoad set and filter conditions that work when I open them directly. One of the two of them does not honor this FilterOnLoad when accessed as a subdatasheet. I'm not sure what seeing the database would reveal beyond that.

Are there reasons (what are the reasons) why a designed Table or Query FilterOnLoad condition would be honored by Access every time in the datasheet context but only in certain cases in the subdatasheet context?

Answer 3

I agree with Daniel, as, in my experience, would most experienced Access developers, that the user interface should be by means of forms, not raw datasheets.  The fact that you are the only user is not really relevant.  The point is that forms give you far more control over the interface than datasheets, so you are able to provide yourself with an interface with is customized for your purpose.  I do this all the time with my own personal databases, including one in which I store stock answers to frequent questions in this and other forums.  It makes my life a lot easier.

To show a multi-level tree-structured hierarchy in a form, where each level is in continuous forms view, you can use correlated subforms.  Unlike a normal form/subform set-up the subforms' recordsets are not restricted by means of the LinkMasterFields and LinkChildFields properties, but by basing the subforms on queries which reference the primary key control in the subform immediately above the current subform in the hierarchy.

You'll find examples of the use of correlated subforms, using Northwind data, in CorrelatedSubs.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The zip archive includes two files, CorrelatedSubs.accdb and CorrelatedSubsWithShipments.accdb.  The former has two levels of subforms, Orders and OrderDetails, while the latter has three, adding Shipments, all in continuous forms view.

Note that, in the CorrelatedSubsWithShipments demo, the shipments subform is not correlated with the order details subform, but with the orders subform, so shows all shipments for the current order, not for the current order line.  It could be correlated with order details if desired by referencing the key of the latter as parameters, the key in this case being a composite of OrderID and ProductID.

---

Ken Sheridan, Stafford, England

Answer 4

Thanks, Ken. Yes, I get that there are way fancier ways to achieve the objective I'm trying to achieve, and have done the subform<->parent form linkage kinds of things before.

In my present case, the datasheet/subdatasheet paradigm perfectly reflects what I'm trying to achieve and linked parent/linked child fields is perfectly adequate. Maybe even preferable since the datagrid crams a whole lot more information on the screen in a whole lot less screen area than the typical one-control-at-a-time GUI form solution as you have in your example. And, if only the subdatasheet filtering FilterOnLoad worked as expected, is perfect for my needs. (The rapacious inefficiency of screen real estate use is the largest single reason, followed by sloth, why I don't leap to make Forms where just using the tables and queries in datasheet view will do. And also why I use the (dreaded, evil, only true numpties use) Lookup data fields.)

I don't know of a way to have a Datasheet view only form specify that its subdatasheet is a datasheet view only form. Indeed, I just tried it and, even if you set the Source Object for the subform to another datasheet view only form, when you open that form, and try to open the subdatasheet, you get a form view, not the design-specified datasheet view. Do you know of a way to achieve multiply nested subdatasheets in a Form?

The application is a database of multiple different modified time versions of VBAcontainers (with VBProjects (with VBComponents (with CodeModule.Lines, CodeModule module declarations, Code Module unit declarations, and Code Module unit implementations))). Nesting and drilldown is what this is all about.

How I want to view my data (whether Forms are involved or not):

The kind of solution Access Forms comes up with for viewing my data (granted, I could make their layouts denser, etc, but the basic nested datagrid paradigm just doesn't seem achievable to me--note that this is the same amount of screen real estate and the second nested form/third nested subform are so tiny as to not begin to show the whole form...):

OT: I just got an email from UserVoice saying Microsoft is (finally) going to put an improved SQL editor in Access sometime next year. That's the biggest Access product improvement news in a decade...

Answer 5

............. if only the subdatasheet filtering FilterOnLoad worked as expected, is perfect for my needs.

You don't say what filters you are applying, but you should be able to achieve the same result by restricting the queries.  If the filters are constant then the restriction can be hard-coded in the WHERE clause of each query.  If they are variable, then you could reference controls in a small unbound form as parameters.

---

Ken Sheridan, Stafford, England

Answer 6

Each lower level has two columns that define "effectivity" of specific objects to version(s) of the source file. Effectivity is FromVersionKey to ToVersionKey (or ToVersionKey=0) for effective currently. (E.g., a specific VBA code unit implementation was present in a given VBComponent from fileM, version X to Y, or from version Y to current.)

The lower level filters are the same at each level, ToVersionKey=0, so as to filter out the obsolete effectivities. So, filters working, the drilldown only shows latest VBProject within container, latest VBComponents within VBProject, latest CodeModule.Lines / module declaration / code unit declarations + code unit implementations within VBComponent. At all lower levels, the useful filter is ToVersionKey=0 or no filter at all.

I could just use manually the pulldowns on each level to force it, but, alas, I use Lookup on all the versions since a string I can display like "8/27/2020 17:54 [941]" is much more meaningful to display than just the no lookup, bound value 941. But that breaks the pulldown filter since it insists on operating at the Lookup level, not the underlying bound column level, so I can pick a lookup test string but cannot force the not-lookup value of 0. (Yes, I know, Lookup fields are evil and only numpties use them.)  ... some time passes ... I had a mini epiphany, from a trick I learned here and used elsewhere long ago, while typing that out. I appended a 0 into the Versions table, auto-number, key field and changed my query for the lookup to format the displayed value for the 0 case as "(and up) [0]" so now it us usable from all the filter pulldowns. (Side benefit: now I can enforce referential integrity.) FilterOnLoad may not work consistently, but turning it off for all the tables/queries and having the ability to do this by hand in the subdatasheet views may be the next best thing.

I suspect I will either:

a) live with the broken behavior (likely, especially with the "improvement" I just made so the Lookup field+filter pulldown work and play better together), or

b) (less likely) use queries at every lower level incorporating an immutable WHERE ToVersionKey=0, or

c) (even less likely) build an unbound form with a checkbox for "Show Current Only" (to condition use of the filer or not) and five subform datasheets, linked from one to the next for tracking Parent/Child fields. (Containers subform selection sets Projects subform ContainerID, Projects subform selection sets Component subform ProjectID, etc.) It'd be fugly.